Excel Assignment About Business Finance

profilewarpzone
revised_assignment__1_-_fall_2017.xlsx
Home>Business & Finance homework help>Excel Assignment About Business Finance

FCF

INCOME STATEMENTS 2013 2014E 2015E 2016E 2017E
Sales $1,200.00
Operating costs $920.00
Depreciation $30.00
Total operating costs
EBIT
Less interest (INT) $25.00
Earnings before taxes (EBT)
Taxes 40% $0.00
Net income for common (NI)
Dividends to common (DIVs) $12.00
Add. R/E
Shares of common stock 12
Earnings per share (EPS)
Dividends per share (DPS)
Price per share (P) $26.40
BALANCE SHEETS 2013 2014E 2015E 2016E 2017E
Assets
Cash $10.00
ST Investments $0.00
Accounts receivable $140.00
Inventories $200.00
Total current assets
Fixed assets $250.00
Total assets
Liabilities and equity
Accounts payable & accruals $40.00
Line of Credit $0.00
Total current liab.
Long-term bonds $250.00
Total liabilities
Common stock $210.00
Retained earnings $100.00
Total common equity
Total liab. & equity
Hatfield
Selected Additional Data Industry 2013 2014 2015 2016 2017
Sales growth 10.00% 8.00% 6.00% 4.00%
Op costs / Sales 88.00% 76.67%
Depr / FA 12.00% 12.00%
Cash / Sales 0.25% 0.83%
Receivables / Sales 11.00%
Inventory / Sales 11.11% 16.67%
Fixed assets / Sales 33.33% 20.83%
AP & accruals / Sales 2.00% 3.33%
Tax rate 40.00%
Net operating profit after taxes (NOPAT)
Net operating working capital (NOWC)
Total operating capital (TOC)
ΔTOC
NOPAT - ΔTOC (FCF)
Growth in FCF
ROIC 12.50%

TVM

a. Find the FV of $1,000 invested to earn 10% annually 5 years from now. Use excel functions to solve.
N I/Y PV PMT FV
Inputs 5 10.00% $1,000.00
Output $1,610.51
b. Now create a table that shows the FV at 0%, 5%, and 20% for 0, 1, 2, 3, 4, and 5 years.
Then create a graph with years on the horizontal axis and FV on the vertical axis to display your results starting on Cell H10.
Years (D10): Interest Rate (D9)
0.00% 5.00% 20.00%
0 $1,000.00 $1,000.00 $1,000.00
1 $1,000.00 $1,050.00 $1,200.00
2 $1,000.00 $1,102.50 $1,440.00
3 $1,000.00 $1,157.63 $1,728.00
4 $1,000.00 $1,215.51 $2,073.60
5 $1,000.00 $1,276.28 $2,488.32
c. Find the PV of $1,000 due in 5 years if the discount rate is 10% per year. Use excel functions to solve.
N I/Y PV PMT FV
Inputs 5 10.00% $1,000.00
Output ($620.92)
d. A security has a cost of $1,200 and will return $1,800 after 5 years. What rate of return does the security provide?
N I/Y PV PMT FV
Inputs 5 ($1,200.00) $1,800.00
Output ERROR:#NAME?
e. Suppose California’s population is 35 million people, and its population is expected to grow by 2% per year.
How long would it take for the population to double?
N I/Y PV PMT FV
Inputs 2.00% 35.00 70.00
Output 35.00
f. Find the PV of an ordinary annuity that pays $1,100 at the end of each of the next 5 years if the interest rate is 14%. Then find the FV of that same annuity.
N I/Y PV PMT FV
Inputs 5 14.00% $1,100.00
Output ($3,776.39)
N I/Y PV PMT FV
Inputs 5 14.00% $1,100.00
Output $0.00
g. How would the PV and FV of the above annuity change if it were an annuity due rather than an
ordinary annuity?
N I/Y PV PMT FV
Inputs 5 14.00% ($1,100.00)
Output $3,776.39
N I/Y PV PMT FV
Inputs 5 14.00% ($1,100.00)
Output $0.00
h. What would the FV and the PV for parts a and c be if the interest rate were 10% with
semiannual compounding rather than 10% with annual compounding?
Annual N I/Y PV PMT FV Semi-annual N I/Y PV PMT FV
Inputs 5 10.00% ($1,000.00) Inputs 2 10 5.00% ($1,000.00)
Output $1,610.51 Output $1,628.89
Annual N I/Y PV PMT FV N I/Y PV PMT FV
Inputs 5 10.00% ($1,000.00) Inputs 2 10 5.00% ($1,000.00)
Output $620.92 Output $17.34
i. Find the PV and FV of an investment that makes the following end-of-year payments. The
interest rate is 8%.
Year Payment
1 150
2 225
3 375
Rate 8.00%
NPV $629.48
Year Payment FV (Yr 3)
1 100 ($779.84)
2 200 ($1,150.22)
3 400 ($2,091.52)
Sum = ($4,021.58)
j. Suppose you bought a house and took out a mortgage for $500,000. The interest rate is 5%, and you must amortize the loan over 10 years with equal monthly payments. Set up a monthly amortizating schedule that shows the monthly payments and the amount of each payment that repays the principal and the amount that constitutes interest expense to the borrower and interest income to the lender.
The monthly payment would be: $4,375.00
Years Annual Interest Mortgage $525,000.00
Months Monthly Interest
Month Beg. Amt. Pmt Interest Principal End. Bal.
1 = $4,375.00 5.00%
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120

WACC

P0 $60.00
Net Ppf $35.00
Dpf $3.50
D0 $2.50
g 6.00%
B-T rd 11.00%
Skye's beta 0.83
Market risk premium, RPM 6.00%
Risk free rate, rRF 6.50%
Target capital structure from debt 45.00%
Target capital structure from preferred stock 5.00%
Target capital structure from common stock 50.00%
Tax rate 35.00%
Flotation cost for common 10.00%
Cost of debt:
Cost of preferred stock (including flotation costs):
Cost of common equity, DCF (ignoring flotation costs):
Cost of common equity, CAPM: 5.37%
Cost of new stock using the DCF model
Cost of new common stock based on the CAPM
(Hint: Find the difference between re and rs as determined by the DCF method and add that differential to the CAPM value for rs.)
WACC

CAP BUDGET

Inputs
Equipment cost $6,000
Net WC/Sales 10.00% Market value of equipment at Year 4 $250
First year sales (in units) 500 Tax rate 40.00%
Sales price per unit $25.00 WACC 10.00%
Variable cost per unit $17.50 Inflation 3.00%
Nonvariable costs $500
Depreciation and Amortization Schedule Years Accum.
Year Initial Cost 1 2 3 4 Dep.
Equipment Depreciation Rate 20.0% 32.0% 19.0% 12.0%
Equipment Depreciation Amount
Ending Bk Value
Net Salvage Values, in Year 4 Equipment
Estimated Market Value in Year 4
Book Value in Year 4
Expected Gain or Loss
Taxes paid or tax credit
Net cash flow from salvage
Projected Net Cash Flows
0 1 2 3 4
Initial Outflow
Equipment ($6,000)
Operating Cash Flows
Units sold 500 500 500 500
Sales price $25.00 $25.75 $26.52 $27.32
Variable costs $17.50 $18.03 $18.57 $19.12
Sales revenue
Variable costs
Nonvariable operating costs
Depreciation (equipment)
Oper. income before taxes (EBIT)
Taxes on operating income (40%)
After-tax operating income
Add back depreciation
Operating cash flow
Required level of net working capital
Required investment in NWC
Terminal Year Cash Flow
Net salvage value
Net Cash Flows
NPV
IRR
MIRR