Financial Projections Templates

Your Office – Virtually 6 Washwood Close Little Hulton Manchester M38 9EQ T:0161-799-2738 M:0755 415-2279 E:[email protected] W:www.youroffice-virtually.com

Start at the INTRO worksheet where you need to fill in a few boxes. The information from these boxes is used in some formulae in the later pages.

Box 1: If you want to start your 12 month projections on 1st May 2009, then enter that date in the first box in the format shown. You can change it at any time without losing any data. Box 2: Enter Y if you are a Manufacturer. Leave blank or enter N if you are not manufacturing goods. Box 3: Enter Y if you are providing a Service. Leave blank or enter N if you are not. Box 4: Enter Y if you are registered for VAT. Leave Blank or enter N if you are not registered for VAT. You can use this box to see the effect that VAT will have on your Sales, Profit & Loss and Cash Flow Forecasts. Start off by entering all your data with the box Blank or with N. When you’ve finished, save the Workbook as something like ‘Projections Excluding VAT’. Then, enter Y in this box and save another copy as ‘Projections Including VAT’. The VAT is calculated as if it were included in the prices you are charging so the Revenue in your Sales figures will DECREASE by the amount of VAT. We are preparing a new version of the templates that will offer you the alternative of adding the VAT to the current Sales figures, but this will mean you need to increase your Prices! Box 5: Allows you to change the current VAT Rate which is set at 15%. It is due to go up again to 17.5% in January 2010 so be prepared! Ready? OK, if you are a Manufacturer, go the first Worksheet called ‘Manufacturing_Forecast’. If you are NOT a Manufacturer, ignore the ‘Manufacturing_Forecast’ worksheet, and go straight to the ‘Sales_Forecast’ worksheet.

Notice that all 12 months are populated at the top. This is based on the date you entered on the INTRO page. Notice also that there are some Divide By Zero messages in the Unit Production Cost row at the bottom. Don’t worry about these as they will disappear as you populate the data. The first Column is filled in as an example for you. In the UNITS row, you need to enter the QUANTITY of Items you expect to Manufacture (NOT Sell) in each Period.

 Note 2

You might be manufacturing more than one product and the costs will be different for each. There is no template for this at present so you will need to do some basic calculations and come up with an average number of Units Manufactured and Average Costs. Under Direct Manufacturing Costs, you need to enter the AMOUNT against each type of Direct Cost. A Direct Cost is something that you can definitely relate to the production of the Units above. Direct Costs are usually Variable Costs that change according to how many units you produce. You can use the ‘Other’ rows to enter your own specific costs. As you enter the Data, a Unit Production Cost is calculated at the bottom. This will be used in the Trading Forecast as the Cost of Goods Sold. Finished? OK, now go to the Sales_Forecast worksheet.

Once again, the months are filled in for you but you need to complete the ‘Quantity Sold’ and the Unit Price boxes. In the Quantity Sold box for each month you need to enter either the number of Items sold or the number of Hours/Days charged if you are a Service provider.

Note 2

If you are a Manufacturer, the Quantity Sold will not necessarily be the same as the Quantity Manufactured. Also, if you buy and re-sell products, you may not sell in any month everything you buy that month. You need to decide how many of what you make or buy each month will actually be Sold in the same month. The remainder will be classed as STOCK and will appear in your Balance Sheet. For service providers, you will be selling the hours you worked each month – hopefully! Now you must complete the Unit Price boxes. Once you have entered the Unit Prices, the boxes below will be filled in differently depending on what you have entered in the VAT registered box on the INTRO worksheet. Go back to the INTRO worksheet and change what you have entered to see the effect.

Note 3

If you are manufacturing or buying and re-selling a range of different products, you will need to do a Product Mix forecast. At the moment, there is no Template for this as it is so business specific. Try and estimate how many of each Product you will sell and at what price, then calculate average Units Sold and an Average Price based on those calculations. Now you have completed your Sales Forecast, we can move on. If you are a Service Provider or if you buy and re-sell goods, go straight to the Profit & Loss Forecast. If you Manufacture, go to the Trading Account Forecast worksheet. This concept of a Trading Account is quite old fashioned and is rarely used these days, however it is quite handy for seeing your Gross Profit (or Gross Margin or Contribution). You don’t need to enter anything in here – it’s all automatic.

It takes your forecast Sales values and quantities (the value will differ if you are registered for VAT) then calculates the production cost of the goods SOLD, not of the goods manufactured. In the example you will see those Divide by Zero errors but they will disappear once you have all your data entered. These errors only appear if you have said you are a Manufacturer.

You will be wondering what happens to the production costs for the items that have not been sold. Well, this value becomes your STOCK value in the Balance Sheet. Now, you can move on to the Profit & Loss Forecast worksheet.

Unfortunately, you have to do a bit more work here. An example column is filled in for you, but these are really YOUR numbers. The Turnover or Gross Profit Row will be populated and the Profit/Loss carried forward is automatically calculated for you. Don’t forget the Turnover or Gross Profit row will change depending on your VAT Registered choice. Because this is your Profit & Loss projection, you need to think about how much of each expense actually relates to the current period. For example, you might pay your Rent Quarterly in June, September, December and March. Most people do. However, this is likely to be a large sum and not all of the rent you pay in March relates to the sales you made in March. Normally, for budgeting purposes, you will work out what your rent is annually then divide it by 12 to get a Monthly charge. You should do this with any cost that is charged to you for any period greater than a month. For something like Direct Wages, you will enter what you actually paid for Gross Wages in the month as these will usually relate to what you sold (unless you pay monthly in arrears). Use the ‘Other’ rows to add your own expense categories. Don’t be too detailed at this stage. If an expense is the same each month, use one of the Excel copy across techniques rather than typing them in. The totals will all calculate for you.

Once the P&L forecast is done, you can move to the Cash Flow forecast.

The Cash Flow forecast is VERY IMPORTANT. Cash is your lifeblood. Run out and your business could die. The template is based on the Profit & Loss forecast, but there are additional items at the bottom of the Payments section for things like VAT, PAYE and Drawings. When you fill in this grid, you need to think about when the money will actually have to be paid, or when it will actually be received. So, Customer receipts will be different if you are a Retailer in a Cash business where what you sell puts money in the till; than if you provide a service where you invoice a Customer and then they take two months to pay you. How much credit will you give? What does that mean for your Cash Flow? Think carefully about this before filling in the grid. The same goes for payments. Raw Material Suppliers may insist on Cash on Delivery for a new business and the taxman doesn’t like to be kept waiting. Consider each receipt and payment carefully before filling it in. Take more time over this page than any other as it could save your business and it will show the bank that you understand the issues facing you. The forecast assumes that you start with No Money at all! Generally, that will not be the case, so you should enter the amount of money you are introducing into the business in Cell B4 which is also called Bank Balance/P1.

Once all the payments and receipts are entered, you will have a running cash position and you will be able to see where things are going to be tight. Knowing this in advance may allow you to negotiate with people. The final template is the Balance Sheet. This is under development but should be completed very soon. Please note that all values apart from Unit Cost and Price are rounded to the nearest £. We are developing a version that will allow foreign currencies to be used for exporters. If you find anything that doesn’t appear to be working, if there is anything you would like to be included in a later version or if you have any other comments, please let is know straight away and it will be actioned. The email address is below.

If you would like an updated version of the Financial Forecasts Workbook, email [email protected] or download from our website www.youroffice-virtually.com.