How to create a business plan using ExcelАртем
the payback of roasted sunflower seeds shop.
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.
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.
The doc “Profit and Loss Account” contains profit and loss account. Rental costs are included into operational costs of the enterprise.
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.
necessary (doc “Purchase“)
If necessary, prices can be corrected each month. So you need to define the funds necessary for purchase:
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.
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:
Starting from the second month, the cash flow is calculated on the basis of the following:
This table figures are posted into Balance sheet (table #28).
one variant of the packet weight sold).
of 100 g packets in a film kilogram).
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)
Electricity consumption kilowatt-hour is determined by multiplying
the following figures:
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.
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.
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”
The cost of wages per 1 packet is calculated using a formula:
of inventory(doc “Warehouse”)
Now, let’s calculate the movement of seeds in money equivalent.
The above stated is to be transferred to the table “Movement of seeds in money equivalent”
of polypropylene film
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”
Let’s calculate the movement of polypropylene film in money equivalent:
of ready-made products
The above stated is to be transferred to the table “Movement of ready-made products in money equivalent”
The above stated is to be transferred to the table “Movement of plastic packaging”
Let’s calculate the movement of plastic packaging in money equivalent:
Prepayment sales are not stipulated.
We start the calculations from the second line:
of the accounts receivable.
The figures from this line are used in Balance sheet (table #28)
The lines of the given report are formed in the following way:
7. Gross profit rate is calculated as a correlation of gross profit and sales volume.
16. Net profit rate is calculated as a correlation of net profit and sales volume.
The section of cash flow from the operating activities is formed automatically on the basis of the tables formed earlier:
8. Taxes is the sum of the lines “VAT”, “Income tax”, “Payroll tax” (table #26) for the previous month.
14. Investments, this line includes the funds required to eliminate cash shortages. See paragraph 18, line “Cash balance at the end of the period”.
investments required (paragraph 14) or withdrawals possible (paragraph 15).
This form is automatically filled, so you do not need to post any data manually.
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.
21. Total current liabilities represent the sum total of the lines “Accounts payable”, “Wages pay”, “Taxes payable”
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: