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
- FIXED COSTS: 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 - CAPEX: $30,000 every other year starting year 0. Assume you pay cash, no credit
- WORKING CAPITAL: break this section into 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
Make sure your spreadsheet is formatted to be easily readable. This means: format all numbers using the “,” or “$” command so that commas automatically appear when there are more than 3 digits in length; reduce decimal places to NONE so your sheet isn’t cluttered up with pennies; make sure all numbers are right-aligned so the digits line up in columns; use the Underline command under the last number in a column before each subtotal. This is “best practice” Excel spreadsheet hygiene and is as important in business as good grammar and correct spelling.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
