Chat with us, powered by LiveChat BUS400 Scrub Daddy Product Financial Management Project | acewriters
+1(978)310-4246 credencewriters@gmail.com
  

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

error: Content is protected !!