This exam is closed book, closed notes (other than one sheet), and closed Excel files other than this file. It is to
with anyone in answering the questions. You are allowed to use a one page formulas/notes sheet.
You must show all your work for full credi
Retirement Planning:
Very frequently retirement planners use models, which help them to answer the question, how much a client need
finance life at retirement. These models depend on many assumptions that need to be made, and good models hav
hand, some fl
Sales growth
Current assets/Sales
Current liabilities/Sales
Costs of goods sold/Sales
Depreciation rate
Interest rate on debt
Interest paid on cash & marketable securities
Tax rate
Dividend payout ratio
Year
Income statement
Sales
Costs of goods sold
Inte
Sales growth
Current assets/Sales
Current liabilities/Sales
Costs of goods sold/Sales
Depreciation rate
Interest rate on debt
Interest paid on cash & marketable securities
Tax rate
Dividend payout ratio
Target Debt-Asset Ratio
Cost of equity
Year
Income s
Valuationmethods
1. Dividend growth Model
2. Comparables valuation
3. Discounted Cash Flow (DCF) valuation
StepsinDCFValuation
1.
Estimate future cash flows
2.
Determine the forecast horizon T
3. Make detailed FCF forecast for every year until the end of
Retirement Age Planning :
This is a modification of retirement planning problem. Please create a model, which would calculate your retirem
model such as Current age, Life expectancy, as well as Inflation-adjusted parameters such as Annual Contributio
inco
Loan Calculation (Chapters 0, 1, 31, 33, 35):
A car dealership wants to create a model, which would calculate client's payments based on parameters of the loa
a) Please develop such model in two different ways: using both formulas and loan amortization sc
Data validation and Finding Return (Chapters 31, 33, 35):
Below is the table of annual returns for S&P 500, 3-month T-Bills, and 10-year T-Bonds
Questions:
a) Create a mini-model, which would report S&P 500 return for a given year; don't forget data valid
Chapter 1 Exercise
We want to build a model which would allow us to calculate either:
How long it will take to repay debt accumulated over time in college?
What the payment should be to repay debt over preset period of time.
Starting from the basic exampl
Submodel 1 (Payments-inputs)
Output - Time to repay
Simple Version -Excel function NPER
Function Arguments
Rate = (Refinanced annual rate /Payment Frequency )
Payment = Input
PV = -accumulated FV of all loan tranches
FV- = the Balloon (Lump Sum) payment a
Finding Return:
Below is the table of annual returns for S&P 500, 3-month T-Bills, and 10-year T-Bonds
Questions:
a) Create a mini-model, which would report S&P 500 return for a given year;
b) Create a mini-model, which would report return for security of