Homework Help is Here – Start Your Trial Now!
Business
Finance
VBA Instructions From the Developer tab, click on "Visual Basic". This takes you to a higher-level project view of your workbook, the column at left shows all your work sheets. You will need to right click on your "Microsoft Excel Objects" in order to "Insert" a "Module" where you can code your function. Begin your function by typing "function FInterp(x, xarray, yarray)" When you hit enter, Excel should provide the "End Function" line that ends the code. You will need to determine the length of the input arrays. Let "n = xarray.Rows.Count". At this point, your code should include several error checking requirements. These are not required for this lab. But a proper coding of this function should check for some things like: - Is n at least 2? - Does yarray also have a row count of n? - Are the values in xarray in increasing order down a column? For this lab, just don't send your function any erroneous inputs. The idea is to find the appropriate index i in order to find the y value on the line between the points: (xarray(i), yarray(i)), and (xarray(i+1),yarray(i+1)) To that end, i will be some integer between 1 and n-1. You will start with i=1, and while i < n-1 and while xarray(i+1) is not yet larger than your x, increase i. So code "i=1". Then, to create an appropriate "While" loop, begin with While (i 1.5 3 8 5 9 Create a straight-line interpolation function in VBA called, FInterp(x, xarray, yarray), that takes as input an array of x values, an array of their matching y values, and the x value for which you want it to interpolate/extrapolate a y value. Instructions for doing this are here. Create a new table with column headings Term Interpolated Vlookup true Vlookup false Under "Term", create a column of terms running from 0 to 6.0 by increments of 0.2. In the "Interpolated" column, for each term, call your FInterp to interpolate/extrapolate the yield for that term (If your sheet appears like the one above, the xarray input for the interpolator is $B$5-$B$8, while the yarray is $C$5-$C$8). For comparison, also find what the "vlookup" function produces. In the column beside your "Interpolated" column, use vlookup on the table $B$5:$C$8 for each term, with the optional 4th argument, "Range_lookup", set to "TRUE" to have vlookup provide the closest match. In the last column, again use vlookup, but with "Range_lookup" set to "FALSE" so that it returns #N/A if there is no exact match. (note: with vlookup set to false, vlookup may return #N/A even for terms of 3 and 5. The failure to find a match is due to unseen numerical errors somewhere around the 16th decimal place of the value in the "Term" column. Your solution will be accepted even with the #N/As). (a) What value does your interpolator return for a term of 2.0? (b) What value does your interpolator return for a term of 0.6? Answer as a real number correct to 4 decimals Answer as a real number correct to 4 decimals

VBA Instructions From the Developer tab, click on "Visual Basic". This takes you to a higher-level project view of your workbook, the column at left shows all your work sheets. You will need to right click on your "Microsoft Excel Objects" in order to "Insert" a "Module" where you can code your function. Begin your function by typing "function FInterp(x, xarray, yarray)" When you hit enter, Excel should provide the "End Function" line that ends the code. You will need to determine the length of the input arrays. Let "n = xarray.Rows.Count". At this point, your code should include several error checking requirements. These are not required for this lab. But a proper coding of this function should check for some things like: - Is n at least 2? - Does yarray also have a row count of n? - Are the values in xarray in increasing order down a column? For this lab, just don't send your function any erroneous inputs. The idea is to find the appropriate index i in order to find the y value on the line between the points: (xarray(i), yarray(i)), and (xarray(i+1),yarray(i+1)) To that end, i will be some integer between 1 and n-1. You will start with i=1, and while i < n-1 and while xarray(i+1) is not yet larger than your x, increase i. So code "i=1". Then, to create an appropriate "While" loop, begin with While (i 1.5 3 8 5 9 Create a straight-line interpolation function in VBA called, FInterp(x, xarray, yarray), that takes as input an array of x values, an array of their matching y values, and the x value for which you want it to interpolate/extrapolate a y value. Instructions for doing this are here. Create a new table with column headings Term Interpolated Vlookup true Vlookup false Under "Term", create a column of terms running from 0 to 6.0 by increments of 0.2. In the "Interpolated" column, for each term, call your FInterp to interpolate/extrapolate the yield for that term (If your sheet appears like the one above, the xarray input for the interpolator is $B$5-$B$8, while the yarray is $C$5-$C$8). For comparison, also find what the "vlookup" function produces. In the column beside your "Interpolated" column, use vlookup on the table $B$5:$C$8 for each term, with the optional 4th argument, "Range_lookup", set to "TRUE" to have vlookup provide the closest match. In the last column, again use vlookup, but with "Range_lookup" set to "FALSE" so that it returns #N/A if there is no exact match. (note: with vlookup set to false, vlookup may return #N/A even for terms of 3 and 5. The failure to find a match is due to unseen numerical errors somewhere around the 16th decimal place of the value in the "Term" column. Your solution will be accepted even with the #N/As). (a) What value does your interpolator return for a term of 2.0? (b) What value does your interpolator return for a term of 0.6? Answer as a real number correct to 4 decimals Answer as a real number correct to 4 decimals

Excel Applications for Accounting Principles
Excel Applications for Accounting Principles
4th Edition
ISBN:9781111581565
Author:Gaylord N. Smith
Publisher:Gaylord N. Smith
Chapter20: Activity-based Costing (abc)
Section: Chapter Questions
Problem 5R
See similar textbooks
icon
Related questions
Question
100%

Please show a step-by-step solution. Please explain your steps on excel and code you input

VBA Instructions From the Developer tab, click on "Visual Basic". This takes you to a higher-level project view of your workbook, the column at left shows all your work sheets. You will need to right click on your "Microsoft Excel Objects" in order to "Insert" a "Module" where you can code your function. Begin your function by typing "function FInterp(x, xarray, yarray)" When you hit enter, Excel should provide the "End Function" line that ends the code. You will need to determine the length of the input arrays. Let "n = xarray.Rows.Count". At this point, your code should include several error checking requirements. These are not required for this lab. But a proper coding of this function should check for some things like: - Is n at least 2? - Does yarray also have a row count of n? - Are the values in xarray in increasing order down a column? For this lab, just don't send your function any erroneous inputs. The idea is to find the appropriate index i in order to find the y value on the line between the points: (xarray(i), yarray(i)), and (xarray(i+1),yarray(i+1)) To that end, i will be some integer between 1 and n-1. You will start with i=1, and while i < n-1 and while xarray(i+1) is not yet larger than your x, increase i. So code "i=1". Then, to create an appropriate "While" loop, begin with While (i<n-1) and (xarray(i+1)<x) The loop will contain only the line of code "i = i +1", where "=" is obviously not an equal sign. It is an assignment operator that computes the value on the RHS, that is i+1, and assigns it to i. You end the loop with a line saying "Wend" For the last line of code, you will have to code up this formula for FInterp: FINTERP = y(t) +; x-x(i) x(i + 1) −x(i) (v(i + 1) − y(i)) but of course coding with your variable names, as appropriate. This formula takes y(i), and adds to it the appropriate portion of the difference between y(i+1) and y(i). If that proportion is negative, or greater than 1, it just winds up extrapolating. We know it's the equation of a line since "x" appears only once, so this equation is effectively y = b + mx, with a messy combination of constants making up b and m. Once you have assigned that value to FInterp, the code for your function is complete. You can exit the visual basic screen, and now your FInterp function can be called from your spreadsheet the same way you would call any other function.
Transcribed Image Text:VBA Instructions From the Developer tab, click on "Visual Basic". This takes you to a higher-level project view of your workbook, the column at left shows all your work sheets. You will need to right click on your "Microsoft Excel Objects" in order to "Insert" a "Module" where you can code your function. Begin your function by typing "function FInterp(x, xarray, yarray)" When you hit enter, Excel should provide the "End Function" line that ends the code. You will need to determine the length of the input arrays. Let "n = xarray.Rows.Count". At this point, your code should include several error checking requirements. These are not required for this lab. But a proper coding of this function should check for some things like: - Is n at least 2? - Does yarray also have a row count of n? - Are the values in xarray in increasing order down a column? For this lab, just don't send your function any erroneous inputs. The idea is to find the appropriate index i in order to find the y value on the line between the points: (xarray(i), yarray(i)), and (xarray(i+1),yarray(i+1)) To that end, i will be some integer between 1 and n-1. You will start with i=1, and while i < n-1 and while xarray(i+1) is not yet larger than your x, increase i. So code "i=1". Then, to create an appropriate "While" loop, begin with While (i<n-1) and (xarray(i+1)<x) The loop will contain only the line of code "i = i +1", where "=" is obviously not an equal sign. It is an assignment operator that computes the value on the RHS, that is i+1, and assigns it to i. You end the loop with a line saying "Wend" For the last line of code, you will have to code up this formula for FInterp: FINTERP = y(t) +; x-x(i) x(i + 1) −x(i) (v(i + 1) − y(i)) but of course coding with your variable names, as appropriate. This formula takes y(i), and adds to it the appropriate portion of the difference between y(i+1) and y(i). If that proportion is negative, or greater than 1, it just winds up extrapolating. We know it's the equation of a line since "x" appears only once, so this equation is effectively y = b + mx, with a messy combination of constants making up b and m. Once you have assigned that value to FInterp, the code for your function is complete. You can exit the visual basic screen, and now your FInterp function can be called from your spreadsheet the same way you would call any other function.
Problem #3: In a previous assignment, Excel wrote a Macro for you by recording your actions. The language the Macro was written in was Visual Basic, or VBA. You can use VBA to program functions or subroutines for yourself. In this exercise you are to write an interpolation function in VBA. You will need to have the "Developer" tab appearing on your ribbon. If it's not there, find "Options" under File or Home, click on "Customize Ribbon" and activate "Developer". Make a yield curve by interpolating and extrapolating from four given terms and yields. Create a table of values on a spreadsheet as shown below, but with the following terms and yields: Problem #3(a): Problem #3(b): Term Yield 1 6.00% 1.5 6.50% 3 3.50% 5 8.50% 4 A B C D 123456700 Term Yield 1 <your value> 1.5 <your value> 3 <your value> 8 5 <your value> 9 Create a straight-line interpolation function in VBA called, FInterp(x, xarray, yarray), that takes as input an array of x values, an array of their matching y values, and the x value for which you want it to interpolate/extrapolate a y value. Instructions for doing this are here. Create a new table with column headings Term Interpolated Vlookup true Vlookup false Under "Term", create a column of terms running from 0 to 6.0 by increments of 0.2. In the "Interpolated" column, for each term, call your FInterp to interpolate/extrapolate the yield for that term (If your sheet appears like the one above, the xarray input for the interpolator is $B$5-$B$8, while the yarray is $C$5-$C$8). For comparison, also find what the "vlookup" function produces. In the column beside your "Interpolated" column, use vlookup on the table $B$5:$C$8 for each term, with the optional 4th argument, "Range_lookup", set to "TRUE" to have vlookup provide the closest match. In the last column, again use vlookup, but with "Range_lookup" set to "FALSE" so that it returns #N/A if there is no exact match. (note: with vlookup set to false, vlookup may return #N/A even for terms of 3 and 5. The failure to find a match is due to unseen numerical errors somewhere around the 16th decimal place of the value in the "Term" column. Your solution will be accepted even with the #N/As). (a) What value does your interpolator return for a term of 2.0? (b) What value does your interpolator return for a term of 0.6? Answer as a real number correct to 4 decimals Answer as a real number correct to 4 decimals
Transcribed Image Text:Problem #3: In a previous assignment, Excel wrote a Macro for you by recording your actions. The language the Macro was written in was Visual Basic, or VBA. You can use VBA to program functions or subroutines for yourself. In this exercise you are to write an interpolation function in VBA. You will need to have the "Developer" tab appearing on your ribbon. If it's not there, find "Options" under File or Home, click on "Customize Ribbon" and activate "Developer". Make a yield curve by interpolating and extrapolating from four given terms and yields. Create a table of values on a spreadsheet as shown below, but with the following terms and yields: Problem #3(a): Problem #3(b): Term Yield 1 6.00% 1.5 6.50% 3 3.50% 5 8.50% 4 A B C D 123456700 Term Yield 1 <your value> 1.5 <your value> 3 <your value> 8 5 <your value> 9 Create a straight-line interpolation function in VBA called, FInterp(x, xarray, yarray), that takes as input an array of x values, an array of their matching y values, and the x value for which you want it to interpolate/extrapolate a y value. Instructions for doing this are here. Create a new table with column headings Term Interpolated Vlookup true Vlookup false Under "Term", create a column of terms running from 0 to 6.0 by increments of 0.2. In the "Interpolated" column, for each term, call your FInterp to interpolate/extrapolate the yield for that term (If your sheet appears like the one above, the xarray input for the interpolator is $B$5-$B$8, while the yarray is $C$5-$C$8). For comparison, also find what the "vlookup" function produces. In the column beside your "Interpolated" column, use vlookup on the table $B$5:$C$8 for each term, with the optional 4th argument, "Range_lookup", set to "TRUE" to have vlookup provide the closest match. In the last column, again use vlookup, but with "Range_lookup" set to "FALSE" so that it returns #N/A if there is no exact match. (note: with vlookup set to false, vlookup may return #N/A even for terms of 3 and 5. The failure to find a match is due to unseen numerical errors somewhere around the 16th decimal place of the value in the "Term" column. Your solution will be accepted even with the #N/As). (a) What value does your interpolator return for a term of 2.0? (b) What value does your interpolator return for a term of 0.6? Answer as a real number correct to 4 decimals Answer as a real number correct to 4 decimals
Expert Solution
steps

Step by step

Solved in 2 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Excel Applications for Accounting Principles
Excel Applications for Accounting Principles
Accounting
ISBN:
9781111581565
Author:
Gaylord N. Smith
Publisher:
Cengage Learning
SEE MORE TEXTBOOKS