Question

Forecasting Cash Flow and Burn Rate

1. Create a Cash Flow Forecast on Excel using the following assumptions:
   Forecast duration: Years 0 through 5, then Exit
   Unit Sales: Sell 2000 units your first year and increase 30% per year
   Price: $100/unit first year and increase 5% per year
   COGS: Calculate based on a 75% Gross Profit Margin
   NOTE: to complete the Operating Expense section, break it into two lines: Payroll and Other
   Payroll: Start with 2 employees in year 0 paid $50,000 each; add 1 employee with every additional year, at same pay
   Other Operating Expenses: $75,000 per year starting year 0, no change over the 5 years
   Capital Expenditures: $30,000 every other year starting year 0. Assume you pay in cash, no credit
   NOTE: to complete the Working Capital section, break it into 1 line for each of the 4 components
   Increase in Accounts Receivable: Based on giving customers 60 days of credit
   Increase in Inventory: Based on keeping 3 months of Inventory on hand
   Increase in Accounts Payable: Based on your vendors giving you 30 days of credit
   Increase in Accrued Payroll: Based on paying your employees every other week
   Exit Sell company for 5x Year 5 EBITDA

Answer 1

Unit Sales in First year = 2000 units | Growth in Unit Sales = 30%

Unit Sales in each year will be:

Year 1 = 2000 | Year 2 = 2000 * (1+30%) = 2600 | Year 3 = 3380 | Year 4 = 4394 | Year 5 = 5712

Price/unit = 100 in first year | Growth in Price = 5%

Price in each year will be:

Year 1 = 100 | Year 2 = 100 * (1+5%) = 105 | Year 3 = 110.25 | Year 4 = 115.76 | Year 5 = 121.55

Now Sales for each year can be calculated using Price/unit * Units sold formula

Sales in each year will be:

Year 1 = 2000 * 100 = 200,000 | Year 2 = 273,000 | Year 3 = 372,645 | Year 4 = 508,660.43 | Year 5 = 694,321.48

Gross Profit Margin = 75%

Therefore, Gross Profit in each will be:

Year 1 = 200,000 * 75% = 150,000 | Year 2 = 204,750 | Year 3 = 279,483.75 | Year 4 = 381,495.32 | Year 5 = 520,741.11

Using Gross Profit, we can find the COGS for each year which will be:

Year 1 = 200,000 - 150,000 = 50,000 | Year 2 = 68,250 | Year 3 = 93,161.25 | Year 4 = 127,165.11 |

Year 5 = 173,580.37

Number of employees in Year 0 = 2 | Addition of 1 each year

Number of employees in each year will be:

Year 0 = 2 | Year 1 = 2 +1 = 3 | Year 2 = 4 | Year 3 = 5 | Year 4 = 6 | Year 5 = 7

Cost per employee = 50,000

Total Payroll in each year will be:

Year 0 = 2*50,000 = 100,000 | Year 1 = 150,000 | Year 2 = 200,000 | Year 3 = 250,000 | Year 4 = 300,000 |

Year 5 = 350,000

Other Operating Expenses = 75,000 each year with no change

Total Operating Expenses in each year will be:

Year 0 = 100,000 + 75,000 = 175,000 | Year 1 = 225,000 | Year 2 = 275,000 | Year 3 = 325,000 | Year 4 = 375,000 |

Year 5 = 425,000

Operating Income = Gross Profit - Operating Expenses

Operating Income in each year will be:

Year 0 = 0 - 175,000 = -175,000 | Year 1 = -75,000 | Year 2 = -70,250 | Year 3 = -45,516.25 | Yea 4 = 6,495.32

Year 5 = 95,741.11

The Operating Income is also the EBITDA which will be used to calculate 5x Year 5 EBITDA Selling Price of the business

Capital Expenditure = 30,000 every other year which means Year 0 will have the Capex, then Year 1 will not, then Year 2 will have the Capex

Capex in each year will be:

Year 0 = 30,000 | Year 1 = 0 | Year 2 = 30,000 | Year 3 = 0 | Year 4 = 30,000 | Year 5 = 0

For calculation of each component of Net Working Capital, the given days have been converted into Months, assuming 30 days a month.

Days Sales Outstanding or Credit term = 60 days or 2 months which is now Months Sales Outstanding

Account Receivable = (Months Sales Outstanding / 12) * Sales

Accounts Receivable in each year will be:

Year 1 = 2/12 * 200,000 = 33,333.33 | Year 2 = 45,500 | Year 3 = 62,107.5 | Year 4 = 84,776.74 |

Year 5 = 115,720.25

Months in Inventory = 3 months

Inventory = (Months in Inventory / 12)*COGS

Inventory in each year will be:

Year 1 = 3/12 * 50,000 = 12,500 | Year 2 = 17,062.5 | Year 3 = 23,290.31 | Year 4 = 31,791.28 | Year 5 = 43,395.09

Days Payable Outstanding = 30 or Months Payable outstanding = 1

Accounts Payable = (Months Payable outstanding / 12)*COGS

Accounts Payable in each year will be:

Year 1 = 1/12*50,000 = 4,166.67 | Year 2 = 5,687.5 | Year 3 = 7,763.44 | Year 4 = 10,597.09 | Year 5 = 14,465.03

Weeks Payroll outstanding = 2 or Months Payroll Outstanding = 0.5

Accrued Payroll = (Months Payroll outstanding / 12)*Payroll

Accrued Payroll in each year will be:

Year 0 = 0.5/12*100,000 = 4,166.67 | Year 1 = 6,250 | Year 2 = 8,333.33 | Year 3 = 10,416.67 | Year 4 = 12,500 |

Year 5 = 14,465.03

Net Working Capital = (Receivable + Inventory) - (Payable + Accrued Payroll)

Net Working capital in each year will be:

Year 0 = (0 + 0) - (0 + 4,166.67) = -4,166.67 | Year 1 = 35,416.67 | Year 2 = 48,541.67 | Year 3 = 67,217.71 |

Year 4 = 93,470.92 | Year 5 = 130,066.98

Change in NWC = Current Year NWC - Previous Year NWC

Change in NWC for each year will be:

Year 1 = 35,416.67 - (-4,166.67) = 39,583.33 | Year 2 = 13,125 | Year 3 = 18,676.04 | Year 4 = 26,253.21 |

Year 5 = 36,596.05

Free cashflow to the firm = Operating Profit - Capex - Change in NWC

Free cashflow to the firm in each year will be:

Year 0 = -175,000 - 30,000 - (-4,166.67) = 200,833.33 | Year 1 = -114,583.33 | Year 2 = -113,375

Year 3 = -64,192.29 | Year 4 = -49,757.89 | Year 5 = 59,145.06

Finally, as we need Selling price at 5x of Year 5 EBITDA

Selling Price = 5 * Year 5 EBITDA = 5 * 95,741.11 = $ 478,705.55

Hence, Selling Price of Business at 5x of Year 5 EBITDA is $ 478,705.55 or $ 478,706

Below is the spreadsheet, created for the problem, for your reference: