Case study

The excel is provided, I just need the two-page paper done.

Initial Inputs:
Best Case
Lower Bound Upper Bound
Unit Sales
40,000
35,000
45,000
Price Per Unit
$100.00
$90.00
$110
Variable Costs Per Unit
$75.00
$70.00
$80
Fixed Costs per year $150,000.00 $125,000.00
$175,000
Required Return
14.00%
14.00%
14.00%
Tax Rate
21.00%
21.00%
21.00%
Annual Depreciation $100,000.00 $100,000.00 $100,000.00
Initial Investment
-$500,000.00 -$500,000.00 -$500,000.00
New Details: You now have been asked to consider some other inputs. The cost, life
of the project, salvage value,
straight-line depreciation, required return, and tax rate will remain the same. Now,
however, you have been asked to
consider the following additional possibilities:
1. Management is concerned that the sales projections may be too optimistic. What
happens if all of our models are too
high in terms of unit sales? What if actual sales are 25% lower than our models?
2. How would an annual increase in unit sales of 15% or an annual reduction in unit
sales of 15% impact our models?
(Hint: This is not a one-time change. It is an increase or decrease of 15% each year.)
3. Consider an annual decrease in price per unit in the best case, worst case, and
base case of 10% per unit to an
increase in price per unit of 10%. (Hint: This is not a one-time change. It is an
increase or decrease of 10% each year.)
4. Consider an annual decrease in the variable costs per unit in the best case, worst
case, and base case of 10% per unit
to an increase in variable costs per unit of 10%. (Hint: This is not a one-time change.
It is an increase or decrease of
10% each year.)
Best Case
Unit Sales
Price Per Unit
Variable Costs Per Unit
Fixed Costs per year
Required Return
Tax Rate
Annual Depreciation
Initial Investment
Worst Case
45,000
$110.00
$70.00
$125,000.00
14.00%
21.00%
$100,000.00
-$500,000.00
Base Case
35,000
$90.00
$80.00
$175,000.00
14.00%
21.00%
$100,000.00
-$500,000.00
40,000
$100
$75
$150,000
14.00%
21.00%
$100,000.00
-$500,000.00
1
$4,950,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$1,575,000.00
-$330,750.00
$1,244,250.00
$1,344,250.00
$1,179,166.67
2
$4,950,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$1,575,000.00
-$330,750.00
$1,244,250.00
$1,344,250.00
$1,034,356.73
1
$3,150,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$75,000.00
-$15,750.00
$59,250.00
$159,250.00
$139,692.98
2
$3,150,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$75,000.00
-$15,750.00
$59,250.00
$159,250.00
$122,537.70
1
$4,000,000.00
2
$4,000,000.00
Best Case
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Taxes
Net Income
Cash Flow
PV
0
-$500,000.00
-$500,000.00
-$500,000.00
NPV of Project
IRR
$4,114,919
268.45%
Worst Case
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax Reduction (Due to Loss)
Net Income
Cash Flow
PV
0
-$500,000.00
-$500,000.00
-$500,000.00
NPV of Project
IRR
$46,718
17.82%
Base Case
Year
Sales
0
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax
Net Income
Cash Flow
PV
-$500,000.00
-$500,000.00
-$500,000.00
NPV of Project
IRR
$1,877,409
136.63%
-$3,000,000.00
-$150,000.00
-$100,000.00
$750,000.00
-$157,500.00
$592,500.00
$692,500.00
$607,456.14
-$3,000,000.00
-$150,000.00
-$100,000.00
$750,000.00
-$157,500.00
$592,500.00
$692,500.00
$532,856.26
3
$4,950,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$1,575,000.00
-$330,750.00
$1,244,250.00
$1,344,250.00
$907,330.46
4
$4,950,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$1,575,000.00
-$330,750.00
$1,244,250.00
$1,344,250.00
$795,903.91
5
$4,950,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$1,575,000.00
-$330,750.00
$1,244,250.00
$1,344,250.00
$698,161.33
3
$3,150,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$75,000.00
-$15,750.00
$59,250.00
$159,250.00
$107,489.21
4
$3,150,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$75,000.00
-$15,750.00
$59,250.00
$159,250.00
$94,288.78
5
$3,150,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$75,000.00
-$15,750.00
$59,250.00
$159,250.00
$82,709.46
3
$4,000,000.00
4
$4,000,000.00
5
$4,000,000.00
-$3,000,000.00
-$150,000.00
-$100,000.00
$750,000.00
-$157,500.00
$592,500.00
$692,500.00
$467,417.77
-$3,000,000.00
-$150,000.00
-$100,000.00
$750,000.00
-$157,500.00
$592,500.00
$692,500.00
$410,015.59
-$3,000,000.00
-$150,000.00
-$100,000.00
$750,000.00
-$157,500.00
$592,500.00
$692,500.00
$359,662.80
For a decrease in unit sales by 25%
Best Case
Unit Sales
Price Per Unit
Variable Costs Per Unit
Fixed Costs per year
Required Return
Tax Rate
Annual Depreciation
Initial Investment
Worst Case
33,750
$110.00
$70.00
$125,000.00
14.00%
21.00%
$100,000.00
-$500,000.00
Base Case
26,250
$90.00
$80.00
$175,000.00
14.00%
21.00%
$100,000.00
-$500,000.00
30,000
$100
$75
$150,000
14.00%
21.00%
$100,000.00
-$500,000.00
1
$3,712,500.00
-$2,362,500.00
-$125,000.00
-$100,000.00
$1,125,000.00
-$236,250.00
$888,750.00
$988,750.00
$867,324.56
2
$3,712,500.00
-$2,362,500.00
-$125,000.00
-$100,000.00
$1,125,000.00
-$236,250.00
$888,750.00
$988,750.00
$760,811.02
1
$2,362,500.00
-$2,100,000.00
-$175,000.00
-$100,000.00
-$12,500.00
$2,625.00
-$9,875.00
$90,125.00
$79,057.02
2
$2,362,500.00
-$2,100,000.00
-$175,000.00
-$100,000.00
-$12,500.00
$2,625.00
-$9,875.00
$90,125.00
$69,348.26
1
2
Best Case
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Taxes
Net Income
Cash Flow
PV
0
-$500,000.00
-$500,000.00
-$500,000.00
NPV of Project
IRR
$2,894,459
196.89%
Worst Case
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax Reduction (Due to Loss)
Net Income
Cash Flow
PV
0
-$500,000.00
-$500,000.00
-$500,000.00
NPV of Project
IRR
($190,594)
-3.37%
Base Case
Year
0
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax
Net Income
Cash Flow
PV
-$500,000.00
-$500,000.00
-$500,000.00
NPV of Project
IRR
$1,199,375
95.54%
$3,000,000.00
-$2,250,000.00
-$150,000.00
-$100,000.00
$500,000.00
-$105,000.00
$395,000.00
$495,000.00
$434,210.53
$3,000,000.00
-$2,250,000.00
-$150,000.00
-$100,000.00
$500,000.00
-$105,000.00
$395,000.00
$495,000.00
$380,886.43
3
$3,712,500.00
-$2,362,500.00
-$125,000.00
-$100,000.00
$1,125,000.00
-$236,250.00
$888,750.00
$988,750.00
$667,378.09
4
$3,712,500.00
-$2,362,500.00
-$125,000.00
-$100,000.00
$1,125,000.00
-$236,250.00
$888,750.00
$988,750.00
$585,419.37
5
$3,712,500.00
-$2,362,500.00
-$125,000.00
-$100,000.00
$1,125,000.00
-$236,250.00
$888,750.00
$988,750.00
$513,525.77
3
$2,362,500.00
-$2,100,000.00
-$175,000.00
-$100,000.00
-$12,500.00
$2,625.00
-$9,875.00
$90,125.00
$60,831.81
4
$2,362,500.00
-$2,100,000.00
-$175,000.00
-$100,000.00
-$12,500.00
$2,625.00
-$9,875.00
$90,125.00
$53,361.23
5
$2,362,500.00
-$2,100,000.00
-$175,000.00
-$100,000.00
-$12,500.00
$2,625.00
-$9,875.00
$90,125.00
$46,808.10
3
4
5
$3,000,000.00
-$2,250,000.00
-$150,000.00
-$100,000.00
$500,000.00
-$105,000.00
$395,000.00
$495,000.00
$334,110.90
$3,000,000.00
-$2,250,000.00
-$150,000.00
-$100,000.00
$500,000.00
-$105,000.00
$395,000.00
$495,000.00
$293,079.74
$3,000,000.00
-$2,250,000.00
-$150,000.00
-$100,000.00
$500,000.00
-$105,000.00
$395,000.00
$495,000.00
$257,087.49
an annual increase in unit sales of 15%
Best Case
Worst Case
Base Case
Unit Sales
45,000
35,000
40,000
Price Per Unit
$110.00
$90.00
$100
Variable Costs Per Unit
$70.00
$80.00
$75
Fixed Costs per year
$125,000.00
$175,000.00
$150,000
Required Return
14.00%
14.00%
14.00%
Tax Rate
21.00%
21.00%
21.00%
Annual Depreciation
$100,000.00
$100,000.00
$100,000.00
Initial Investment
-$500,000.00
-$500,000.00
-$500,000.00
Best Case
New Unit Sales
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Taxes
Net Income
Cash Flow
PV
NPV of Project
IRR
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax Reduction (Due to Loss)
Net Income
Cash Flow
PV
NPV of Project
IRR
0
-$500,000.00
-$500,000.00
-$500,000.00
$5,580,302
284.00%
Worst Case
New Unit Sales
0
-$500,000.00
-$500,000.00
-$500,000.00
51,750
2
$5,692,500.00
-$3,622,500.00
-$125,000.00
-$100,000.00
$1,845,000.00
-$387,450.00
$1,457,550.00
$1,557,550.00
$1,198,484.15
35,000
1
$3,150,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$75,000.00
-$15,750.00
$59,250.00
$159,250.00
$139,692.98
40,250
2
$3,622,500.00
-$3,220,000.00
-$175,000.00
-$100,000.00
$127,500.00
-$26,775.00
$100,725.00
$200,725.00
$154,451.37
40,000
1
$4,000,000.00
46,000
2
$4,600,000.00
$331,654
35.20%
Base Case
New Unit Sales
Year
Sales
45,000
1
$4,950,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$1,575,000.00
-$330,750.00
$1,244,250.00
$1,344,250.00
$1,179,166.67
0
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax
Net Income
Cash Flow
PV
-$500,000.00
-$500,000.00
-$500,000.00
NPV of Project
IRR
$2,691,510
152.49%
-$3,000,000.00
-$150,000.00
-$100,000.00
$750,000.00
-$157,500.00
$592,500.00
$692,500.00
$607,456.14
-$3,450,000.00
-$150,000.00
-$100,000.00
$900,000.00
-$189,000.00
$711,000.00
$811,000.00
$624,038.17
an annual decrease in unit sales of 15%
Unit Sales
Price Per Unit
Variable Costs Per Unit
Fixed Costs per year
Required Return
Tax Rate
Annual Depreciation
Initial Investment
59,513
3
$6,546,375.00
-$4,165,875.00
-$125,000.00
-$100,000.00
$2,155,500.00
-$452,655.00
$1,702,845.00
$1,802,845.00
$1,216,869.02
68,439
4
$7,528,331.25
-$4,790,756.25
-$125,000.00
-$100,000.00
$2,512,575.00
-$527,640.75
$1,984,934.25
$2,084,934.25
$1,234,448.45
78,705
5
$8,657,580.94
-$5,509,369.69
-$125,000.00
-$100,000.00
$2,923,211.25
-$613,874.36
$2,309,336.89
$2,409,336.89
$1,251,334.08
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Taxes
Net Income
Cash Flow
PV
NPV of Project
IRR
46,288
3
$4,165,875.00
-$3,703,000.00
-$175,000.00
-$100,000.00
$187,875.00
-$39,453.75
$148,421.25
$248,421.25
$167,677.27
53,231
4
$4,790,756.25
-$4,258,450.00
-$175,000.00
-$100,000.00
$257,306.25
-$54,034.31
$203,271.94
$303,271.94
$179,561.33
61,215
5
$5,509,369.69
-$4,897,217.50
-$175,000.00
-$100,000.00
$337,152.19
-$70,801.96
$266,350.23
$366,350.23
$190,270.83
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax Reduction (Due to Loss)
Net Income
Cash Flow
PV
NPV of Project
IRR
52,900
3
$5,290,000.00
60,835
4
$6,083,500.00
69,960
5
$6,996,025.00
Year
Sales
-$3,967,500.00
-$150,000.00
-$100,000.00
$1,072,500.00
-$225,225.00
$847,275.00
$947,275.00
$639,383.64
-$4,562,625.00
-$150,000.00
-$100,000.00
$1,270,875.00
-$266,883.75
$1,003,991.25
$1,103,991.25
$653,651.45
-$5,247,018.75
-$150,000.00
-$100,000.00
$1,499,006.25
-$314,791.31
$1,184,214.94
$1,284,214.94
$666,981.00
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax
Net Income
Cash Flow
PV
NPV of Project
IRR
in unit sales of 15%
Best Case
Worst Case
45,000
$110.00
$70.00
$125,000.00
14.00%
21.00%
$100,000.00
-$500,000.00
Best Case
New Unit Sales
0
-$500,000.00
-$500,000.00
-$500,000.00
$3,006,543
252.73%
Worst Case
New Unit Sales
0
-$500,000.00
-$500,000.00
-$500,000.00
($168,799)
-5.50%
Base Case
New Unit Sales
0
Base Case
35,000
$90.00
$80.00
$175,000.00
14.00%
21.00%
$100,000.00
-$500,000.00
40,000
$100
$75
$150,000
14.00%
21.00%
$100,000.00
-$500,000.00
45,000
1
$4,950,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$1,575,000.00
-$330,750.00
$1,244,250.00
$1,344,250.00
$1,179,166.67
38,250
2
$4,207,500.00
-$2,677,500.00
-$125,000.00
-$100,000.00
$1,305,000.00
-$274,050.00
$1,030,950.00
$1,130,950.00
$870,229.30
32,513
3
$3,576,375.00
-$2,275,875.00
-$125,000.00
-$100,000.00
$1,075,500.00
-$225,855.00
$849,645.00
$949,645.00
$640,983.33
27,636
4
$3,039,918.75
-$1,934,493.75
-$125,000.00
-$100,000.00
$880,425.00
-$184,889.25
$695,535.75
$795,535.75
$471,021.03
35,000
1
$3,150,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$75,000.00
-$15,750.00
$59,250.00
$159,250.00
$139,692.98
29,750
2
$2,677,500.00
-$2,380,000.00
-$175,000.00
-$100,000.00
$22,500.00
-$4,725.00
$17,775.00
$117,775.00
$90,624.04
25,288
3
$2,275,875.00
-$2,023,000.00
-$175,000.00
-$100,000.00
-$22,125.00
$4,646.25
-$17,478.75
$82,521.25
$55,699.49
21,494
4
$1,934,493.75
-$1,719,550.00
-$175,000.00
-$100,000.00
-$60,056.25
$12,611.81
-$47,444.44
$52,555.56
$31,117.11
40,000
1
$4,000,000.00
34,000
2
$3,400,000.00
28,900
3
$2,890,000.00
24,565
4
$2,456,500.00
-$500,000.00
-$500,000.00
-$500,000.00
$1,261,644
120.13%
-$3,000,000.00
-$150,000.00
-$100,000.00
$750,000.00
-$157,500.00
$592,500.00
$692,500.00
$607,456.14
-$2,550,000.00
-$150,000.00
-$100,000.00
$600,000.00
-$126,000.00
$474,000.00
$574,000.00
$441,674.36
-$2,167,500.00
-$150,000.00
-$100,000.00
$472,500.00
-$99,225.00
$373,275.00
$473,275.00
$319,447.14
-$1,842,375.00
-$150,000.00
-$100,000.00
$364,125.00
-$76,466.25
$287,658.75
$387,658.75
$229,525.10
23,490
5
$2,583,930.94
-$1,644,319.69
-$125,000.00
-$100,000.00
$714,611.25
-$150,068.36
$564,542.89
$664,542.89
$345,142.75
18,270
5
$1,644,319.69
-$1,461,617.50
-$175,000.00
-$100,000.00
-$92,297.81
$19,382.54
-$72,915.27
$27,084.73
$14,066.96
20,880
5
$2,088,025.00
-$1,566,018.75
-$150,000.00
-$100,000.00
$272,006.25
-$57,121.31
$214,884.94
$314,884.94
$163,541.37
an annual increase in price per unit of 10%
Best Case
Worst Case
Base Case
Unit Sales
45,000
35,000
40,000
Price Per Unit
$110.00
$90.00
$100
Variable Costs Per Unit
$70.00
$80.00
$75
Fixed Costs per year
$125,000.00
$175,000.00
$150,000
Required Return
14.00%
14.00%
14.00%
Tax Rate
21.00%
21.00%
21.00%
Annual Depreciation
$100,000.00
$100,000.00
$100,000.00
Initial Investment
-$500,000.00
-$500,000.00
-$500,000.00
Best Case
New price per unit
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Taxes
Net Income
Cash Flow
PV
NPV of Project
IRR
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax Reduction (Due to Loss)
Net Income
Cash Flow
PV
NPV of Project
IRR
0
-$500,000.00
-$500,000.00
-$500,000.00
$6,679,067
295.43%
Worst Case
New price per unit
0
-$500,000.00
-$500,000.00
-$500,000.00
121
2
$5,445,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$2,070,000.00
-$434,700.00
$1,635,300.00
$1,735,300.00
$1,335,257.00
90
1
$3,150,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$75,000.00
-$15,750.00
$59,250.00
$159,250.00
$139,692.98
99
2
$3,465,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$390,000.00
-$81,900.00
$308,100.00
$408,100.00
$314,019.70
100
1
$4,000,000.00
111
2
$4,440,000.00
$1,678,448
79.31%
Base Case
New price per unit
Year
Sales
110
1
$4,950,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$1,575,000.00
-$330,750.00
$1,244,250.00
$1,344,250.00
$1,179,166.67
0
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax
Net Income
Cash Flow
PV
-$500,000.00
-$500,000.00
-$500,000.00
NPV of Project
IRR
$4,178,012
176.53%
-$3,000,000.00
-$150,000.00
-$100,000.00
$750,000.00
-$157,500.00
$592,500.00
$692,500.00
$607,456.14
-$3,000,000.00
-$150,000.00
-$100,000.00
$1,190,000.00
-$249,900.00
$940,100.00
$1,040,100.00
$800,323.18
an annual decrease in price per unit of 10%
Unit Sales
Price Per Unit
Variable Costs Per Unit
Fixed Costs per year
Required Return
Tax Rate
Annual Depreciation
Initial Investment
133
3
$5,989,500.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$2,614,500.00
-$549,045.00
$2,065,455.00
$2,165,455.00
$1,461,620.44
146
4
$6,588,450.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$3,213,450.00
-$674,824.50
$2,538,625.50
$2,638,625.50
$1,562,278.12
161
5
$7,247,295.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$3,872,295.00
-$813,181.95
$3,059,113.05
$3,159,113.05
$1,640,744.33
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Taxes
Net Income
Cash Flow
PV
NPV of Project
IRR
109
3
$3,811,500.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$736,500.00
-$154,665.00
$581,835.00
$681,835.00
$460,219.20
120
4
$4,192,650.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$1,117,650.00
-$234,706.50
$882,943.50
$982,943.50
$581,981.46
132
5
$4,611,915.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$1,536,915.00
-$322,752.15
$1,214,162.85
$1,314,162.85
$682,535.00
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax Reduction (Due to Loss)
Net Income
Cash Flow
PV
NPV of Project
IRR
123
3
$4,928,400.00
137
4
$5,470,524.00
152
5
$6,072,281.64
Year
Sales
-$3,000,000.00
-$150,000.00
-$100,000.00
$1,678,400.00
-$352,464.00
$1,325,936.00
$1,425,936.00
$962,466.18
-$3,000,000.00
-$150,000.00
-$100,000.00
$2,220,524.00
-$466,310.04
$1,754,213.96
$1,854,213.96
$1,097,843.52
-$3,000,000.00
-$150,000.00
-$100,000.00
$2,822,281.64
-$592,679.14
$2,229,602.50
$2,329,602.50
$1,209,922.54
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax
Net Income
Cash Flow
PV
NPV of Project
IRR
in price per unit of 10%
Best Case
Worst Case
45,000
$110.00
$70.00
$125,000.00
14.00%
21.00%
$100,000.00
-$500,000.00
Best Case
New price per unit
0
-$500,000.00
-$500,000.00
-$500,000.00
$1,986,606
237.40%
Worst Case
New price per unit
0
-$500,000.00
-$500,000.00
-$500,000.00
($1,307,663)
#NUM!
Base Case
New price per unit
0
Base Case
35,000
$90.00
$80.00
$175,000.00
14.00%
21.00%
$100,000.00
-$500,000.00
40,000
$100
$75
$150,000
14.00%
21.00%
$100,000.00
-$500,000.00
110
1
$4,950,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$1,575,000.00
-$330,750.00
$1,244,250.00
$1,344,250.00
$1,179,166.67
99
2
$4,455,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$1,080,000.00
-$226,800.00
$853,200.00
$953,200.00
$733,456.45
89
3
$4,009,500.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$634,500.00
-$133,245.00
$501,255.00
$601,255.00
$405,830.00
80
4
$3,608,550.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$233,550.00
-$49,045.50
$184,504.50
$284,504.50
$168,449.50
90
1
$3,150,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$75,000.00
-$15,750.00
$59,250.00
$159,250.00
$139,692.98
81
2
$2,835,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
-$240,000.00
$50,400.00
-$189,600.00
-$89,600.00
-$68,944.29
73
3
$2,551,500.00
-$2,800,000.00
-$175,000.00
-$100,000.00
-$523,500.00
$109,935.00
-$413,565.00
-$313,565.00
-$211,647.44
66
4
$2,296,350.00
-$2,800,000.00
-$175,000.00
-$100,000.00
-$778,650.00
$163,516.50
-$615,133.50
-$515,133.50
-$305,000.39
100
1
$4,000,000.00
90
2
$3,600,000.00
81
3
$3,240,000.00
73
4
$2,916,000.00
-$500,000.00
-$500,000.00
-$500,000.00
$157,560
-7.45%
-$3,000,000.00
-$150,000.00
-$100,000.00
$750,000.00
-$157,500.00
$592,500.00
$692,500.00
$607,456.14
-$3,000,000.00
-$150,000.00
-$100,000.00
$350,000.00
-$73,500.00
$276,500.00
$376,500.00
$289,704.52
-$3,000,000.00
-$150,000.00
-$100,000.00
-$10,000.00
$2,100.00
-$7,900.00
$92,100.00
$62,164.88
-$3,000,000.00
-$150,000.00
-$100,000.00
-$334,000.00
$70,140.00
-$263,860.00
-$163,860.00
-$97,018.27
72
5
$3,247,695.00
-$3,150,000.00
-$125,000.00
-$100,000.00
-$127,305.00
$26,734.05
-$100,570.95
-$570.95
-$296.53
59
5
$2,066,715.00
-$2,800,000.00
-$175,000.00
-$100,000.00
-$1,008,285.00
$211,739.85
-$796,545.15
-$696,545.15
-$361,763.72
66
5
$2,624,400.00
-$3,000,000.00
-$150,000.00
-$100,000.00
-$625,600.00
$131,376.00
-$494,224.00
-$394,224.00
-$204,747.59
an annual increase in variable costs per unit of 10%
Best Case
Worst Case
Base Case
Unit Sales
45,000
35,000
40,000
Price Per Unit
$110.00
$90.00
$100
Variable Costs Per Unit
$70.00
$80.00
$75
Fixed Costs per year
$125,000.00
$175,000.00
$150,000
Required Return
14.00%
14.00%
14.00%
Tax Rate
21.00%
21.00%
21.00%
Annual Depreciation
$100,000.00
$100,000.00
$100,000.00
Initial Investment
-$500,000.00
-$500,000.00
-$500,000.00
Best Case
variable costs per unit
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Taxes
Net Income
Cash Flow
PV
0
-$500,000.00
-$500,000.00
-$500,000.00
NPV of Project
IRR
$2,483,189
248.08%
Worst Case
variable costs per unit
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax Reduction (Due to Loss)
Net Income
Cash Flow
PV
NPV of Project
IRR
Year
Sales
0
-$500,000.00
-$500,000.00
-$500,000.00
($1,403,709)
#NUM!
Base Case
variable costs per unit
0
70
1
$4,950,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$1,575,000.00
-$330,750.00
$1,244,250.00
$1,344,250.00
$1,179,166.67
77
2
$4,950,000.00
-$3,465,000.00
-$125,000.00
-$100,000.00
$1,260,000.00
-$264,600.00
$995,400.00
$1,095,400.00
$842,874.73
80
1
$3,150,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$75,000.00
-$15,750.00
$59,250.00
$159,250.00
$139,692.98
88
2
$3,150,000.00
-$3,080,000.00
-$175,000.00
-$100,000.00
-$205,000.00
$43,050.00
-$161,950.00
-$61,950.00
-$47,668.51
75
1
$4,000,000.00
83
2
$4,000,000.00
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax
Net Income
Cash Flow
PV
-$500,000.00
-$500,000.00
-$500,000.00
NPV of Project
IRR
$151,956
-3.19%
-$3,000,000.00
-$150,000.00
-$100,000.00
$750,000.00
-$157,500.00
$592,500.00
$692,500.00
$607,456.14
-$3,330,000.00
-$150,000.00
-$100,000.00
$420,000.00
-$88,200.00
$331,800.00
$431,800.00
$332,256.08
an annual decrease in variable costs per unit o
Unit Sales
Price Per Unit
Variable Costs Per Unit
Fixed Costs per year
Required Return
Tax Rate
Annual Depreciation
Initial Investment
85
3
$4,950,000.00
-$3,811,500.00
-$125,000.00
-$100,000.00
$913,500.00
-$191,835.00
$721,665.00
$821,665.00
$554,600.47
93
4
$4,950,000.00
-$4,192,650.00
-$125,000.00
-$100,000.00
$532,350.00
-$111,793.50
$420,556.50
$520,556.50
$308,211.24
102
5
$4,950,000.00
-$4,611,915.00
-$125,000.00
-$100,000.00
$113,085.00
-$23,747.85
$89,337.15
$189,337.15
$98,335.78
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Taxes
Net Income
Cash Flow
PV
NPV of Project
IRR
97
3
$3,150,000.00
-$3,388,000.00
-$175,000.00
-$100,000.00
-$513,000.00
$107,730.00
-$405,270.00
-$305,270.00
-$206,048.55
106
4
$3,150,000.00
-$3,726,800.00
-$175,000.00
-$100,000.00
-$851,800.00
$178,878.00
-$672,922.00
-$572,922.00
-$339,215.82
117
5
$3,150,000.00
-$4,099,480.00
-$175,000.00
-$100,000.00
-$1,224,480.00
$257,140.80
-$967,339.20
-$867,339.20
-$450,468.80
Year
Sales
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax Reduction (Due to Loss)
Net Income
Cash Flow
PV
NPV of Project
IRR
92
3
$4,000,000.00
103
4
$4,000,000.00
114
5
$4,000,000.00
Year
Sales
-$3,696,300.00
-$150,000.00
-$100,000.00
$53,700.00
-$11,277.00
$42,423.00
$142,423.00
$96,131.47
-$4,102,893.00
-$150,000.00
-$100,000.00
-$352,893.00
$74,107.53
-$278,785.47
-$178,785.47
-$105,855.35
-$4,554,211.23
-$150,000.00
-$100,000.00
-$804,211.23
$168,884.36
-$635,326.87
-$535,326.87
-$278,032.00
Variable Costs
Fixed Costs
Depreciation
EBIT
Tax
Net Income
Cash Flow
PV
NPV of Project
IRR
in variable costs per unit of 10%
Best Case
Worst Case
Base Case
45,000
35,000
40,000
$110.00
$90.00
$100
$70.00
$80.00
$75
$125,000.00
$175,000.00
$150,000
14.00%
14.00%
14.00%
21.00%
21.00%
21.00%
$100,000.00
$100,000.00
$100,000.00
-$500,000.00
-$500,000.00
-$500,000.00
Best Case
variable costs per unit
0
-$500,000.00
-$500,000.00
-$500,000.00
70
1
$4,950,000.00
-$3,150,000.00
-$125,000.00
-$100,000.00
$1,575,000.00
-$330,750.00
$1,244,250.00
$1,344,250.00
$1,179,166.67
63
2
$4,950,000.00
-$2,835,000.00
-$125,000.00
-$100,000.00
$1,890,000.00
-$396,900.00
$1,493,100.00
$1,593,100.00
$1,225,838.72
57
3
$4,950,000.00
-$2,551,500.00
-$125,000.00
-$100,000.00
$2,173,500.00
-$456,435.00
$1,717,065.00
$1,817,065.00
$1,226,467.12
51
4
$4,950,000.00
-$2,296,350.00
-$125,000.00
-$100,000.00
$2,428,650.00
-$510,016.50
$1,918,633.50
$2,018,633.50
$1,195,193.08
80
1
$3,150,000.00
-$2,800,000.00
-$175,000.00
-$100,000.00
$75,000.00
-$15,750.00
$59,250.00
$159,250.00
$139,692.98
72
2
$3,150,000.00
-$2,520,000.00
-$175,000.00
-$100,000.00
$355,000.00
-$74,550.00
$280,450.00
$380,450.00
$292,743.92
65
3
$3,150,000.00
-$2,268,000.00
-$175,000.00
-$100,000.00
$607,000.00
-$127,470.00
$479,530.00
$579,530.00
$391,166.24
58
4
$3,150,000.00
-$2,041,200.00
-$175,000.00
-$100,000.00
$833,800.00
-$175,098.00
$658,702.00
$758,702.00
$449,212.49
75
1
$4,000,000.00
68
2
$4,000,000.00
61
3
$4,000,000.00
55
4
$4,000,000.00
$5,469,300
285.14%
Worst Case
variable costs per unit
0
-$500,000.00
-$500,000.00
-$500,000.00
$1,250,612
69.87%
Base Case
variable costs per unit
0
-$500,000.00
-$500,000.00
-$500,000.00
$3,167,295
163.11%
-$3,000,000.00
-$150,000.00
-$100,000.00
$750,000.00
-$157,500.00
$592,500.00
$692,500.00
$607,456.14
-$2,700,000.00
-$150,000.00
-$100,000.00
$1,050,000.00
-$220,500.00
$829,500.00
$929,500.00
$715,220.07
-$2,430,000.00
-$150,000.00
-$100,000.00
$1,320,000.00
-$277,200.00
$1,042,800.00
$1,142,800.00
$771,357.45
-$2,187,000.00
-$150,000.00
-$100,000.00
$1,563,000.00
-$328,230.00
$1,234,770.00
$1,334,770.00
$790,290.99
46
5
$4,950,000.00
-$2,066,715.00
-$125,000.00
-$100,000.00
$2,658,285.00
-$558,239.85
$2,100,045.15
$2,200,045.15
$1,142,634.51
52
5
$3,150,000.00
-$1,837,080.00
-$175,000.00
-$100,000.00
$1,037,920.00
-$217,963.20
$819,956.80
$919,956.80
$477,796.73
49
5
$4,000,000.00
-$1,968,300.00
-$150,000.00
-$100,000.00
$1,781,700.00
-$374,157.00
$1,407,543.00
$1,507,543.00
$782,970.59
CASE STUDY – 50 POINTS
THE ULTIMATE RETRO GAMING SYSTEM
Initial Inputs: A technology firm is considering developing “The Ultimate Retro Gaming System” which will include
thousands of games, highlighted by many of the classics from the 1970s, 1980s, and 1990s. This project under
consideration costs $500,000, has a five-year life, and has no salvage value. Depreciation is straight-line to zero. The
required return is 14%, and the tax rate is 21%. In addition, we have compiled the following information:
Unit sales
Price per unit
Variable costs per unit
Fixed Costs per year
Base Case
Lower Bound
Upper Bound
40,000
$100
$75
$150,000
35,000
$90
$70
$125,000
45,000
$110
$80
$175,000
New Details: You now have been asked to consider some other inputs. The cost, life of the project, salvage value,
straight-line depreciation, required return, and tax rate will remain the same. Now, however, you have been asked to
consider the following additional possibilities:
1. Management is concerned that the sales projections may be too optimistic. What happens if all of our models are too
high in terms of unit sales? What if actual sales are 25% lower than our models?
2. How would an annual increase in unit sales of 15% or an annual reduction in unit sales of 15% impact our models?
(Hint: This is not a one-time change. It is an increase or decrease of 15% each year.)
3. Consider an annual decrease in price per unit in the best case, worst case, and base case of 10% per unit to an
increase in price per unit of 10%. (Hint: This is not a one-time change. It is an increase or decrease of 10% each year.)
4. Consider an annual decrease in the variable costs per unit in the best case, worst case, and base case of 10% per unit
to an increase in variable costs per unit of 10%. (Hint: This is not a one-time change. It is an increase or decrease of
10% each year.)
5. Consider any other potential changes to the model you may want to run. (This step is optional, but I want to allow for
your own creativity.)
Develop models in Excel to consider these possibilities outlined above and resulting NPVs. Then, write a brief report
(no more than 2-3 pages) describing what you have found. There is no need to highlight every single calculation or
model in your written analysis. Focus on the big picture, and highlight the key projections that support your
recommendations! What is your advice to management regarding the project? What, if anything, concerns you
about the possibilities outlined above? What potential opportunity do you see? You may find it helpful to use the
Excel model which I shared for the problem from Chapter 11 in Canvas as a starting point, but you will need to
conduct further analysis. The report and models which you run for this case study will be worth 50 points toward
your final course grade, 10% of your final grade. IMPORTANT NOTE: You may work with others in terms of
developing your Excel models! In fact, I encourage doing so. However, the final written analysis is to be YOUR
INDIVIDUAL WORK ONLY. If students turn in identical written analysis, that will be considered academic dishonesty
and penalized accordingly.