Abraham Lincoln University Cash Flow and Risk Free Rates Case Study Calculations Read Chapters 8, 9, 10, 11 and 12 and complete excels attached. Complete t

Abraham Lincoln University Cash Flow and Risk Free Rates Case Study Calculations Read Chapters 8, 9, 10, 11 and 12 and complete excels attached. Complete the Case Study.

Case Study:

Objective

To calculate Cash Flow and Risk free rates.

Synopsis

Please make sure you read the case’s in the book.

Bullock Gold Mining, chapter 8.

Conch Republic Electronics, chapter 9

A Job at S&S Air, chapter 10.

Assignment Questions

1.complete excel attached.

2. Write paper 750 words or less, of how this assignment helped you understand what you have learned thus far in this course. Chapter 8
Bullock Gold Mining
Input area:
Year
0
1
2
3
4
5
6
7
8
9
Cash flow
Required return
Output area:
Payback period
IRR
IRR
#NUM! =IRR(D8:D17)
#NUM! =IRR(D8:D17,-0.99)
MIRR
#DIV/0! =MIRR(D8:D17,D19,D19)
Profitability index
#DIV/0! =NPV(D19,D9:D17)/-D8
NPV
$
–
=NPV(D19,D9:D17)+D8
Chapter 9
Conch Republic Electronics
Input Area:
Equipment
Salvage value
R&D
Marketing study
sunk cost
sunk cost
Year 1
Year 2
Year 3
Sales(units)
Depreciation rate
Price
VC
FC
Tax rate
NWC percentage
Required return
Sensivity analysis
New price
Quantity change
NOTE: Change in units per year
Output Area:
Year 1
Sales
VC
Fixed costs
Dep
EBT
Tax
NI
+Dep
OCF
NWC
Beg
End
Year 2
$0
0
0
0
$0
0
$0
0
$0
$0
0
Year 3
$0
0
0
0
$0
0
$0
0
$0
$0
0
$0
0
0
0
$0
0
$0
0
$0
$0
0
NWC CF
$0
$0
$0
Net CF
$0
$0
$0
Salvage
BV of equipment
Taxes
Salvage CF
Net CF
$0
0
$0
Time
0 $
1
2
3
4
5
Payback period
PI
IRR
NPV
FALSE
#DIV/0!
#NUM!
$0.00
Sensitivity to change in price
Sales
Sales
VC
Fixed costs
Dep
EBT
Tax
NI
+Dep
OCF
Year 1
Year 2
Year 3
$0
0
0
0
$0
0
$0
0
$0
$0
0
0
0
$0
0
$0
0
$0
$0
0
0
0
$0
0
$0
0
$0
NWC
Beg
End
NWC CF
$0
0
$0
$0
0
$0
$0
0
$0
Net CF
$0
$0
$0
Salvage
BV of equipment
Taxes
Salvage CF
Net CF
$0
0
$0
Time
0 $
1
2
3
4
5
NPV
–
$
DNPV/DP
#DIV/0!
Sensitivity to change in quantity
Sales
Sales
VC
Fixed costs
Dep
EBT
Tax
NI
+Dep
OCF
Year 1
Year 2
Year 3
$0
0
0
0
$0
0
$0
0
$0
$0
0
0
0
$0
0
$0
0
$0
$0
0
0
0
$0
0
$0
0
$0
NWC
Beg
End
NWC CF
$0
0
$0
$0
0
$0
$0
0
$0
Net CF
$0
$0
$0
Salvage
BV of equipment
Taxes
Salvage CF
Net CF
$0
0
$0
Time
0 $
1
–
2
3
4
5
NPV
DNPV/DQ
$0.00
#DIV/0!
Year 4
Year 5
Year 4
Year 5
$0
0
0
0
$0
0
$0
0
$0
$0
0
$0
0
0
0
$0
0
$0
0
$0
$0
0
$0
$0
$0
$0
Year 4
Year 5
$0
0
0
0
$0
0
$0
0
$0
$0
0
0
0
$0
0
$0
0
$0
$0
0
$0
$0
0
$0
$0
$0
Year 4
Year 5
$0
0
0
0
$0
0
$0
0
$0
$0
0
0
0
$0
0
$0
0
$0
$0
0
$0
$0
0
$0
$0
$0
Chapter 10
A Job at S&S Air
Input area:
Risk-free rate
10-year return
S&P 500 Index fund
Small-cap stock fund
Large company stock fund
Bond fund
Output area:
Sharpe ratios:
S&P 500 Index fund
Small-cap stock fund
Large company stock fund
Bond fund
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Standard
deviation
Ross, Westerfield, and Jordan’s Excel
Essentials of Corporate Finance, 9th edition
by Brad Jordan and Joe Smolira
Version 9.0
Chapter 9
In these spreadsheets, you will learn how to use the following Excel f
Naming cells
SLN
VDB
Scenario Manager
One-way Data Table
Solver
The following conventions are used in these spreadsheets:
1) Given data in blue
2) Calculations in red
NOTE: Some functions used in these spreadsheets may require that
the “Analysis ToolPak” or “Solver Add-In” be installed in Excel.
To install these, click on the File button
then “Options,” “Add-Ins” and select
“Go.” Check “Analysis ToolPak” and
“Solver Add-In,” then click “OK.”
Chapter 9 – Section 3
Pro Forma Financial Statements and Cash Flows
Because capital budgeting requires numerous repetitive cash flows, it is an ideal application for Excel. When doing
should do few or no calculations on your own, but rather let Excel do the calculations for you. We will begin with th
projections for the project:
Cans sold per year:
Price per can:
Variable cost per can:
Required return:
Fixed costs per year:
Manufacturing equipment:
Project life (years):
Initial net working capital:
Tax rate:
$
$
$
$
$
50,000
4.00
2.50
20%
12,000
90,000
3
20,000
34%
RWJ Excel Tip
In a problem with a number of different variables, it can be advantageous to name the cells. Click on the input cell
the Formula bar in the name bar and you will see the name “Units.” We entered the name in the name bar to nam
from this cell later, we can type in the name of the variable instead of referencing the cell. For example, if you look
formula we used in this cell is Units * Price_per_unit. When naming cells, you should keep the names short but und
the variable name, so we used an underscore instead of the space in Price_per_unit.
With these numbers, we can prepare the pro forma income statement, which will be:
Sales
Variable costs
Fixed costs
Depreciation
EBIT
Taxes (34%)
Net income
$
$
$
200,000
125,000
12,000
30,000
33,000
11,220
21,780
RWJ Excel Tip
To calculate the depreciation each year for straight-line depreciation, we can divide the initial cost by the life of th
as we have done here. The SLN inputs we used in this case looks like this:
The inputs are Cost, which is the initial cost, Salvage, which is the salvage value, and Life, which is the life of the ass
cost by the life of the equipment in the cell rather than use this particular function, but it is available if you prefer.
Average Accounting Return
To calculate the AAR, we need the average investment in assets each year. The total investment each year will be:
Net working capital
Net fixed assets
Total investment
$
$
0
20,000 $
90,000
110,000 $
Year
1
20,000 $
60,000
80,000 $
2
20,000
30,000
50,000
So, the average assets are:
Average assets:
$
65,000
Now, we can calculate the operating cash flow each year, which will be:
EBIT
+ Depreciation
– Taxes
Operating cash flow
$
$
33,000
30,000
11,220
51,780
So, the total cash flow for each year of the project will be:
Year
0
Operating cash flow
Changes in NWC
Capital spending
Total project cash flow
$
$
$
1
51,780 $
2
51,780
(20,000)
(90,000)
(110,000) $
51,780 $
51,780
Given these cash flows, we can now calculate the NPV, IRR, and AAR of the project, which are:
NPV
IRR
AAR
$
10,647.69
25.76%
33.51%
l application for Excel. When doing a capital budgeting problem, as in most Excel uses, you
ations for you. We will begin with the shark attractant project. We have the following
me the cells. Click on the input cell for the number of cans sold per year, and look to the left of
d the name in the name bar to name the input in this cell. Whenever we want to use the input
ng the cell. For example, if you look at the sales calculation below, you will see that the
hould keep the names short but understandable. In addition, Excel does not allow spaces in
_unit.
ivide the initial cost by the life of the equipment, or we can use the built-in Excel function SLN
and Life, which is the life of the asset. In general, we usually find it easier just to divide the
ion, but it is available if you prefer.
total investment each year will be:
$
3
20,000
20,000
$
$
3
51,780
20,000
$
71,780
$
Chapter 9 – Section4
More about Project Cash Flow
In practice, many assets are depreciated on a MACRS schedule for tax purposes. The three-, five-, and seven-year M
Year
1
2
3
4
5
6
7
8
3-year
33.33%
44.45%
14.81%
7.41%
Property Class
5- year
20.00%
32.00%
19.20%
11.52%
11.52%
5.76%
7-year
14.29%
24.49%
17.49%
12.49%
8.93%
8.92%
8.93%
4.46%
For example, suppose we have an asset that falls in the five-year MACRS classification and the initial cost is:
Initial cost:
$
12,000
The depreciation for each year will be:
Year
1
2
3
4
5
6
MACRS
percentage
Depreciation
20.00% $
2,400.00
32.00%
3,840.00
19.20%
2,304.00
11.52%
1,382.40
11.52%
1,382.40
5.76%
691.20
100.00% $ 12,000.00
To find the book value of the asset, we subtract depreciation each year from the beginning book value. The book v
Year
1
2
3
4
5
Beginning
Ending
book value
Depreciation
book value
$
12,000 $
2,400.00 $
9,600.00
9,600.00
3,840.00
5,760.00
5,760.00
2,304.00
3,456.00
3,456.00
1,382.40
2,073.60
2,073.60
1,382.40
691.20
6
691.20
691.20
–
When the asset is sold, taxes will be paid if the asset is sold for more than book value, or a tax rebate will be given
calculate the taxes on the sale of the asset is (Book value – Market value)(Tax rate). Suppose the pretax salvage valu
Pretax salvage value:
Tax rate:
$
3,000
34%
The aftertax salvage value, and therefore net cash flow from selling the asset at the end of the project (Year 6 in th
Pretax salvage value:
Taxes on sale:
Aftertax salvage value:
$
$
3,000.00
(1,020.00)
1,980.00
The Majestic Mulch and Compost Company (MMCC)
The MMCC capital budgeting problem is a more in-depth analysis. As before, we want to put all inputs in a separat
entering the data in rows for each variable, we could enter the data in columns as well. The input information for t
Year
Units sales
Price
NWC to start
NWC % of sales
Variable cost
Fixed costs
Equipment
MACRS
Pretax salvage
value
Tax rate
Required return
1
$
$
$
$
$
2
3,000
120 $
20,000
15%
60
25,000
800,000
14.29%
3
5,000
120 $
24.49%
4
6,000
120 $
17.49%
6,500
110
12.49%
20%
34%
15%
We will calculate the operating cash flow first, which we can calculate as net income plus depreciation. So, the pro
year will be:
Year
Revenues
Variable costs
Fixed costs
Depreciation
EBIT
Taxes (34%)
1
2
$ 360,000.00 $ 600,000.00 $
180,000.00
300,000.00
25,000.00
25,000.00
114,320.00
195,920.00
$ 40,680.00 $ 79,080.00 $
13,831.20
26,887.20
3
720,000.00
360,000.00
25,000.00
139,920.00
195,080.00
66,327.20
Pro Forma Income Statements
4
$ 715,000.00
390,000.00
25,000.00
99,920.00
$ 200,080.00
68,027.20
Net income
+ Depreciation
OCF
$
26,848.80 $ 52,192.80 $ 128,752.80 $ 132,052.80
114,320.00
195,920.00
139,920.00
99,920.00
$ 141,168.80 $ 248,112.80 $ 268,672.80 $ 231,972.80
RWJ Excel Tip
Notice that in the income statements, we were careful to use absolute references in the variable costs, fixed costs,
the equations to calculate the net income during the first year, we simple copied and pasted the year 1 net income
Next, we will calculate the change in net working capital. One way to do this is to calculate the difference between
to remember that the net working capital at the end of the project will be zero. So, the net working capital require
Year
Initial NWC
Ending NWC
NWC cash flow
$
0
(20,000) $
$
(20,000) $
1
20,000.00 $
54,000.00
(34,000.00) $
2
3
54,000.00 $ 90,000.00
90,000.00
108,000.00
(36,000.00) $ (18,000.00)
To find the aftertax salvage value, we need to calculate the taxes. We get:
Pretax salvage value:
Taxes on sale:
Aftertax salvage value:
$ 160,000.00
(54,400.00)
$ 105,600.00
So, the total cash flows for each year of the project are:
Project Cash Flows
Year
OCF
Change in NWC
Capital spending
Total cash flow
0
$
$
$
1
2
3
$ 141,168.80 $ 248,112.80 $ 268,672.80
(20,000)
(34,000.00)
(36,000.00)
(18,000.00)
(800,000)
(820,000) $ 107,168.80 $ 212,112.80 $ 250,672.80
Finally, the NPV and IRR of the project are:
NPV:
IRR:
$
65,484.83
17.24%
A Note on MACRS Depreciation
There are actually six MACRS schedules, three-, five-, seven-, 10-, 15-, and 20-year schedules. The MACRS schedule
declining balance method, and switching to straight-line depreciation when it is more advantageous. The three-, fiv
when calculating the double declining balance depreciation amount, while the 15- and 20-year schedules use a fac
used to construct a MACRS table. Below, we have constructed a MACRS table with all six schedules.
Equipment Life (Years)
Year
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
3
33.33%
44.44%
14.81%
7.41%
5
20.00%
32.00%
19.20%
11.52%
11.52%
5.76%
7
14.29%
24.49%
17.49%
12.49%
8.92%
8.92%
8.92%
4.46%
RWJ Excel Tip
To construct the MACRS table, we used the variable declining balance (VDB) function. Constructing the MACRS tab
will see what we entered for the second year of the three-year MACRS schedule.
Cost is the cost of the equipment. In this case, we entered one in order to get the answers as a percentage rather t
zero. Life is the life of the asset. Since we have a table here, we entered the column as a floating input and locked t
down the table was well as across. The Start_period is the starting period for which we want to calculate the depre
subtracted 1/2. To calculate the End_period, we used the MIN function. This function will return the lesser of the n
years we could have taken the next year minus one-half, but this would not work for the last year. Notice that this
prior year. So, for the first year, we eliminated the MIN function. Finally, the Factor is not shown on the picture abo
used a factor of two for the three-, five-, seven-, and 10-year schedules and a factor of 1.5 for the 15- and 20-year
Finally, note that the MACRS schedule is slightly different from the table presented in the textbook for the 6th and
that the IRS publishes a MACRS schedule, which is the schedule we used in the textbook. However, you are allowed
outlined by the IRS. If you do so, you will get the table above, not the table in the textbook (or the table published
textbook for our calculations.
The three-, five-, and seven-year MACRS schedules are:
ation and the initial cost is:
beginning book value. The book value of this asset each year will be:
alue, or a tax rebate will be given if the asset is sold for less than book value. An easy way to
e). Suppose the pretax salvage value of the asset and the tax rate are:
he end of the project (Year 6 in this case) will be:
want to put all inputs in a separate cell and have Excel handle all the calculations. While we are
s well. The input information for the MMCC line of power mulching tools is:
5
$
6
7
8
6,000
110 $
5,000
110 $
4,000
110 $
3,000
110
8.93%
8.92%
8.93%
4.46%
me plus depreciation. So, the pro forma income statements each
Forma Income Statements
5
6
7
8
$ 660,000.00 $ 550,000.00 $ 440,000.00 $ 330,000.00
360,000.00
300,000.00
240,000.00
180,000.00
25,000.00
25,000.00
25,000.00
25,000.00
71,440.00
71,360.00
71,440.00
35,680.00
$ 203,560.00 $ 153,640.00 $ 103,560.00 $ 89,320.00
69,210.40
52,237.60
35,210.40
30,368.80
$ 134,349.60 $ 101,402.40 $ 68,349.60 $
71,440.00
71,360.00
71,440.00
$ 205,789.60 $ 172,762.40 $ 139,789.60 $
58,951.20
35,680.00
94,631.20
s in the variable costs, fixed costs, depreciation, and tax cells. That way, once we entered all
and pasted the year 1 net income column to the rest of the years.
calculate the difference between the beginning and ending net working capital. We also need
o, the net working capital requirements each year are:
4
5
$ 108,000.00 $ 107,250.00 $
107,250.00
99,000.00
$
750.00 $
8,250.00 $
6
99,000.00 $
82,500.00
16,500.00 $
7
82,500.00 $
66,000.00
16,500.00 $
Project Cash Flows
4
5
6
7
$ 231,972.80 $ 205,789.60 $ 172,762.40 $ 139,789.60 $
750.00
8,250.00
16,500.00
16,500.00
8
66,000.00
66,000.00
8
94,631.20
66,000.00
105,600.00
$ 232,722.80 $ 214,039.60 $ 189,262.40 $ 156,289.60 $ 266,231.20
ar schedules. The MACRS schedule is calculated using the depreciation according to the double
more advantageous. The three-, five-, seven-, and 10-year schedules use a factor of 2 (200%)
5- and 20-year schedules use a factor of 1.5 (150%). Excel has a function, VDB, which can be
h all six schedules.
Equipment Life (Years)
10
10.00%
18.00%
14.40%
11.52%
9.22%
7.37%
6.55%
6.55%
6.55%
6.55%
3.28%
15
5.00%
9.50%
8.55%
7.70%
6.93%
6.23%
5.90%
5.90%
5.90%
5.90%
5.90%
5.90%
5.90%
5.90%
5.90%
2.95%
20
3.75%
7.22%
6.68%
6.18%
5.71%
5.28%
4.89%
4.52%
4.46%
4.46%
4.46%
4.46%
4.46%
4.46%
4.46%
4.46%
4.46%
4.46%
4.46%
4.46%
2.23%
tion. Constructing the MACRS table is tricky because of the half-year convention. Below you
e answers as a percentage rather than a dollar amount. Salvage is the salvage value, which is
mn as a floating input and locked the row. This allows us to copy and paste the formula further
ch we want to calculate the depreciation. With the half-year convention, we used the year and
ction will return the lesser of the next year minus one-half, or the life of the asset. In most
for the last year. Notice that this MIN function will not work for the first year since there is no
or is not shown on the picture above since Excel scrolls through the inputs in this case. We
tor of 1.5 for the 15- and 20-year schedules.
ed in the textbook for the 6th and 8th year of the seven-year MACRS schedule. The reason is
xtbook. However, you are allowed to calculate the schedule on your own based on the rules
textbook (or the table published by the IRS!). In the future, we will use the table in the
Chapter 9 – Section 6
Scenario and Other What-If Analyses
Scenario Analysis
Scenario analysis is used to determine the range of possible outcomes for a project. Typically, the base case, best c
scenario analysis. Because of the repetitive nature of the calculations, spreadsheets are an excellent tool for doing
in the textbook:
Unit sales:
Price per unit
Variable costs per unit:
Fixed costs per year:
Initial cost:
Project life (years):
Required return:
Tax rate:
$
$
$
$
Base case
6,000
80
60
50,000
200,000
5
12%
34%
With these values, we need to calculate the base case, best case, and worst case NPVs and IRRs. First, we want to c
which are:
Base Case Income Statement
Sales
$
480,000
Variable costs
360,000
Fixed costs
50,000
Depreciation
40,000
EBIT
$
30,000
Taxes (34%)
10,200
Net income
$
19,800
OCF
$
59,800
NPV
IRR
$
15,566
15.10%
Notice, in this case we calculated the NPV and IRR using the PV function and RATE function rather than the NPV an
year, we find this calculation easier.
To calculate the best case and worst case, we will use Scenario Manager, which is described below.
RWJ Excel Tip
Scenario Manager is a powerful tool that allows you to evaluate different scenarios and is useful in cases such as th
cells that we will be changing, in this case cells D9 through D12. Next, go to the Data tab, click What-If Analysis, Sce
When you click on Add, another box comes up that will allow you to enter the scenario name. After entering the na
this:
Notice two things about this box. First, the values are changed to the best case values. This is because the image w
instead of cell names, i.e. D9, the variable in the cell comes up because we named each input cell, as well as the NP
case, we simply clicked Add, then added the worst case scenario. When the values for both scenarios are entered,
names which we have already added.
Now that all the scenarios are entered, we can click on Summary, which brings up the final box. This box allows us
cells D32 (NPV) and D33 (IRR) as the final results we wanted Scenario Manager to calculate, then clicked OK. The re
Sensitivity Analysis
In contrast to scenario analysis, sensitivity analysis holds all variables except one constant. This allows us to see how
this case, we will perform sensitivity analysis using fixed costs, although all other variables could be similarly exami
completed using a one-way data table. Below, you will see a table with the NPV for different levels of fixed costs:
$
$
$
$
$
$
$
$
$
$
Fixed costs
25,000
30,000
35,000
40,000
45,000
50,000
55,000
60,000
65,000
70,000
$
$
$
$
$
$
$
$
$
$
NPV
75,044
63,149
51,253
39,357
27,461
15,566
3,670
(8,226)
(20,122)
(32,017)
$
75,000 $
(43,913)
Graphically, the relationship between fixed costs and NPV looks like this:
Sensitivity Analysis for Fixed Cost
$100,000
$80,000
Net Present Value
$60,000
$40,000
$20,000
$$25,000 $30,000 $35,000 $40,000 $45,000
$(20,000)
$(40,000)
$(60,000)
As you can see, there is a negative relationship between fixed costs and project NPV. We would expect this: As cos
decrease.
RWJ Excel Tip
To set up a one-way data table, we need to first enter the inputs we want to use in the calculations in a column (or
right and one cell above where the input values begin, we need to make the cell equal to the final value we want th
that in our data table, this cell is C109. However, to make the data table look better, we have hidden this row. To u
and then select “Unhide.” This first step is to highlight the entire column with the numbers we want used in the cal
the adjacent column. Next, select the “Data” tab, then “What-If Analysis,” and “Data Table.” Finally, enter the origi
calculate the values in the data table, which is cell D12 for fixed costs.
We should note that when you create a data table, you can change the input cells in which you entered the new va
of the data table.
Of course, in our sensit…
Purchase answer to see full
attachment