1) The attached includes the project example we discussed in the class. Study the example and get familiar with the excel calculations. 2) Use the excel template to work out the financial forecast for your own business idea. For business idea: If you have a idea, you are encouraged to work out financial idea.You can also use the business idea from other class for our project.Or, you can pick up a start up company from a newly listed company for forecasting. Submit your work in a word document with no more than 5 pages. Briefly explain the idea and source of profit. Explain the assumptions for your business forecast. Use the template to forecast the valuation cash flow generated by the business.Calculate the NPV, IRR, and payback period for your business.6) Prepare a PPT file for class presentation.You can use your idea. Both service and product should work. Or you can borrow ideas from some start ups or tv programs like Shark Tank. The focus of this project is to translate the business idea into financial planning and forecasting.use scrub daddy product
_efu_proj2_example_bus_400_upload.docx
_efu_proj2_example_worksheet_upload.xlsx
Unformatted Attachment Preview
The Local Café: New Project Analysis
Richard Borgman and Kirk Ramsay
Kirk Ramsay was a serial entrepreneur. Another way to characterize him was as a small
businessman who enjoyed new challenges and new adventures. After earning his business
degree at the state university, Kirk built several successful businesses. He designed and built
homes, owned several rental properties, and ran a property management business, and
continued to follow his musical passion by playing lead guitar in a successful local “cover” band.
In the small city in New England where he lived, he saw a need for a local no-frills, good food,
breakfast and lunch eatery located downtown, where the national chains did not locate. He had
found his next potential project—which he planned to call “The Local Café.” And he had found
the empty storefront in which to put the restaurant. The landlord had urged Kirk to make his
decision in a few days, because he claimed to have another interested party. To make the
decision, Kirk would calculate NPV, IRR, and payback period.
The restaurant was part of a clearly defined life plan. His children were now grown; Kirk and his
wife hoped to work about ten years more to solidify their retirement, and in ten years move to
a warmer climate, hopefully fully retired or, if necessary, semi-retired. So if opened, Kirk
expected to own and operate the restaurant for about 10 years. After establishing it as a
successful business, he planned to sell the restaurant as part of his plan to move at least part of
the year to a warmer climate.
The space Kirk had found was about 3,000 square feet, enough for a kitchen and ten tables. The
monthly rent was $600, leased “triple net,” meaning that the renter was responsible for all
other costs, including utilities. He anticipated other building-related costs of $300/month for
insurance and $500/month for utilities (water, sewer, gas, trash removal). He believed these
costs would increase at least with inflation, which he assumed to be about 4 percent per year.
To prepare the space, which had not previously been a restaurant, Kirk anticipated the
following costs: stove, $1,600; double fryer, $800; sinks, $700; preparation tables, $1,500; pots
and pans, utensils, glasses, and dishes, $1,500; 10 tables and 40 chairs, $2,000 in total. The
costs to renovate the space (plumbing, electric, carpentry, and signage) would be $10,000. All
these costs were subject to depreciation. Kirk assumed a 6-year straight line depreciable life.
For the first year, Kirk assumed the following revenues. The average breakfast patron would
spend about $7.50. He assumed that with a 40-person capacity, there would be about 30
people per sitting, and about two “turns” per day. The average lunch patron would spend about
$11.50. There would be 30 people per sitting, and about three “turns” per day. The restaurant
would close in the early afternoon and would not serve dinner. The plan would be for the
restaurant to be open 5 days a week (260 days per year).
To control costs, the restaurant would not have an extensive menu, serving standard breakfast
fare (eggs and bacon and the like) and having rotating specials for lunch plus a standard menu.
Kirk anticipated “consumables and perishables” costs to be about 60 percent of revenues.
Other non-labor costs would be about 3 percent of revenues.
There would be three cooks working three overlapping 8 hour shifts, making $11 an hour. There
would be two servers working two overlapping 8 hour shifts, making $4 per hour (plus tips).
Finally, there would be a manager who normally operated a register as well, making $12 per
hour for a daily 8 hour shift.
Over the following years, Kirk expected revenues to increase at least with inflation (as before,
assumed to be about 4 percent per year). Costs would remain at the current percentage of
revenues. Net working capital (NWC) was assumed to be 5 percent of revenues in the upcoming
year (for example, NWC at t = 1 equals 5 percent of t = 2 revenues). The change in net working
capital was calculated as NWC in the current year minus NWC in the previous year (thus change
in NWC for t = 1 is NWC (t = 1) minus NWC (t = 0).
Kirk faced a 35 percent tax rate. He could borrow from a local bank at 9 percent. The loan
would have five-year amortizing, monthly payments, and no prepayment penalty. The loan was
secured by the equipment and, as was usual, required a personal guarantee by Ramsay.
He estimated his cost of equity and financing weights using data from a sample of 84 publicly
traded restaurant firms compiled by Answath Damodaran
(http://pages.stern.nyu.edu/~adamodar/). These firms had an average unlevered beta
(unlevered means the beta if there was no debt in their capital structure) of 0.69, an average
debt-to-equity (D/E) ratio of 0.2757
He knew that he could calculate a levered beta using the following simplified Hamada equation:
BetaLevered = BetaUn-levered * (1+(1-T) * (D/E))
Ramsay assumed that his mixture of debt and equity financing (D/E) would be close to the
industry average.
Ramsay also realized that on average for small business like his, there are about 10% risk
premium in addition to the industry average
Radd = 10%
Cost of Equity = Re = RLevered + Radd
where MRP is the market risk premium, gRF is the risk-free rate, and beta in this case is the total
beta. He planned to calculate his project weighted average cost of capital (WACC) as,
WACC = weRe + wdRd(1 − T)
where “w” are the weights of equity and debt, respectively, and “g” refers to the costs of equity
and debt, respectively. T is the tax rate.
In ten years, when Kirk expected to sell the restaurant to fund his retirement, he knew the
value would depend on cash flow. To be conservative, he valued the sale in ten years as a nogrowth perpetuity; that is, the firm would be worth the present value of a perpetual series of
constant cash flows (based on t = 10 cash flow). Also, to be conservative, he assumed that the
sale would be fully taxable.
Kirk wanted a payback within three years, assuming a risk-free rate of 3.04 percent and an
equity risk premium of 5 percent.
Questions to answer with your spreadsheet analysis:
I have worked out an excel template for your analysis. However, all the results are hardcoded numbers. You need to figure out the excel formula to replicate my analysis. Such an
exercise also can serve as a template for your other work in project analysis.
Once you have created a spreadsheet to analyze the project, you should be able to study other
scenarios. Here are some of the changes you need to make
a.
b.
c.
d.
e.
Reduce the lunch price from 11.5 to 11.
Inflation is scaled down from 4% to 3%.
Tax rate is scaled down from 35% to 30%.
Average S&P500 risk premium is adjusted from 5% to 5.5%.
Cost of debt adjusted from 9% to 8%.
Please include the above changes into your Excel worksheet and update the calculations. With
the updated calculations, please answer the following questions.
1. What is Ramsay’s cost of capital to use in the analysis?
2. Calculate the expected sale price of the restaurant at the end of year 10 (that is, at t =
10).
3. Calculate the project’s NPV, IRR, and payback period. Based on these calculations,
should Ramsay go ahead with the project?
4. Is the payback criteria a good indicator for the project?
Appendix 8.1 Industry-Specific Values for Unlevered Beta and Unsystematic Risk Adjustment
Factor
Examples with Capital Budgeting Criteria
Year
net cash flow
0.00
-100,000.00
1
30,000.00
2
30,000.00
3
30,000.00
4
30,000.00
cumulative cash flow
pay back period
-100,000.00
-70,000.00
—
-40,000.00
—
-10,000.00
—
20,000.00
3.33
cost of capital
Present value of future cash flows
Initial Investment
Net present value
Internal Rate of Returns
Payback period
0.15
124,812.59
-100,000.00
24,812.59
22.93%
3.33
<-- use excel NPV function
<-- initial cash outflow
<-- present value + initial cash outflow
<-- return from the project
<-- time to recover initial investment
5
30,000.00
6
30,000.00
7
30,000.00
50,000.00
--
80,000.00 110,000.00
---
Industry
Advertising
Aerospace, defense
Air transport
Apparel
Auto parts
Beverages
Beverages (alcoholic)
Biotechnology
Broadcasting
Brokerage and investment banking
Building materials
Business and consumer services
Chemical (specialty)
Computer services
Computer software
Computers, peripherals
Construction
Educational services
Electrical equipment
Electronics
Electronics (consumer and office)
Engineering
Entertainment
Environmental and waste services
Financial services
Food processing
Furniture, home furnishings
Healthcare equipment
Healthcare facilities
Healthcare products
Healthcare services
Healthcare information and technology
Heavy construction
Homebuilding
Hotel, gaming
Household products
Information services
Internet software and services
Machinery
Metals and mining
Office equipment and services
Number of
Unlevered beta
firms in sample
65
95
25
70
75
47
19
349
30
49
37
179
100
129
273
66
18
40
135
191
26
56
85
108
76
97
36
193
47
58
126
125
46
32
89
139
71
330
141
134
30
0.73
0.92
0.52
0.99
1.23
1.24
0.93
1.07
1.1
0.33
1.07
0.75
0.95
0.82
1.04
1.13
0.77
1.04
1.07
1
1.08
1.13
0.99
0.81
0.58
0.71
1.03
0.77
0.56
0.89
0.72
0.92
1.22
1.23
0.9
0.89
0.81
1.05
0.96
0.9
0.82
Oil and gas distribution
Oilfield services, equipment
Packaging and container
Paper, forest products
Pharmaceuticals and drugs
Publishing and newspapers
Real estate (development)
Real estate (operations and services)
Recreation
Restaurant
Retail (automotive)
Retail (distributors)
Retail (general)
Retail (groceries and food)
Retail (Internet)
Retail (special lines)
Semiconductor
Semiconductor equipment
Shoes
Telecommunication equipment
Telecommunication services
Trucking
Total market
80
163
24
21
138
52
22
47
70
84
30
87
21
21
47
137
104
51
14
131
82
28
7766
0.55
1.17
0.73
0.93
1.03
0.87
0.85
0.94
1.11
0.71
0.8
0.74
0.8
0.58
1.02
0.78
1.14
1.21
0.81
1.11
0.63
0.77
0.64
Unsystematic risk
adjustment factor
for beta
4.266
2.1175
2.7867
2.7381
1.8859
3.5149
2.7359
4.2222
1.9887
1.5462
1.343
2.2246
1.7817
2.9972
2.6655
2.8819
2.4059
3.3313
3.0638
3.2237
2.6005
1.5648
3.3646
3.9113
2.2173
2.6459
2.1108
3.22
2.0639
2.7051
3.2714
3.6152
1.2203
1.2515
2.5806
3.8576
1.7
4.4316
1.6138
3.8475
1.4741
1.8186
1.9525
1.0791
1.7822
3.2703
2.6891
2.8024
4.0758
2.8666
2.3116
1.8263
2.38
1.8299
2.4285
3.1829
2.2239
1.7847
1.9286
2.0437
2.57
3.4359
1.6574
2.5729
a
b
c
d
e
Increase the breakfast price from 7 to 7.5
Reduce the lunch price from 11.5 to 11.
Inflation is scaled down from 4% to 3%.
Tax rate is scaled down from 35% to 30%.
Cost of debt adjusted from 9% to 8%.
input parameter
breakfast price
lunch price
inflation
tax rate
cost of debt
Variable cost as a percentage of sales (cost of sales)
Other variable cost as pecentage of sales
7.00
11.50
4.00%
35.00%
9.00%
60.00%
3.00%
Market risk permium
Net working capital as a percentage of Revenue
Risk free rate
Unlevered beta
Unsystematic risk adjustment factor for beta
Debt / Equity ratio
Cost of debt
Radd, additional risk premium for new ventures
5.00%
5.00%
3.04%
0.71
2.65
27.57%
9.00%
<-- change the parameter for scenario analysis.
<-- from the industry table
<-- from the industry table
10.00%
<-- from your own estimate
Summary of Results
Net cash flow
Net cash flow
payback period
0
-37,015
-37,015
1
8,704
-28,311
2
9,010
-19,300
3
9,329
-9,972
Cost of Capital
Net Present Value
Internal Rate of Return
20.17%
8,222
25.37%
stove
double fryer
sinks
preparation tables
pots and pans, utensils, etc
10 tables and 40 chairs
renovation
total
Initial
1,600
800
700
1,500
1,500
2,000
10,000
18,100
breakfast
lunch
Initial estimation of revenue
yearly
109,200
269,100
378,300
consumables and perishables
other cost
total
226,980
11,349
238,329
rent
other building related costs
utilities
total
yearly
7,200
3,600
6,000
16,800
monthly
600
300
500
yearly
7200
3600
6000
3 cooks
2 servers
1 manager
total
yearly
68,640
16,640
24,960
110,240
# people
3
2
1
# of hours
8
8
8
Tax rate (T)
Rf = average10-year treasury yield
35.00%
3.04%
4
9,659
-312
5
10,004
9,691
4.03
6
10,361
20,053
7
9,678
29,731
8
10,065
39,795
9
10,468
50,263
10
45,971
96,234
<-- from calculations
<-- from calculations
Details of analysis for Local Café
Initial cost
Revenue
spending
7
11.5
customer
30
30
turns
2
3
daily$
420
1035
yearly$
109200
269100
houly pay
11
4
12
daily
264
64
96
yearly
68640
16640
24960
variable cost
fixed cost: operation expense
fixed cost: labor
<-- 60% of revenue
<-- 3% of revenue
Cost of equity
<-- given
<-- given
MRP: average S&P500 risk premium
BetaU
Unsystematic risk adjustment factor for beta
5.00%
0.71
2.65
<-- given
<-- given
<-- given
27.57%
0.84
<-- given
Systematic risk premiuim
D/E ratio
BetaL = BetaU * (1+(1-T)*(D/E))
Beta = BetaL * (Unsystematic Risk Adjustment Factor)
RL = Rf + Beta * MRP
2.22
0.14
Radd, additional risk premium for new ventures
10.00%
total cost of equity = RL + Radd
24.12%
Cost of debt
<-- based on industry survey
9.00%
<-- given
Weighted average cost of capital
Debt
Equity
weight
0.216
0.784
cost
9.00%
24.12%
after tax
cost
5.85%
24.12%
wacc
Cost
component
1.26%
18.90%
<-- after tax cost = before tax cost * (1-T)
<-- after tax cost = before tax cost
20.17%
<--sum of cost components
assumption for pro forma
cost of capital
tax rate
Inflation, growth of revenue and operating expense
Cost of sales
variable cost as an percentage of revenue
net working capital as a percentage of revenue
20.17%
35.00%
4.00%
60.00%
3.00%
5.00%
straight line depreciation %
Cash flow
year
Revenue
Cost of sales
Other variable cost
operating expense
cost of labor
depr = initial captial spending * depr %
EBIT
0
<-- from calculation
<-- from calculation
<-- given
<-- given
<-- given
<-- given
16.7%
16.7%
16.7%
16.7%
16.7%
16.7%
<-- straight line depreciation
1
378,300
226,980
11,349
16,800
110,240
3,017
9,914
2
393,432
236,059
11,803
17,472
114,650
3,017
10,432
3
409,169
245,502
12,275
18,171
119,236
3,017
10,970
4
425,536
255,322
12,766
18,898
124,005
3,017
11,529
5
442,557
265,534
13,277
19,654
128,965
3,017
12,111
6
460,260
276,156
13,808
20,440
134,124
3,017
12,716
7
478,670
287,202
14,360
21,257
139,489
0
16,362
8
497,817
298,690
14,935
22,108
145,068
0
17,016
9
517,730
310,638
15,532
22,992
150,871
0
17,697
10
538,439
323,063
16,153
23,912
156,906
0
18,405
11
559,976
335,986
16,799
24,868
163,182
0
19,141
9,461
9,797
10,147
10,510
10,889
11,282
10,635
11,061
11,503
11,963
12,442
<-- revenue growth at constant rate
<-- percentage of revenue
<-- percentage of revenue
Operating cash flow
= EBIT *(1-T) + Depr
0
Net capital spending
18,100
net working capital (NWC)
ΔNWC
18,915
18,915
19,672
757
20,458
787
21,277
818
22,128
851
23,013
885
23,934
921
24,891
957
25,886
996
26,922
1,035
27,999
1,077
<-- 5% of revenue
<-- release of net working capital at terminal
Free cash flow (FCF)
-37,015
8,704
9,010
9,329
9,659
10,004
10,361
9,678
10,065
10,468
10,886
<-- EBIT*(1-T) - NCS - ΔNWC
53,976
35,085
<- from assumption, FCF10 / cost of capital
terminal value at year 10
sales proceeds after tax
Year
net cash flow
cumulative net cash flow
0
-37,015
-37,015
payback period
cost of capital
NPV
IRR
payback period
20.17%
8,222.50
25.4%
4.03
1
8,704
-28,311
--
2
9,010
-19,300
--
3
9,329
-9,972
--
4
9,659
-312
--
5
10,004
9,691
4.03
6
10,361
20,053
--
7
9,678
29,731
--
8
10,065
39,795
--
9
10,468
50,263
--
10
45,971
96,234
--
<-- terminal value * (1-T)
<-- operation & terminal sales
...
Purchase answer to see full
attachment
