APPLY YOUR SKILLS: E10-A3 Perform a Cost Analysis In this exercise, you will use financial functions and what-if analysis to help Universal Corporate Events analyze its office renovation project and decide how to finance it. 1. Open the E10-A3-Reno file from your Excel Chapter 10 folder and save it as E10-A3-RenoCosts. 2. In cell B10, insert a PMT function based on the renovation costs, term, and interest rate. (Hint: multiply years by 12 to get the number of payments for the NPER argument.) 3. In cell B11, insert a formula to calculate the total interest by multiplying the payments times the years, times 12, and then subtracting the renovation costs. 4. Select cell F6 and insert a reference to the interest cost formula in cell B11 5. Select the range E6:F11 and insert a Data Table with one variable substituting the new values for years. 6. Hide row 6 in the worksheet. 7. Select the range A7:B11 and create names for the cells from the selection based on the left column. Now you will create 3 scenarios and generate a summary report to compare the 3 options. 8. Using cells B7, B8, and B9 as the changing cells, create a scenario based on the current worksheet data called Average. 9. Create a new scenario called Best and change the renovation costs to $21,000, term to 10 years, and the interest rate to 4.25%. 10. Create a new scenario called Worst and change the renovation costs to $30,000, term to 20 years, and the interest rate to 5.5%. 11. Generate a scenario summary report with payments and total interest cost for the result cells. As you can see the payments for the Best scenario are slightly higher, but the total interest cost is much lower. 12. Save your work and close the file. File Home Insert Draw Page Layout Formulas Data Review View Help Search Share Comments A 3 A D AutoSum 47 O AA - A- Calibri - 11 BIU - A - Font === 20 SE Wrap Text 3 Merge & Center - Paste General $ % $,% , CO .00 688 Insert Delete Format Ideas Fill Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter - Select Editing Clipboard Alignment Number Cells Ideas B12 co E F G H I J K L M N O P Q R S T | B 1 Universal Corporate Events 2 Office Renovations Financing Interest Cost $ 24,000 Years 5.00% 7 Renovation Costs 8 Term (Years) 9 Interest Rate 10 Payments 11 Total Interest Cost 12 Costs Ready @ -_ _ +