Section 6. Question 14
You can use this template:
Submit all your financial data, which includes:
Start by providing us with your venture’s historical data.
Click on the “Last 12 months” tab.
When you see instructions in italics (like this) complete the relevant task using the template provided. Sometimes you will also need to use a calculator and a notebook or the notes app on your computer.
Tabs are found at the bottom of the spreadsheet.
Collect your historical data by using your records to determine the income you have received and the costs you have incurred.
Use your receipts and bank statements for an accurate record of your income and expenditures for each of the last 12 months.
List each expenditure separately and break down your income into different sources (such as grants and donations) as well as the different types of products and services you sell.
Create more spreadsheet rows if you need them for all the different types of income sources or expenditures. Right-click on the last row, move your cursor over “Insert” on the pop-up menu, and click on “Sheet Rows”.
The amount in the field “Total Income - Expenditure” for the most recent month should match the current figure in your bank account.
If these figures do not match, you will need to check your income and expenditure figures to find the error. You may have forgotten to include an item.
After completing your historical data, you are ready to make a forecast of your venture’s financial future.
A financial forecast is a plan that sets out two things:
Start your forecast by listing assumptions about the future performance of your venture in the tab labelled “Assumptions”.
Use your business plan to generate a list of expected income and expenditures in the provided tables.
Assumptions about your income and expenditures should be detailed and realistic. Tell us what your assumptions are based on (for example, the previous performance of your venture, market research, or data insights).
Describe the income source and the amount you expect it to generate. Income may come from sales of products and services, as well as grants and donations.
Also called “costs”. Describe the type and amount of expenditures you expect your venture to involve. Expenditures may include rent, salaries, equipment, material, and legal costs.
Now that you have a list of assumptions about your venture’s income sources and costs, you can use theme to produce a financial forecast.
Use the tab labelled “Next 12 months”.
Start your forecast by using a sales budget to calculate the income you will generate from sales of products and services.
A sales budget lists every type of product or service you offer.
It then calculates how much income you can expect by multiplying the number of items of each type by the price you will charge for each item.
Sales budget formula: Number of items you expect to sell X Price you will charge for each item = Income you will generate from that item
For example, if your venture offers group training sessions for £120, and you expect to sell 6 group training sessions in Month 1, then your income from all training sessions for that month will total £720.
Here is how to complete a sales budget.
Start with an item you will sell and write its name in the income section of the financial forecast.
Next, use a piece of paper or a notes app to write down the price you will charge for each item and the number of items you expect to sell next month.
After that, use a calculator to multiply the price per unit by the number of units you expect to sell.
Record your total for each item type in the appropriate field.
If you expect to sell the same number of units at the same price in the month after next, just copy the same number into the next field to the right. Otherwise, perform the sales calculation again using the new numbers for price per unit and units sold for that month.
Use the income part of your spreadsheet to record income from grants as well.
Next, calculate the costs your venture will involve.
There are a few different types of costs to consider:
Variable costs are costs that increase with the amount of sales. They include things like materials or ingredients.
For example, if you run a coffee shop that sells sandwiches, the cost of your bread, cheese, and ham will vary depending on how many ham and cheese sandwiches you sell.
You can work these costs out by identifying the cost per unit of each ingredient and multiplying that by the number of units you expect to sell. This will indicate the total cost for that ingredient.
If the same variable cost is used in multiple items, then you will need to perform the above calculation for each item type and then add them together.
For example, if the cost of cheese per sandwich is 20p but cheese is also used for jacket potatoes at a cost of 40p per unit, then you can work out your total cost for cheese through the following three-stage process.
First, multiply the cost of cheese per sandwich by the number of sandwiches you expect to sell. Second, multiply the cost of cheese per jacket potato by the number of jacket potatoes you expect to sell. Third, add the product of both multiplications together.
Fixed costs are costs that remain the same no matter how much your venture sells. They include things like rent and insurance.
For example, if you run a coffee shop, rent and insurance will probably cost you the same amount whether you sell 50 sandwiches or 500.
You do not need a formula to calculate fixed costs.
Simply record the cost in the column of the month you expect to incur it.
Many fixed costs, such as rent, will not vary from month to month.
You can copy and paste the amount of a fixed cost from one month to the next, as long as you do not expect it to change.
One-time costs are costs that do not occur regularly. They include things like computers, machinery, or tools.
Record each one-time cost in the appropriate field for the month during which you expect to incur it.
Check that you have: