Retirement Planning Worksheet: 50 points In this problem,

we want to estimate someones income in retirement based on several variables: the amount of money being saved, the number of years they plan to save, and the number of years they plan to be in retirement. We will also estimate how much someone needs to be saving if they want to have a target level of income during retirement. These retirement calculations require the user to provide information. Wed like to restrict what values can be entered. 1. For cell B5, use the Data Validation tool found in the Data tab to: (3 points) a. Display the message Please input 1 for annual payments, 4 for quarterly payments, and 12 for monthly payments when the cell is selected. b. Only allow the values 1, 4, and 12 in this cell (look under Settings, in the Allow drop down box select Custom, and use the OR function in Formula. Note that an error message will display if the OR function returns the value FALSE.) c. Display the error message You must choose annual, quarterly or monthly payments if a value is entered that returns the value FALSE (that is, if anything other than 1, 4, or 12 is entered). 2. For cell B6, use the Data Validation tool found in the Data tab to: (3 points) a. Display the message Please input the expected annual return on your savings while youre working and saving for retirement when the cell is selected. b. Restrict the values that can be entered into this cell to be a decimal between 0.05 and 0.25 (5% and 25%). NOTE: be certain to format this cell to display as a percent (%). c. Display the error message The return must be between 5% and 25% (0.05 and 0.25) if a value is entered that is outside this range. 3. For cell B7, use the Data Validation tool found in the Data tab to: (3 points) a. Display the message Please input the expected annual return on your savings during retirement when the cell is selected. b. Restrict the values that may be selected to the list Ive provided in Column Q (Look under Settings. In the Allow drop down box, select List. Use Source to select those cells in Column Q). c. Now hide Column Q (right-click on the column heading, Q). Now calculate what someone will be able to spend in retirement given how much they are saving (B2), how often theyre saving (B5), how long they save (B3), how long theyll be in retirement (B4), and the return on their investments both prior to and during retirement (B6/B9 and B7). 4. Based on the amount being saved each period, estimate how much can be spent each period in retirement. (12 points) a. In cell B9, use the function NOMINAL to convert the expected return on your savings prior to retirement into an APR. Note that if you pick monthly payments (12 payments per year), then the APR needs to be based on monthly compounding, so youll need to link to cells B5 and B6. b. In cell B10, use the function FV to calculate the how much money will be in the account at retirement. As with all the calculations, this should be linked to other cells; dont input the values. c. In cell B11, use the PMT function to calculate the retirement annuity based on the savings at retirement in B10, (how much money you can live off of each period during retirement given savings at retirement). 5. Assume that this individual intends to save nothing for the first few years, which is common for young workers. (12 points) a. Use the RATE function in cell B14 to calculate what you would need the return to be in order to have the same amount of money at retirement as in Problem 4 (cell B10) given the delay entered in cell B13. Report this rate as an APR. b. Use the EFFECT function in cell B15 to convert this APR into an EAR. c. Use the IFERROR function in cell B14 and B15 to return the message "Based on these numbers, you never save any money! if the worker delays saving (cell B13) for more years than there are years until retirement (cell B3). Now calculate the amount that should be saved each period to attain a target retirement income (B17) based on the frequency of savings (B5), the number of years (B3 and B4), and the return on the investments (B7 and B9). 6. Based on the desired income in retirement (B17), estimate how much should be saved each period before retirement. (8 points) a. In cell B19, use the PV function to calculate the how much money is needed at retirement to provide the desired income in B17. b. In cell B20, use the amount in B19 and the PMT function to calculate how much money must be saved each period. 7. To give a sense of how interest rates (B6) and the years of savings (B3) impact the funds available in retirement (B11), create a two-way data table and allow the interest rate to vary from 2% to 12% in increments of 1%, and the years of savings from 10 years to 50 years in increments of 5. The start of the table should be in cell B23, and the table should be formatted (use colors, borders, etc. to make the data table readable). (5 points) 8. Protect the worksheet RetirementProblem so that only the dark green (input) cells values may be changed. Do NOT password protect the sheet. (4 points) NPV Worksheet: 50 points Garca and Martinez manufacture widgets and currently have $16 million in taxable income. The company is considering an expansion, and theyve asked you to evaluate the project. The expansion requires the firm to produce 70,000 widgets a year for 6 years, and the company estimates they can sell them for $30 per widget. Garca and Martinez estimate they will need an additional $5,000,000 worth of machinery. The machinery costs $150,000 a year to operate and maintain. The machinerys depreciable life is 7-years, and the company expects to salvage the machinery for $160,000 at the end of year 6. If the project is accepted, the company will immediately increase inventory by $500,000 and maintain the new inventory level over the projects life. Similarly, the company will immediately add $50,000 to their cash balance and maintain that higher cash balance over the projects life. The investments in cash and inventory will be recovered when the project is completed. The marginal cost of producing a widget is $6.00 and the cost of capital is 14%. Calculate the projects NPV by linking to the information/variable values in Column K. 1. Enter the relevant values for the variables that you use in solving this problem in Column K (as Ive done with the companys taxable income in K3 and the price per unit in K4). Name each cell in Column K that contains a variable. (e.g. define the name of cell K4 to be Price). Where possible (linking cells or writing functions), use these names to reference the cells in your calculations. This makes it easier for others to know what the functions are doing. (5 points) 2. Reformat Row 2 (cells B2 to H2) so that the numbers display the text Year instead of Period. For example, there is currently a zero in cell B2, but we see Period 0 displayed and not just 0. Change this so it displays as Year 0 (this formatting trick allows us to use the numbers in calculations, but we can also use labels so we know what the numbers represent). (3 points) 3. Calculate the net capital spending for each period in Row 12 and calculate the change in net working capital for each period in Row 11. (5 points) 4. Calculate the operating cash flow for each period in Row 10. a. Using relative and/or absolute cell references, use the VDB function in Row 7 to calculate depreciation. Note the function should use the accelerated (double-declining) depreciation for each year but switch to straight-line when its depreciation is larger. If done and linked correctly, when I copy the cell in Column C into Column H, the cell in Column H will return the correct answer for depreciation. (3 points) b. Use the VLOOKUP function on the provided tax table to calculate the marginal tax rate in cell K15. Use that marginal tax rate to calculate taxes in each year in Row 9. (3 points) c. Make sure to fill in rows 3 to 9, and then calculate the OCF for each period in Row 10. (2 points) 5. Evaluate the project. a. Calculate the cash flow from assets in Row 13. (2 points) b. Using relative and/or absolute cell references, use the PV function to calculate the present value of CFFA for each year in Row 15. If done correctly, I will be able to copy cell B15 and paste it into cells C15:H15 and those cells will display the correct present value. (3 points) c. Use the NPV function in D18 to calculate the net present value of the project. If done correctly, it will give the same value as summing cells B15 to H15. (3 points) d. Use the IRR function in C18 to calculate the internal rate of return on the project. (3 points) e. In E19, use an IF statement to return the text Accept or Reject depending on the NPV calculation. (2 points) 6. Construct a sensitivity analysis for units sold (quantities are listed in Column B starting in Row 19). Define a data table using Excels What-If Analysis located in the data tab. (6 points) a. The table should include the IRR, NPV, and Decision for each quantity; i.e. for each quantity Excel will calculate these three items in the table which will extend from C19 to E27. b. Use conditional formatting (found in the Home tab) to highlight the NPV in red if the NPV is negative, and green if it is positive. Use a conditional format to highlight the IRR in red if the IRR is greater than the cost of capital, and in green if it is less than the cost of capital. 7. Define a base-case, best-case and worst-case scenario using the Scenario Manager in Excels What-If Analysis. Use the following value ranges for the best and worst cases: (6 points) a. Per unit price is plus/minus $5 b. Quantity sold is plus/minus 15,000 units c. Marginal cost of producing a widget is plus/minus $1.50 8. Using the Scenario Manager, create a scenario summary. (4 points)

MBAproject(Spring2020) Q- Search Sheet Home Insert Page Layout Review View + Share Formulas Data o Connections 2 AZ Clear > DE Show Detail - Properties HD From From FileMaker HTML Filter From New Database Text Query Refresh All + Sort Advanced Edit Links Text to Remove Data Columns Duplicates Validation Consolidate What If Analysis Group Ungroup Subtotale Hide Detail B5 fx B D E F G H 1 K L L M N o P Q R 1 2 3 4 750 How much money do you save each period? 30 Years until your retirement 15 Years in you plan to be in retirement How many periods in a year? Expected return on your savings before retirement (this is an EAR) Expected return on savings during retirement (this is an APR) 5 6 Problem 1 Problem 2 Problem 3 7 1% 2% 3% 4% 5% 6% Problem 4 8 9 10 11 12 Expected annual return on savings before retirement (convert to an APR) Amount you'll have in your account at retirement based on the savings amount in cell B2 Amount you can spend each period during your retirement 7% 8% 9% 10% 13 Problem 5 14 15 16 17 Number of years you delay before starting to save for retirement If you do delay starting to save, what would the interest rate need to be for you to hit the target amount in B10? (APR) Convert that APR to an EAR. 5000 How much do you want to be able to spend each period during your retirement? 18 19 Problem 6 Amount you need in your account at retirement in order to spend this amount (cell B17) Amount you need to save each period before retirement to have enough to meet your goal. 20 21 22 23 24 25 ac 26 27 28 29 30 31 32 Retirement Problem NPV + Ready PH + 100% MBAproject(Spring2020) Q- Search Sheet Home Insert Page Layout Formulas Data Review View 9+ Share a Connections Clear > DE Show Detail HD - Properties 2 AZ + Sort Filter From From FileMaker HTML From New Database Text Query Refresh All Advanced Text to Remove Data Columns Duplicates Validation Consolidate What If Analysis Group Edit Links Ungroup Subtotal Hide Detail C12 fx A B D E F G H 1 L M N O P Q Periodo Period 1 Period 2 Period 3 Period 4 1 2 3 4 Periods Period 6 5 6 7 8 9 Revenues Variable Costs Fixed Costs/Expenses EBITDA Depreciation EBIT Taxes OCE Change NWC Net Capital Spending CFFA Information/Variables $25,000,000.00 Company's Other Taxable income $30.00 Price per Unit Depreciable Life (machinery) Units Sold Inventory Cost of Capital Marginal Cost (per unit) Machinery Machinery maintenance costs Increase in Cash Balance Project Life (years) Salvage Value Marginal Tax Rate PV (CFFA) IRR NPV Decision 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Units Sold 50000 55000 60000 65000 70000 75000 80000 85000 90000 Taxable income $ 50,000 50,000.00 $ 75,000 75,000.00 $ 100,000 100,000.00 $ 335,000 335,000.00 $ 10,000,000 10,000,000.00 $ 15,000,000 15,000,000.00 $ 18,333,333 18,333,333.00 Above $18,333,333 Tax Rate 15% 25% 34% 39% 34% 35% 38% $ $ $ $ 35% Note: Corporate taxes are now flat at 21%. I have kept the old corporate tax table in this example to illustrate the VLOOKUP/HLOOKUP function. In addition, personal taxes are still based on similar tables, as are state corporate taxes. 31 32 33 34 35 36 37 38 39 40 Retirement Problem NPV + Ready + 81% MBAproject(Spring2020) Q- Search Sheet Home Insert Page Layout Review View + Share Formulas Data o Connections 2 AZ Clear > DE Show Detail - Properties HD From From FileMaker HTML Filter From New Database Text Query Refresh All + Sort Advanced Edit Links Text to Remove Data Columns Duplicates Validation Consolidate What If Analysis Group Ungroup Subtotale Hide Detail B5 fx B D E F G H 1 K L L M N o P Q R 1 2 3 4 750 How much money do you save each period? 30 Years until your retirement 15 Years in you plan to be in retirement How many periods in a year? Expected return on your savings before retirement (this is an EAR) Expected return on savings during retirement (this is an APR) 5 6 Problem 1 Problem 2 Problem 3 7 1% 2% 3% 4% 5% 6% Problem 4 8 9 10 11 12 Expected annual return on savings before retirement (convert to an APR) Amount you'll have in your account at retirement based on the savings amount in cell B2 Amount you can spend each period during your retirement 7% 8% 9% 10% 13 Problem 5 14 15 16 17 Number of years you delay before starting to save for retirement If you do delay starting to save, what would the interest rate need to be for you to hit the target amount in B10? (APR) Convert that APR to an EAR. 5000 How much do you want to be able to spend each period during your retirement? 18 19 Problem 6 Amount you need in your account at retirement in order to spend this amount (cell B17) Amount you need to save each period before retirement to have enough to meet your goal. 20 21 22 23 24 25 ac 26 27 28 29 30 31 32 Retirement Problem NPV + Ready PH + 100% MBAproject(Spring2020) Q- Search Sheet Home Insert Page Layout Formulas Data Review View 9+ Share a Connections Clear > DE Show Detail HD - Properties 2 AZ + Sort Filter From From FileMaker HTML From New Database Text Query Refresh All Advanced Text to Remove Data Columns Duplicates Validation Consolidate What If Analysis Group Edit Links Ungroup Subtotal Hide Detail C12 fx A B D E F G H 1 L M N O P Q Periodo Period 1 Period 2 Period 3 Period 4 1 2 3 4 Periods Period 6 5 6 7 8 9 Revenues Variable Costs Fixed Costs/Expenses EBITDA Depreciation EBIT Taxes OCE Change NWC Net Capital Spending CFFA Information/Variables $25,000,000.00 Company's Other Taxable income $30.00 Price per Unit Depreciable Life (machinery) Units Sold Inventory Cost of Capital Marginal Cost (per unit) Machinery Machinery maintenance costs Increase in Cash Balance Project Life (years) Salvage Value Marginal Tax Rate PV (CFFA) IRR NPV Decision 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Units Sold 50000 55000 60000 65000 70000 75000 80000 85000 90000 Taxable income $ 50,000 50,000.00 $ 75,000 75,000.00 $ 100,000 100,000.00 $ 335,000 335,000.00 $ 10,000,000 10,000,000.00 $ 15,000,000 15,000,000.00 $ 18,333,333 18,333,333.00 Above $18,333,333 Tax Rate 15% 25% 34% 39% 34% 35% 38% $ $ $ $ 35% Note: Corporate taxes are now flat at 21%. I have kept the old corporate tax table in this example to illustrate the VLOOKUP/HLOOKUP function. In addition, personal taxes are still based on similar tables, as are state corporate taxes. 31 32 33 34 35 36 37 38 39 40 Retirement Problem NPV + Ready + 81%