Strategic & Business Planning
Romy 123-
BusinessPlans_FinancialTemplates.xlsHow to use this template
| How to use this template | ||
| Save the template (XLT file) to your computer. Double-click on it to create a new plan, then follow these steps: | ||
| Step 1 | Set a starting point for your projections | |
| Start date | 1/1/09 | |
| Step 2 | Set-up costs | |
| Use the set-up costs sheet to calculate the cost of setting up your business. | ||
| Step 3 | Profit and loss forecast | |
| Forecast your month-by-month profit and loss for the first 12 months of operations. Enter sales in the month when they're invoiced, rather than the month when they're paid. | ||
| Step 4 | Cash flow forecast | |
| Forecast your month-by-month cash flow for the first 12 months of operations. Enter sales revenue in the month when it's received, rather than the month when it's invoiced. | ||
| Step 5 | Balance sheet forecast | |
| Forecast your balance sheet position at the end of the first 12 months of operation. The balance sheet draws on the figures you've entered in your profit and loss forecast. | ||
| Step 6 | Break-even analysis | |
| Find out how many sales you need to make in the first 12 months to cover your fixed costs and break even. | ||
| Tips | ||
| ■ | Enter figures in any white field. The yellow fields are calculated for you. | |
| ■ | Enter as much or as little information as you like. The more information you record, the better your analysis. | |
| ■ | By default, the sheets in this template are locked, so that the formulas cannot be changed. If you are a confident Excel user, you may wish to unlock a sheet and modify it. | |
| ■ | Please send any feedback or questions to [email protected] | |
| Important information | ||
| The figures shown in this Financial Forecast spreadsheet are intended as a guide only. It has been prepared without considering your objectives, financial situation or needs. Before acting on the calculations in this spreadsheet, you should consider its appropriateness to your circumstances. The Commonwealth Bank accepts no responsibility for any financial loss resulting from the use of this spreadsheet. We recommend you seek professional advice before acting on the information or calculations contained in it. |
Set-up costs
| Set-up costs | |
| Setting up the business | |
| Accountant's fees | $10,000 |
| Solicitor's fees | $5,000 |
| Business registration | $3,000 |
| Domain name registration | $1,000 |
| Insurance premiums | $1,200 |
| Licences | $10,000 |
| Workers compensation | $4,000 |
| Setting up the premises | |
| Lease deposit and advance rent | $20,000 |
| Fitout | $1,500 |
| Utility bonds and connection | $800 |
| Stationery and office supplies | $2,000 |
| Plant and equipment | |
| Equipment | $55,000 |
| Vehicles | $45,000 |
| Telecommunications | $2,000 |
| Computers and software | $3,500 |
| Starting operations | |
| Advertising and promotion | $10,000 |
| Raw materials and supplies | $16,000 |
| Working capital | $10,000 |
| Start-up capital | |
| Equity investment | |
| Borrowings | |
| Total | $0 |
| The result | |
| Total set-up costs | $200,000 |
| Surplus funds | 0 |
| Borrowings required | $200,000 |
Profit and loss forecast
| Profit and loss forecast | |||||||||||||
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Totals | |
| Sales | |||||||||||||
| Sales (invoiced) | $400,000.00 | $600,000.00 | $700,000.00 | $800,000.00 | $1,000,000.00 | $1,100,000.00 | $1,200,000.00 | $1,300,000.00 | $1,400,000.00 | $1,500,000.00 | $1,500,000.00 | $1,500,000.00 | $13,000,000.00 |
| Cost of goods sold | $120,000.00 | $200,000.00 | $240,000.00 | $300,000.00 | $400,000.00 | $430,000.00 | $460,000.00 | $520,000.00 | $600,000.00 | $720,000.00 | $720,000.00 | $720,000.00 | $5,430,000.00 |
| Gross profit | $280,000.00 | $400,000.00 | $460,000.00 | $500,000.00 | $600,000.00 | $670,000.00 | $740,000.00 | $780,000.00 | $800,000.00 | $780,000.00 | $780,000.00 | $780,000.00 | $7,570,000.00 |
| Expenses | |||||||||||||
| Accounting fees | $10,000.00 | $10,000.00 | $10,000.00 | $10,000.00 | $10,000.00 | $10,000.00 | $10,000.00 | $10,000.00 | $10,000.00 | $10,000.00 | $10,000.00 | $10,000.00 | $120,000.00 |
| Advertising | $10,000.00 | $10,000.00 | $10,000.00 | $12,000.00 | $14,000.00 | $16,000.00 | $19,000.00 | $21,000.00 | $22,000.00 | $25,000.00 | $25,000.00 | $25,000.00 | $209,000.00 |
| Bank charges | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $12,000.00 |
| Bank interest | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $18,000.00 |
| Depreciation | $2,190.00 | $2,190.00 | $2,190.00 | $2,190.00 | $2,190.00 | $2,190.00 | $2,190.00 | $2,190.00 | $2,190.00 | $2,190.00 | $2,190.00 | $2,190.00 | $26,280.00 |
| Electricity and gas | $1,200.00 | $1,200.00 | $1,200.00 | $1,500.00 | $1,600.00 | $1,800.00 | $2,000.00 | $2,400.00 | $2,600.00 | $3,000.00 | $3,000.00 | $30,000.00 | $51,500.00 |
| Equipment hire/lease | $800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $119,600.00 |
| Insurance | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $14,400.00 |
| Legal fees | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $1,200.00 | $14,400.00 |
| Motor vehicle expenses | $800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $10,800.00 | $119,600.00 |
| Postage, telephone and fax | $500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $17,000.00 |
| Stationery | $1,800.00 | $1,800.00 | $1,800.00 | $1,800.00 | $1,800.00 | $1,800.00 | $1,800.00 | $1,800.00 | $1,800.00 | $1,800.00 | $1,800.00 | $1,800.00 | $21,600.00 |
| Rent | $3,500.00 | $3,500.00 | $3,500.00 | $3,500.00 | $3,500.00 | $3,500.00 | $3,500.00 | $3,500.00 | $3,500.00 | $3,500.00 | $3,500.00 | $3,500.00 | $42,000.00 |
| Repairs and maintenance | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $12,000.00 |
| Security | $2,500.00 | $2,500.00 | $2,500.00 | $2,500.00 | $2,500.00 | $2,500.00 | $2,500.00 | $2,500.00 | $2,500.00 | $2,500.00 | $2,500.00 | $2,500.00 | $30,000.00 |
| Sundries | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $1,500.00 | $18,000.00 |
| Superannuation | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $12,000.00 |
| Transport/courier costs | $5,000.00 | $5,000.00 | $5,000.00 | $5,000.00 | $5,000.00 | $5,000.00 | $5,000.00 | $5,000.00 | $5,000.00 | $5,000.00 | $5,000.00 | $5,000.00 | $60,000.00 |
| Wages | $10,000.00 | $10,000.00 | $120,000.00 | $15,000.00 | $20,000.00 | $24,000.00 | $28,000.00 | $30,000.00 | $32,000.00 | $45,000.00 | $45,000.00 | $45,000.00 | $424,000.00 |
| Workers compensation | $4,000.00 | $4,000.00 | $10,000.00 | $14,000.00 | $16,000.00 | $20,000.00 | $24,000.00 | $26,000.00 | $28,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $266,000.00 |
| $0.00 | |||||||||||||
| $0.00 | |||||||||||||
| $0.00 | |||||||||||||
| $0.00 | |||||||||||||
| $0.00 | |||||||||||||
| $0.00 | |||||||||||||
| $0.00 | |||||||||||||
| $0.00 | |||||||||||||
| $0.00 | |||||||||||||
| Total | $60,690.00 | $81,690.00 | $197,690.00 | $98,990.00 | $108,090.00 | $118,290.00 | $129,490.00 | $135,890.00 | $141,090.00 | $169,490.00 | $169,490.00 | $196,490.00 | $1,607,380.00 |
| Result | |||||||||||||
| Net profit | $219,310.00 | $318,310.00 | $262,310.00 | $401,010.00 | $491,910.00 | $551,710.00 | $610,510.00 | $644,110.00 | $658,910.00 | $610,510.00 | $610,510.00 | $583,510.00 | $5,962,620.00 |
| Gross profit margin | 70% | 67% | 66% | 63% | 60% | 61% | 62% | 60% | 57% | 52% | 52% | 52% | 58% |
| Net profit margin | 55% | 53% | 37% | 50% | 49% | 50% | 51% | 50% | 47% | 41% | 41% | 39% | 46% |
Cash flow forecast
| Cash flow forecast | |||||||||||||
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Totals | |
| Starting cash position | $10,000.00 | $20,250.00 | $168,500.00 | $464,750.00 | $806,000.00 | $1,127,250.00 | $1,473,500.00 | $1,814,750.00 | $2,196,000.00 | $2,435,250.00 | $2,724,500.00 | $3,028,750.00 | |
| Incoming | |||||||||||||
| Cash sales | $200,000.00 | $300,000.00 | $350,000.00 | $400,000.00 | $500,000.00 | $550,000.00 | $600,000.00 | $650,000.00 | $700,000.00 | $750,000.00 | $750,000.00 | $750,000.00 | $6,500,000.00 |
| Collections from accounts receivable | $0.00 | $100,000.00 | $250,000.00 | $325,000.00 | $375,000.00 | $450,000.00 | $525,000.00 | $575,000.00 | $625,000.00 | $725,000.00 | $750,000.00 | $750,000.00 | $5,450,000.00 |
| Other cash receipts | $0.00 | ||||||||||||
| Total | $200,000.00 | $400,000.00 | $600,000.00 | $725,000.00 | $875,000.00 | $1,000,000.00 | $1,125,000.00 | $1,225,000.00 | $1,325,000.00 | $1,475,000.00 | $1,500,000.00 | $1,500,000.00 | $11,950,000.00 |
| Outgoing | |||||||||||||
| Fixed costs | $121,750.00 | $121,750.00 | $121,750.00 | $121,750.00 | $121,750.00 | $121,750.00 | $121,750.00 | $121,750.00 | $121,750.00 | $121,750.00 | $121,750.00 | $121,750.00 | $1,461,000.00 |
| Administration | $20,000.00 | $20,000.00 | $20,000.00 | $20,000.00 | $20,000.00 | $20,000.00 | $20,000.00 | $20,000.00 | $20,000.00 | $20,000.00 | $20,000.00 | $20,000.00 | |
| Marketing | $75,750.00 | $75,750.00 | $75,750.00 | $75,750.00 | $75,750.00 | $75,750.00 | $75,750.00 | $75,750.00 | $75,750.00 | $75,750.00 | $75,750.00 | $75,750.00 | |
| Operations | $26,000.00 | $26,000.00 | $26,000.00 | $26,000.00 | $26,000.00 | $26,000.00 | $26,000.00 | $26,000.00 | $26,000.00 | $26,000.00 | $26,000.00 | $26,000.00 | |
| Variable costs | $68,000.00 | $130,000.00 | $182,000.00 | $262,000.00 | $432,000.00 | $532,000.00 | $662,000.00 | $722,000.00 | $964,000.00 | $1,064,000.00 | $1,074,000.00 | $1,084,000.00 | $7,176,000.00 |
| Administration | $20,000.00 | $42,000.00 | $84,000.00 | $120,000.00 | $250,000.00 | $300,000.00 | $410,000.00 | $450,000.00 | $650,000.00 | $700,000.00 | $700,000.00 | $700,000.00 | |
| Marketing | $10,000.00 | $40,000.00 | $40,000.00 | $64,000.00 | $74,000.00 | $84,000.00 | $94,000.00 | $104,000.00 | $114,000.00 | $124,000.00 | $134,000.00 | $144,000.00 | |
| Operations | $38,000.00 | $48,000.00 | $58,000.00 | $78,000.00 | $108,000.00 | $148,000.00 | $158,000.00 | $168,000.00 | $200,000.00 | $240,000.00 | $240,000.00 | $240,000.00 | |
| Total | $189,750.00 | $251,750.00 | $303,750.00 | $383,750.00 | $553,750.00 | $653,750.00 | $783,750.00 | $843,750.00 | $1,085,750.00 | $1,185,750.00 | $1,195,750.00 | $1,205,750.00 | $8,637,000.00 |
| Result | |||||||||||||
| Change during month | $10,250.00 | $148,250.00 | $296,250.00 | $341,250.00 | $321,250.00 | $346,250.00 | $341,250.00 | $381,250.00 | $239,250.00 | $289,250.00 | $304,250.00 | $294,250.00 | |
| Closing cash position | $20,250.00 | $168,500.00 | $464,750.00 | $806,000.00 | $1,127,250.00 | $1,473,500.00 | $1,814,750.00 | $2,196,000.00 | $2,435,250.00 | $2,724,500.00 | $3,028,750.00 | $3,323,000.00 |
Balance sheet forecast
| Balance sheet forecast | |||
| As at | December 2009 | ||
| Assets | |||
| Current assets | $4,901,620 | ||
| Cash | $3,323,000 | ||
| Petty cash | $10,000 | ||
| Accounts receivable | $750,000 | ||
| Stock | $96,620 | ||
| Short-term investment | $100,000 | ||
| Prepaid expenses | $2,000 | ||
| Long-term investment | $620,000 | ||
| Fixed assets | $2,370,000 | ||
| Land | $850,000 | ||
| Buildings | $500,000 | ||
| Improvements | $800,000 | ||
| Equipment | $55,000 | ||
| Furniture | $120,000 | ||
| Motor/vehicles | $45,000 | ||
| Total assets | $7,271,620 | ||
| Liabilities | |||
| Current liabilities | $1,309,000 | ||
| Accounts payable | $400,000 | ||
| Interest payable | $20,000 | ||
| Taxes payable | $55,000 | ||
| Income tax | $178,000 | ||
| Sales tax | $390,000 | ||
| Payroll accrual | $266,000 | ||
| Long-term liabilities | $0 | ||
| Borrowings | |||
| Total liabilities | $1,309,000 | ||
| Net assets | $5,962,620 | ||
| Owner's equity | |||
| Retained earnings | $0 | ||
| Current year earnings | $5,962,620 | ||
| Total equity (should equal net assets) | $5,962,620 |
Break-even analysis
| Break-even analysis | |
| Average selling price per unit | $400 |
| Average cost of each unit | $150 |
| Gross profit margin | 63% |
| Fixed costs | $1,607,380 |
| Dollar sales to break even | $2,571,808 |
| Number of unit sales to break even | 6,430 |
