## How to create a business plan using Excel

**Calculating**

the payback of roasted sunflower seeds shop.

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.

**1.**

**Purchase of Equipment (doc “Investments”).**

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”)**

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”)**

^{st}month you hire 3 workers, from the 4

^{th}– nine workers more. The necessary quantity of the personnel can be determined by the necessary output and equipment.

**Remuneration**

of labour.

of labour.

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.

**4.**

**Purchase of the Raw materials and the**

necessary (doc “Purchase“)

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.

__Now let’s calculate the cash flow.__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).

**5.**

**Production**

(doc «Production»)

(doc «Production»)

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.

**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**

**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.**

__Note!__

**Sunflower seeds.**Cost of seeds in a packet is calculated using a formula:

**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”

**The cost of wages**per 1 packet is calculated using a formula:

**6.**

**Movement**

of inventory

of inventory

**(doc “**

**Warehouse”)**

**Movement of sunflower seeds**

**Balance at the beginning of the period**always equals to the balance at the end of the previous month.

**Inflow**is calculated by the formula:

**Balance at the end of the period**is calculated by the formula:

Table #11

Now, let’s calculate the movement of seeds in money equivalent.

**Balance at the beginning and end of the period**is calculated similarly to table #11.

**Inflow**is calculated by formula:

**Outflow**is calculated by formula:

The above stated is to be transferred to the table “Movement of seeds in money equivalent”

**Movement**

of

of

**polypropylene film**

**Balance at the beginning and end of the period**is calculated similarly to table #11.

**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”

Let’s calculate the movement of polypropylene film in money equivalent:

**Balance at the beginning and end of the period**is calculated similarly to table #11.

**Inflow**is calculated by formula:

**Outflow**is calculated by formula:

**Movement**

of ready-made products

of ready-made products

**Balance at the beginning and end of the period**is calculated similarly to table #11.

**Inflow**is calculated by formula:

**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

**Balance at the beginning and end of the period**is calculated similarly to table #11.

**Inflow**is calculated by formula:

**Outflow**is calculated by formula:

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:

**Balance at the beginning and end of the period**is calculated similarly to table #11.

**Inflow**is calculated by formula:

**Outflow**is calculated by formula:

**Movement of pallets**

**Balance at the beginning and end of the period**is calculated similarly to table #11.

**Inflow**is calculated by formula:

**Outflow**is calculated by formula:

Table #19

**Balance at the beginning and end of the period**is calculated similarly to table #11.

**Inflow**is calculated by formula:

**Outflow**is calculated by formula:

Table #20

**7.**

**Product sales (doc “Sales”)**

**Let’s define the selling price**and transfer the figures to table #21.

**Let’s define the sales volume**and transfer the figures to table #22.

Table #22

**Calculation of the cash flow from customers.**

Prepayment sales are not stipulated.

We start the calculations from the second line:

**30-day deferment of payment**is calculated by formula:

**“Actual payment”**is calculated by formula:

**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.

The figures from this line are used in Balance sheet (table #28)

**Calculation of transportation costs**

**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”)**

The lines of the given report are formed in the following way:

**Sales volume**

**(goods, services)**equals to the line “Sales volume TOTAL” (table #22 “Sales”).

**Cost of the goods sold**equals to the line “Outflow” (table #16 “Movement of ready-made products in money equivalent”).

**Sales expenditures**is the sum of the lines “Transportation” and “Cost of pallets”.

**Transportation**equals to the line “Calculation of transportation costs”, table #25.

**Cost of pallets**equals to the line “Outflow” (table #20 “Movement of pallets in money equivalent”).

**Gross profit**is calculated by formula:

7.

**Gross profit rate**is calculated as a correlation of gross profit and sales volume.

**Wages**equals to the line “TOTAL” (table #3).

**Payroll tax**is calculated here by formula:

**All other expenses from “Operating expenses”**are posted manually.

**Operating expenses TOTAL**equals to the sum of the above stated expenses.

**Operating income**is calculated by formula:

**VAT**is calculated by formula:

**Income tax**is calculated similarly to VAT.

**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»)**

The section of cash flow from the operating activities is formed automatically on the basis of the tables formed earlier:

**Cash receipts from customers**equals to the line “TOTAL” (table #23 “Cash inflow”).

**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).

**Cash paid to suppliers and contractors**equals to the line “TOTAL” (table #6 “Cash paid to supplier”).

**Cost of electricity**equals to the line “Cost of electricity, hrn” (table #9 “Cost of electricity”).

**Transportation costs**equal to the table #25 “Calculation of transportation costs”.

**Direct costs total**is the sum of the section.

8.

**Taxes**is the sum of the lines “VAT”, “Income tax”, “Payroll tax” (table #26) for the previous month.

**Operating cash payments total**is the sum of the section.

**Net cash flow from operating activities**is calculated by formula:

**Purchase of fixed assets**, in the 1

^{st }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.

**Sale of fixed assets**is filled if you are going to sell the fixed assets.

**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”.

**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”

**Cash balance at the beginning of the period**equals to the line “Cash balance at the end of the period” of the previous month.

**Cash balance at the end of the period**is calculated by formula:

investments required (paragraph 14) or withdrawals possible (paragraph 15).

**10.**

**Balance sheet (doc “Balance sheet”)**

This form is automatically filled, so you do not need to post any data manually.

**Fixed assets**are calculated by formula:

**Warehouse**is the sum of the lines “Sunflower seeds”, “Polypropylene film”, “Plastic packs”, “Pallets”, “Goods”.

**Sunflower seeds**equal to the line “End of the period”, table #12 “Movement of seeds in money equivalent”.

**Polypropylene film**equals to the line “End of the period”, table #14 “Movement of polypropylene film in money equivalent”.

**Plastic packs**equals to the line “End of the period”, table #18 “Movement of plastic packaging in money equivalent”.

**Pallets**equal to the line “End of the period”, table #20 “Movement of pallets in money equivalent”.

**Goods**equal to the line “End of the period”, table #16 “Movement of ready-made products in money equivalent”.

**Accounts receivable**equal to the sum of the lines “Accounts receivable (buyers)” and “Accounts receivable (suppliers)”.

**Accounts receivable (buyers)**equal to the line “Accounts receivable”, table #24 “Calculation of accounts receivable”.

**Accounts receivable (suppliers)**equal to the line “Accounts receivable”, table #7 “Calculation of accounts receivable at the end of the month”.

**Cash assets**equal to the line “Cash balance at the end of the period”, table #27.

**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 1^{st }year of the project or if the owners decide to reinvest.

**Additional investments**are calculated by formula:

**including accounting period**equals to the line “Net profit” (table #26, “Profit and loss account”). Balance sheet does not include this line.

**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)”.

**Accounts payable**. In our case we have no accounts payable.

**Wages pay**equals to the line “Wages” (table #26, “Profit and loss account”).

21.

**Total current liabilities**represent the sum total of the lines “Accounts payable”, “Wages pay”, “Taxes payable”

**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