The purpose of this exercise is to analyze a worksheet to identify and correct any integrity flaws. Read the scenario below, then open the Excel workbook related to this exercise. Use the AnswerSheet worksheet for any written responses required for this exercise.

SCENARIO

Your coworker is analyzing a few financial calculations in Excel and is asking for your assistance. The workbook contains calculations for estimating the future value of investments and annual lease payments for equipment. The following was explained in an email that was sent with the workbook:

The Investment Plan worksheet shows the investment valuation estimates. I set the time horizon for each investment to five years. For some reason the outputs for the functions I used are all negative. I think the numbers are correct, so just assume these values are positive.

The Lease Payments worksheet shows the equipment we need to lease. I estimated the equipment cost at $675,000. Our budget is $45,000 a year for lease payments. My calculations show the annual payments are $52,409.38, so will need to lower the equipment cost.

ASSIGNMENT

1. Look at the FV function that was used in column F on the Investment Plans worksheet. Why are all the outputs negative?

2. Your coworker assumes the outputs produced by the FV function are correct, but they are just negative instead of positive. Do you agree with this statement?

3. Evaluate the function used to calculate the lease payments in the Lease Payments worksheet. Is the function producing a reliable output?

4. Your coworker believes the annual lease payment exceeds the $45,000 budget. If this is true, what can you do to adjust the Equipment Cost so the annual payments are exactly $45,000?

5. Make any necessary corrections to the functions used in this workbook. Set the equipment cost in the Lease Payments worksheet so the annual payments are exactly $45,000.

Investment Analysis