Spreadsheets needed for a
business plan (available soon). These spreadsheets -
profit and loss, cash flow and balance sheet - can be
developed to reflect your actual business forecasts and then up-dated as actual
statements of business performance. The data needed for the forecasts should,
by now, emerge from your deep thinking and research. The estimates need to be as
realistic as possible.
Try to keep the worst case scenario in mind. Then, by changing the actual
data (the values of the variables in a spreadsheet model) you can explore the
outcomes of a number of "what if" scenarios.
Complete the spreadsheet with estimated sales by month. Allow for
seasonality and a build up of sales (related to marketing promotions) from
start-up.
On the basis of your operating plan (volume of production/service, purchasing
plan etc) , having identified your direct variable costs - complete your
budgeted direct costs.
calculate gross profit (sales-direct costs)
now divide gross profit by sales to give the % gross profit margin
e.g. £25,000 profit divided by £125,000 gives a 20% margin.
Overheads (fixed and semi-variable costs) vary little in relation to
output or level of business activity in the short term. If the business expands
fixed overheads may grow (e.g. bigger premises, another telephone). Estimate the
annual cost of each overhead item and calculate the monthly aaverage spend.
Identify items which will be paid quarterly or as one payment. e clear about when
these have to be paid as they will affect cash flow considerably. The profit and
loss statement merely reflects a budget for them.
monthly gross profit less total monthly overrheads gives the trading
profit or loss. In the initial trading period - the losses wil be apparent
and frightening unless your forecasts are sound and you know that you are on
course for breaking even by the planned date.
include depreciation (of plant and equipment) in your profit and loss
forecast. You may seek to rent or hire rather than buy capital assets outright
include interest payable on any loans/overdraft.
calculate net profit before tax (trading profit less depreciation and
interest)
Add notes to your profit and loss forecast to explain the assumptions you have
made about the figures. Readers will want to know why a particular value is as it
is. Append information about supplier prices, advanced sales etc.
As you trade, cash flows in and out. The cash flow forecast predicts the effect
of the business operation on the bank balance. You and your investors need to
anticipate how and if your creditors can be paid, when you will be flushed with
cash or desperately short of it.
Will you be able to pay your bills when they are due? If not you will the bank
extend your overdraft, can you dip into reserves. If not then you are illiquid -
legally unable to trade. You may have to file for bankrupcy or place your company
into receivership. Cash flow is thus something which you cannot ignore.
The cash flow spreadsheet includes budgetted and actual columns so that as you
trade you can update the budgetted figures (as you get nearer the receipt/payment
dates your ability to predict becomes more accurate) and then enter the actual
figures to compare the variances.
The titles and data of your cash flow model will be the same (with a few minor
variations) as those appearing in the profit and loss statement. Once again the
figures must be supplemented by explanations of why. The forecast covers the
following
Receipts
Predict the peaks, troughs and seasonality of your sales. The sales figures
should be inclusive of VAT that you have charged. VAT is an important element in
cash flow - you have to pay the VAT collectors!
the cash sales figures (if most of your customers have paid in cash) will be
similar to the profit and loss data.
Note the "cash from debtors" row in the spreadsheet. Debt
management will be a key part of your cash flow operations. Where sales are on
credit terms build in a factor (say 12%) to cover sales which might become bad
debts. This % of course will vary. You cannot assume that all your debtors will
pay promptly. You will dispair of chasing bad debts. You will become hardened to
the problems of credit sales and bad risk customers. Some large firms are the
worst - their arrogance in delaying payments may bring you to your knees.
Note the "capital introduced" row. From time to time you may new
capital may be injected into the business e.g. from savings, asset sales,
additional bank loans or grants from various agencies.
Payments
Predict ALL expenses that your business will incur. Many of your suppliers
will offer credit times e.g. 30 days to pay. You have use of their goods and
services free until the due payment date. But then you must pay. If you sign a
check will the bank bounce it back?
The cash flow forecast keeps an eye on payments by month. You will also eed to
monitor individual outgoing payments by due date - if you canot cover a
payment....bankrupcy or receivership may loom large.
Will receipt of a payment from a customer cover outgoing payments on a due
date? What if the inflow does not materialise (bad debt) - will the supplier
accept your excuses? Will your bank manager?
Wages
most items are as per the P & L statement.
include PAYE and National Insurance payments made for your employees.
As a sole trader or partner, the salary/wage you take from the business are
"drawings" against the business rather than wage/salary.
implement a monthly direct debt system for your payments to minimise
unexpected quarterly bills.
Interest and VAT Payments
these rows cover monthly or quarterly interest on loans and your overdraft.
Remember the bank will deduct this straight from your business account.
Anticipate the payment in relation to all other outflowing payments to suppliers.
if your sales exceed £45,000 you need to be registered for VAT. There is
the VAT charged to customers and VAT paid to suppliers. If VAT charged to
customers is greated than VAT paid to suppliers then you owe the surplus to HM
Customers and Excise. If you pay more VAT to suppliers than you charge to
customers then you can claim VAT back from the VAT office. These monthly or
quarterly payments/receipts must feature in your cash flow forecast.
Net Cash Flow and Bank Balance
Subtract total monthly payments from total monthy receipts. Note the minus
figures and work out how you are going to cope with these!
add a positive net cash flow to the opening bank balance figure to give your
closing balance (vice versa for negative net cash flow).
Annotate your cah flow forecast/statement with explanatory notes covering your
assumptions, how you have aggregated bits together, debtor and creditor ratios
and any "specials".
Use your spreadsheet to do a sensitivity analysis. See what happens if your
sales are 12% less than you anticipate. What is the affect on cash if your bad
debts ratio is 8%.
The balance sheet forecasts your assets and liabilities. It shows the business's
financial position at a point in time - typically year end. It helps you to
comprehend where all the £ coming into the business has originated and
where it has gone.
In a business start-up, a quarterly analysis is useful to evaluate business
performance. You can then modify your approach to improve your trading position.
Balance sheet data is extracted mostly from the P & L and cash flow
statements. The balance sheet spreadsheet shows how your capital is distributed
across the various assets of the business and where it came from. Assets are
everything your business owns. Liabilities are monies- long-term and short-term -
your business owes others (including yourself!).
Fixed assets
- include land, buildings, machinery/equipment, fixtures and fittings, the van
etc that the business owns (not leased or hired). These items remain part of the
business for a long time. You must evaluate their current worth and allow for
appreciation of their value as well as any depreciation.
Ideally you should allocate reserve/spare cash to anticipate eventual replacement
costs of depreciating assets
Current assets
These assets are acquired and then processed into the items that you sell or
offer for as part of the service. Finished or part finished stocks, raw
materials, consumables, debtors and any cash are all current assets. A value has
to be calculated for non-cash items.
Current liabilities
Add up all temporary debts that must be paid within a short period (no more than
12 months). Include all debts to creditors/suppliers, the bank (overdraft) and
interest on loans. Add in outstanding tax and VAT obligations.
Working capital/Net current assets
Subtract all current liabilities from current assets. When net current assets
are positive (in the black) then you have working capital which can be used to
develop the business and its operations.
If liabilities are greater than assets then you have current net liabilities -
you have no working capital! Can you obtain further covering funds to keep you
going? If not then, you are "trading whilst knowingly insolvent".
Declare yourself bankrupt or call in the receivers!
Financed by
Where the capital that finances your assets originates e.g. investment funds you
have put in from your savings, long term bank or other loans. Include the profit
or loss(from the P & L account) for the period that the balance sheet
relates to.
The total for net assets must be equal to the new capital that has come into the
business. If not, check your figures and re-calculate.
Add supplementary notes to clarify any points. At this stage you have based your
balance sheet on your forecasts for P & L and cash flow. Of course this is a
model. As you start to trade monitor your actuals against your estimates.
Spreadsheets needed for a
business plan (available soon). These spreadsheets -
profit and loss, cash flow and balance sheet - can be
developed to reflect your actual business forecasts and then up-dated as actual
statements of business performance. The data needed for the forecasts should,
by now, emerge from your deep thinking and research. The estimates need to be as
realistic as possible.