How to create a business plan using Excel
Calculating
the payback of roasted sunflower seeds shop.
the payback of roasted sunflower seeds shop.
What should we do?
1. To buy equipment (investment outlay).
2. To rent the premises (office and warehouse).
3. To hire the staff.
4. To buy the raw materials and the necessary.
5. To produce the products.
6. To sell the products.
Now let’s have a closer look. The stated below is the detailed statement how to use Excel for the calculations, so if you are not going to use this software for the
purpose at your enterprise, do not waste your time. It is better for your understanding to download a file (which, if you ask, I will send you via e-mail as I have not found out how to add it to the post. The size of the file is not big, but it will help you to sort the information out. What is more, you can you this file as a form for your own calculations (if
this form is not suitable for you and you need the other one, visit this page).The file contains: Investments, Personnel, Production, Purchase, Warehouse, Sale, Profit and Loss Account, Cash Flow Statement, Balance Sheet.
purpose at your enterprise, do not waste your time. It is better for your understanding to download a file (which, if you ask, I will send you via e-mail as I have not found out how to add it to the post. The size of the file is not big, but it will help you to sort the information out. What is more, you can you this file as a form for your own calculations (if
this form is not suitable for you and you need the other one, visit this page).The file contains: Investments, Personnel, Production, Purchase, Warehouse, Sale, Profit and Loss Account, Cash Flow Statement, Balance Sheet.
1.Purchase of Equipment (doc “Investments”).
Table #1
Thus, investment costs equal to 190,000 hryvnia and they are to be paid at once. When you carry out the payments, it is to be stated in cash flow statement. The presence of the equipment is to be stated in balance sheet.
2. Rent of the Premises (doc “Profit and Loss Account”)
To start the process we need three areas such as Office, Warehouse and Production department.
Table #2
The doc “Profit and Loss Account” contains profit and loss account. Rental costs are included into operational costs of the enterprise.
3. Personnel Recruitment (doc “Personnel”)
As you enterprise is growing, you will need different quantity of the staff, which is firstly connected with the production process. If you plan to launch 24 hour production, you will need to form 4 shifts for the workers before. From the 1st month you hire 3 workers, from the 4th – nine workers more. The necessary quantity of the personnel can be determined by the necessary output and equipment.
Remuneration
of labour.
of labour.
A chief accountant, office manager and cleaner have a fixed pay rate. Sales managers are paid 1,000 hryvnia (fixed pay rate) plus 2% from sales volume.
Workers are paid 1,000 hryvnia (fixed pay rate) plus 0,01 hryvnia per each packet made by the packer and 0,02 hryvnia per each packet produced by the roaster.
Workers are paid 1,000 hryvnia (fixed pay rate) plus 0,01 hryvnia per each packet made by the packer and 0,02 hryvnia per each packet produced by the roaster.
Table #3
Note! To calculate the piece-rate you need the sales figures (see Profit and Loss Account) as well as production output data (see Production).
4.Purchase of the Raw materials and the
necessary (doc “Purchase“)
necessary (doc “Purchase“)
To continue you will need to buy:
· Calibrated sunflower seeds;
· Polypropylene film for the packets;
· Plastic packs for packaging;
· Pallets for transportation.
Firstly, we have to define the price on each item to be bought:
Table #4
If necessary, prices can be corrected each month. So you need to define the funds necessary for purchase:
Table #5
To define the costs of sunflower seeds and polypropylene film you should use the data from the doc “Production”, where the necessary amount of materials is calculated and then it is multiplied to find out the price (see table #4). To determine the amount of packages and pallets you should use the information about the quantity stated in the doc “Warehouse” and then also to multiply by the price. It should be noted that it is impossible to describe everything step by step, moving from one document to another, to explain the origin of the data in each document, all the documents are bonded with the formulas. So I will return to the previously shown tables.
Now let’s calculate the cash flow.
Please, note that the table #5 does not contain the calculation of cash flow, but the amount of materials in money equivalent which is calculated on the basis of the
prices from the table #4. To calculate the cash flow we stipulate that we purchase the raw materials with a 50% prepayment. In other words, in the first month we settle the actual account and pay in advance for the next month:
prices from the table #4. To calculate the cash flow we stipulate that we purchase the raw materials with a 50% prepayment. In other words, in the first month we settle the actual account and pay in advance for the next month:
Table #6
Starting from the second month, the cash flow is calculated on the basis of the following:
1. Prepayment: 50% from the total amount of the necessary funds for the next month (total, table #5).
2. Actual payment: the difference in total (table #5) for the current month and the prepayment for the next one. Table #7 defines the accounts receivable at the end of the month.
Table #7
This table figures are posted into Balance sheet (table #28).
5.Production
(doc «Production»)
(doc «Production»)
The first table in the doc “Production” (here see table #8) defines the general characteristics of the production process:
1. The production capacity of the equipment.
2. The duration of a shift;
3. The number of shifts per day.
4. The number of working days (the production is customized, so at the beginning of the project the production department will not work every day). These figures if multiplied will show the production output (in kg). Next:
5. To determine the quantity of sunflower seeds in each packet (surely, in the process of production the figures can vary, but to simplify the planning we will take only
one variant of the packet weight sold).
one variant of the packet weight sold).
6. To calculate the number of the packets required (by means of dividing the number of output in kilograms by a packet weight).
7. To calculate the quantity of polypropylene film required in kilograms. This can be defined by means of diving the number of the packets required by 600 (a number
of 100 g packets in a film kilogram).
of 100 g packets in a film kilogram).
Table #8
The figures from table #8 are used in the calculation of a unit cost and movement of inventory.
Then we need to define the cost of electricity (see table # 9)
Table #9
Electricity consumption kilowatt-hour is determined by multiplying
the following figures:
· equipment
total capacity according to the specification (in our case, 20 kWh) is to be
multiplied by the figures from table #8;
· The duration of a shift,
· The number of shifts,
· The number of working days.
total capacity according to the specification (in our case, 20 kWh) is to be
multiplied by the figures from table #8;
· The duration of a shift,
· The number of shifts,
· The number of working days.
The cost of electricity in money equivalent is determined by multiplying the quantity of the electricity used by the cost of 1 kWh (in our case, this is 0,77 hrn).
Let’s determine the cost of production
Note!
To simplify the calculation, the production cost providing the grounds for unit cost calculation does not include the workers’ wages. Production cost including the wages is stated in the form of reference.
To simplify the calculation, the production cost providing the grounds for unit cost calculation does not include the workers’ wages. Production cost including the wages is stated in the form of reference.
To determine the cost in table №10 we use the following methods:
1. Sunflower seeds. Cost of seeds in a packet is calculated using a formula:
2. Packaging. Cost of one packet is calculated using a formula:
3. Electricity. Cost of electricity is calculated using a formula:
4. Plastic pack. Cost of packaging per 1 packet is calculated using a formula:
The above stated formulas are to be transferred to the table “Cost of production”
Table #10
The cost of wages per 1 packet is calculated using a formula:
6.Movement
of inventory(doc “Warehouse”)
of inventory(doc “Warehouse”)
This document describes the movement of inventory through the warehouse. The balance of each inventory type is used in Balance sheet in money equivalent (table #28).
Movement of sunflower seeds
The table of sunflower seeds movement has 4 lines:
1. Balance at the beginning of the period always equals to the balance at the end of the previous month.
2. Inflow is calculated by the formula:
3. Outflow is calculated by the formula:
Note: movement of seeds is calculated in tons.
4. Balance at the end of the period is calculated by the formula:
The above stated is to be transferred to the table “Movement of seeds”
Table #11
Table #11
Now, let’s calculate the movement of seeds in money equivalent.
1. Balance at the beginning and end of the period is calculated similarly to table #11.
2. Inflow is calculated by formula:
3. Outflow is calculated by formula:
The above stated is to be transferred to the table “Movement of seeds in money equivalent”
Table #12
Movement
of polypropylene film
of polypropylene film
1. Balance at the beginning and end of the period is calculated similarly to table #11.
2. Inflow is calculated by formula:
3. Outflow equals to the line “Number of packets in kg” (table #8) The above stated is to be transferred to the table “Movement of polypropylene film”
Table #13
Let’s calculate the movement of polypropylene film in money equivalent:
1. Balance at the beginning and end of the period is calculated similarly to table #11.
2. Inflow is calculated by formula:
3. Outflow is calculated by formula:
The above stated is to be transferred to the table “Movement of polypropylene film in money equivalent”
Table #14
Movement
of ready-made products
of ready-made products
1. Balance at the beginning and end of the period is calculated similarly to table #11.
2. Inflow is calculated by formula:
3. Outflow equals to the sales volume in quantity (table #22). The figures are taken from the doc “Sale” The above stated is to be transferred to the table “Movement of ready-made products”
Table #15
Table #15
Let’s calculate the movement of ready-made products in money equivalent:
1. Balance at the beginning and end of the period is calculated similarly to table #11.
2. Inflow is calculated by formula:
3. Outflow is calculated by formula:
The above stated is to be transferred to the table “Movement of ready-made products in money equivalent”
Table #16
Movement
of plastic packaging
of plastic packaging
1. Balance at the beginning and end of the period is calculated similarly to table #11.
2. Inflow is calculated by formula:
3. Outflow is calculated by formula:
The above stated is to be transferred to the table “Movement of plastic packaging”
Table #17
Let’s calculate the movement of plastic packaging in money equivalent:
1. Balance at the beginning and end of the period is calculated similarly to table #11.
2. Inflow is calculated by formula:
3. Outflow is calculated by formula:
The above stated is to be transferred to the table “Movement of plastic packaging in money equivalent”
Table #18
Movement of pallets
1. Balance at the beginning and end of the period is calculated similarly to table #11.
2. Inflow is calculated by formula:
3. Outflow is calculated by formula:
The above stated is to be transferred to the table “Movement of pallets”
Table #19
Table #19
Let’s calculate the movement of pallets in money equivalent:
1. Balance at the beginning and end of the period is calculated similarly to table #11.
2. Inflow is calculated by formula:
3. Outflow is calculated by formula:
The above stated is to be transferred to the table “Movement of pallets in money equivalent”
Table #20
Table #20
7.Product sales (doc “Sales”)
Let’s define the selling price and transfer the figures to table #21.
Table #21
Let’s define the sales volume and transfer the figures to table #22.
Table #22
Table #22
While defining the sales volume it is necessary to remember that the warehouse balance can not be of negative value. You can even bind the sales volume and the production output (i.e., the produced goods are to be sold). Then you can fill the table #22 with the data from the line “Number of the packages required (pieces)”, table #8.
Calculation of the cash flow from customers.
While calculating we consider that the 75% of sales are carried out with 30-day deferment. In other words, 75% of current month cash flow will be received the next month.
Prepayment sales are not stipulated.
Prepayment sales are not stipulated.
Table #23
We start the calculations from the second line:
30-day deferment of payment is calculated by formula:
Line “Actual payment” is calculated by formula:
The line “TOTAL” can define the sum of the lines “Actual payment” and “30-day deferment of payment”.
Calculation
of the accounts receivable.
of the accounts receivable.
Accounts receivable for the current month equals to the line “30-day deferment of payment” for the next month (table #23)
Table #24
The figures from this line are used in Balance sheet (table #28)
Calculation of transportation costs
In this table we will calculate the transportation costs. Transportation costs are calculated by formula:
Table #25
The figures of transportation costs are posted into the Loss part of Profit and loss account (doc “Profit and loss account”).
Final part
The information about the prediction calculations is to be transferred to the standard report forms: Balance sheet, Profit and loss account, Cash flow statement.
8. Profit and loss account (doc “Profit and loss account”)
Table #26
The lines of the given report are formed in the following way:
1. Sales volume(goods, services) equals to the line “Sales volume TOTAL” (table #22 “Sales”).
2. Cost of the goods sold equals to the line “Outflow” (table #16 “Movement of ready-made products in money equivalent”).
3. Sales expenditures is the sum of the lines “Transportation” and “Cost of pallets”.
4. Transportation equals to the line “Calculation of transportation costs”, table #25.
5. Cost of pallets equals to the line “Outflow” (table #20 “Movement of pallets in money equivalent”).
6. Gross profit is calculated by formula:
7. Gross profit rate is calculated as a correlation of gross profit and sales volume.
8. Wages equals to the line “TOTAL” (table #3).
9. Payroll tax is calculated here by formula:
10. All other expenses from “Operating expenses” are posted manually.
11. Operating expenses TOTAL equals to the sum of the above stated expenses.
12. Operating income is calculated by formula:
13. VAT is calculated by formula:
Note: This type of VAT calculation is stipulated by the fact that in Ukraine this tax is considered to be absolutely standardized charges. By the way, Ukraine’s tax authorities use the concept of “tax burden to the turnover”, so the amount of the tax which is to be paid to the country’s budget is calculated.
14. Income tax is calculated similarly to VAT.
15. Net profit is calculated by formula:
16. Net profit rate is calculated as a correlation of net profit and sales volume.
9. Cash flow statement (doc « Cash flow statement»)
Table #27
The section of cash flow from the operating activities is formed automatically on the basis of the tables formed earlier:
1. Cash receipts from customers equals to the line “TOTAL” (table #23 “Cash inflow”).
2. Income total is the sum of the section (I understand that in this case there is one line, but it is a particular case and there is no need to change the form).
3. Cash paid to suppliers and contractors equals to the line “TOTAL” (table #6 “Cash paid to supplier”).
4. Cost of electricity equals to the line “Cost of electricity, hrn” (table #9 “Cost of electricity”).
5. Transportation costs equal to the table #25 “Calculation of transportation costs”.
6. Direct costs total is the sum of the section.
7. Cash paid for operating activities is calculated by formula:
8. Taxes is the sum of the lines “VAT”, “Income tax”, “Payroll tax” (table #26) for the previous month.
9. Operating cash payments total is the sum of the section.
10. Net cash flow from operating activities is calculated by formula:
The next two sections are to be filled manually:
11. Purchase of fixed assets, in the 1st month this line equals to the line “TOTAL”, table #1 “Purchase of equipment”, then it is filled if the purchase of fixed assets is planned.
12. Sale of fixed assets is filled if you are going to sell the fixed assets.
13. Net cash flow from investing activities is calculated by formula:
14. Investments, this line includes the funds required to eliminate cash shortages. See paragraph 18, line “Cash balance at the end of the period”.
15. Withdrawals this line includes the funds which are planned to be taken out the business. See paragraph 18, line “Cash balance at the end of the period”
16. Net cash flow from financing activities is calculated by formula:
17. Cash balance at the beginning of the period equals to the line “Cash balance at the end of the period” of the previous month.
18. Cash balance at the end of the period is calculated by formula:
It is the line with the help of which you can determine the money deficit or surplus. So, in the process of planning this line serves as an indicator for additional
investments required (paragraph 14) or withdrawals possible (paragraph 15).
investments required (paragraph 14) or withdrawals possible (paragraph 15).
10. Balance sheet (doc “Balance sheet”)
Table #28
This form is automatically filled, so you do not need to post any data manually.
1. Fixed assets are calculated by formula:
2. Warehouse is the sum of the lines “Sunflower seeds”, “Polypropylene film”, “Plastic packs”, “Pallets”, “Goods”.
3. Sunflower seeds equal to the line “End of the period”, table #12 “Movement of seeds in money equivalent”.
4. Polypropylene film equals to the line “End of the period”, table #14 “Movement of polypropylene film in money equivalent”.
5. Plastic packs equals to the line “End of the period”, table #18 “Movement of plastic packaging in money equivalent”.
6. Pallets equal to the line “End of the period”, table #20 “Movement of pallets in money equivalent”.
7. Goods equal to the line “End of the period”, table #16 “Movement of ready-made products in money equivalent”.
8. Accounts receivable equal to the sum of the lines “Accounts receivable (buyers)” and “Accounts receivable (suppliers)”.
9. Accounts receivable (buyers) equal to the line “Accounts receivable”, table #24 “Calculation of accounts receivable”.
10. Accounts receivable (suppliers) equal to the line “Accounts receivable”, table #7 “Calculation of accounts receivable at the end of the month”.
11. Cash assets equal to the line “Cash balance at the end of the period”, table #27.
12. Total assets represent the sum total of all the assets of the enterprise. This line calculated by formula:
13. Investments (at the beginning of the year). In this case the line is not filled as the funds are withdrawn according to the agreement of investors. This line can be filled at the end of the 1st year of the project or if the owners decide to reinvest.
14. Additional investments are calculated by formula:
15. Income statement (from the beginning of the year) is calculated by formula:
16. including accounting period equals to the line “Net profit” (table #26, “Profit and loss account”). Balance sheet does not include this line.
17. Total owner’s equity is the sum of the lines “Investments (at the beginning of the year)”, “Additional investments”, “Income statement (from the beginning of the year)”.
18. Accounts payable. In our case we have no accounts payable.
19. Wages pay equals to the line “Wages” (table #26, “Profit and loss account”).
20. Taxes payable is calculated by formula:
21. Total current liabilities represent the sum total of the lines “Accounts payable”, “Wages pay”, “Taxes payable”
22. Total liabilities represent the sum total of all the liabilities of the enterprise. It is calculated by formula:
23. Verification is the checking line. If there is a figure, it means that there is a mistake in calculations. It is calculated by formula:
Leave a Reply