MBA Excel Project
Retirement Problem
750 How much money do you save each period?
30 Years until your retirement
15 Years in you plan to be in retirement
Problem 1 4 How many periods in a year? APR
Problem 2 8% Expected return on your savings before retirement (this is an EAR) 1%
Problem 3 6% Expected return on savings during retirement (this is an APR) 2%
3%
Problem 4 Expected annual return on savings before retirement (convert to an APR) 4%
Amount you'll have in your account at retirement based on the savings amount in cell B2 5%
Amount you can spend each period during your retirement 6%
7%
Problem 5 Number of years you delay before starting to save for retirement 8%
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) 9%
Convert that APR to an EAR. 10%
5000 How much do you want to be able to spend each period during your retirement?
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.
NPV
Period 0 Period 1 Period 2 Period 3 Period 4 Period 5 Period 6 Information/Variables
Revenues $25,000,000.00 Company's Other Taxable Income
Variable Costs $30.00 Price per Unit
Fixed Costs/Expenses Depreciable Life (machinery)
EBITDA Units Sold
Depreciation Inventory
EBIT Cost of Capital
Taxes Marginal Cost (per unit)
OCF Machinery
Change NWC Machinery maintenance costs
Net Capital Spending Increase in Cash Balance
CFFA Project Life (years)
Salvage Value
PV (CFFA) Marginal Tax Rate
IRR NPV Decision
Units Sold XXXXXXXXXXTaxable Income Tax Rate
50000 $ - 0 $ 50,000 15%
55000 $ 50,000.00 $ 75,000 25%
60000 $ 75,000.00 $ 100,000 34%
65000 $ 100,000.00 $ 335,000 39%
70000 $ 335,000.00 $ 10,000,000 34%
75000 $ 10,000,000.00 $ 15,000,000 35%
80000 $ 15,000,000.00 $ 18,333,333 38%
85000 $ 18,333,333.00 Above $18,333,333 35%
90000
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.
Microsoft Word - MBAproject(Spring2020).docx
Excel Project
The project consists of two spreadsheets, each containing a separate problem. These problems are meant to get you some experience
using the basic financial functions in Excel as well as familiarizing yourself with Excel help
esources. In addition to Excel’s help and
documentation, you’ll find many resources available on the internet to help you. For instance, if you search YouTube for “naming cells
in Excel” you’ll discover how to refer to cells using names you assign instead of referencing the row and column of the cell (this is
problem #1).
As you work through both problems, keep in mind that:
A. You are expected to complete this assignment on your own. You may use texts and online resources, but you should not ask
any person for help. I will answer clarifying questions about what a problem is asking for, but not how to complete it. Part of
what I want you to experience in this exercise is finding the resources to help you solve problems on your own.
B. Every function/calculation should link to a cell. Never type a value into a function/calculation or you won’t receive credit. As
an example, don’t type “=PV(.1,5,-10)” but instead type “=PV(A1, A2, A3)” where the cells A1, A2, and A3 contain the relevant
values needed for the present value function.
C. If a cell is named, use the name in the reference. For instance, if A1 is named “Rate” then the present value formula should
e written as “=PV(Rate, A2, A3)”.
D. You should format cells according to their values (percentages, dollars, decimals, etcetera). Make certain that only two
decimals are shown and that the thousands separator (numbers should display as 1,000 and not XXXXXXXXXXIf you are sloppy with
your formatting I will deduct 5 points.
Retirement Planning Worksheet: 50 points
In this problem, we want to estimate someone’s 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. We’d 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.
. 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 e
or message will display if the OR function returns the value FALSE.)
c. Display the e
or 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 you’re working and saving for
etirement” when the cell is selected.
. Restrict the values that can be entered into this cell to be a decimal between 0.05 and XXXXXXXXXX% and 25%). NOTE: be
certain to format this cell to display as a percent (%).
c. Display the e
or 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.
. Restrict the values that may be selected to the list I’ve 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 they’re saving
(B5), how long they save (B3), how long they’ll be in retirement (B4), and the return on their investments both prior to and during
etirement (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 you’ll need to link to cells B5 and B6.
. 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; don’t 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.
. 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.
. 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)
krola
Highlight
NPV Worksheet: 50 points
García and Martinez manufacture widgets and cu
ently have $16 million in taxable income. The company is considering an expansion,
and they’ve 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. García 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 machinery’s depreciable life is 7-years