Case Spreadsheet: The Local Cafe
The process of computing future value is called compounding. The formula for computing future value is:
Future value = Present value × (1 + r)twhere r is the return per period and t is the number of time periods. For example, a real estate mutual fund provides an estimated return of 10 percent per year. An investor starts with $100,000. The future value of her investment will be:
After one year, Future value1 = $100,000 (1.10)1 = $110,000After two years, Future value2 = $100,000 (1.10)2 = $121,000After three years, Future value3 = $100,000 (1.10)3 = $133,100The future value formula takes into account compounding, i.e. the investor’s ability to earn interest on interest.The simple fixer-upper investment illustrates several common themes in project analysis. The start-up costs of a project are usually relatively straightforward to estimate. A fair amount of research is needed but the information is generally available. The size and timing of future cash flows, including estimated revenues and production costs, are harder to predict. Figure 9.1 shows the cash flows of the foreclosure fixer-upper, assuming that it will take you one year to do the remodeling and another year to sell the property.
Prepare a spreadsheet model for the case. Be sure to separate assumptions from the rest of the model and include scenario summary.
Here is a template to help you get started:
Sheet1 Assumptions Start-up Costs Stove$ 1,600.00 Double Fryer$ 800.00 Sinks$ 700.00 Prep Tables$ 1,500.00 Pots/Utensils$ 1,500.00 Tables & Chairs$ 2,000.00 Renovation$ 10,000.00 Total start-up costs$ 18,100.00 5 year MACRS YR120%$ 3,620.00 YR232%$ 5,792.00 YR319.20%$ 3,475.20 YR411.52%$ 2,085.12 YR511.52%$ 2,085.12 YR65.76%$ 1,042.56 Net Working Capital5%of next year's revenue Revenue Growth Rate4% Days Open260 Average Check# of Customers Breakfast$ 7.5060 Lunch$ 11.5090 Expenses Monthly Rent600 utilities500 Insurance300 Yearly Inflation4% Total Annual Costs$ 16,800.00 Perishables60% Non-labor3% Cooks wage$11 # of cook3.00 # of hours for cooks8.00 Cook wage increase4% Server wage$4 # of servers2.00 # of hours for servers8.00 Server wage increase4% Manager$12 # of manager hours8.00 # of manager1.00 Manager wage increase4% Taxes35% WACC Borrowing Rate9% Correlation with Market0.305 Unlevered Beta2.260 Debt To Equity Ratio0.276 Risk Free Rate0.030 Risk Premium0.050 Weight Debt0.216 Weighted equity0.784 Levered Beta2.665 Cost Of Equity16.4% Cost of Debt9.0% WACC14.1% Model Year 0Year 1Year 2Year 3Year 4Year 5Year 6Year 7Year 8Year 9Year 10 Total Revenue Perisables Cook Server Manager non-labor Annual rent, utilities and insurance Depreciation EBIT Taxes Net Income Selected Income Statement Start up NWC Cash flows Operating cash flows Start-up costs Addition to NWC Free cash flow Future business sales price* Tax on sale of business Total cash flow NPV IRR Notes *Future business sales price is a perpetuity = Free cash flow in year 10 / WACC Sheet2