Business plan calculation for 15 types of productsАртем
We will overview in this article the calculation of the production of 15 types of products from 20 materials/ingredients/components. The calculation is made for the next three years with the result of three main forms:
There is a calculation in this file, which shows the profitability of the business for cutting and packing vegetables and fruits. Moreover, such calculations could be used according to any other type of production. It depends on your imagination and purpose.
The first sheet:
There is a simple table with cells highlighted in orange where you manually enter data. In this table you enter the initial costs that occur before the start of production, namely:
● unit price.
The table will calculate the total cost of each type of entered expense and at the end of the table, you will see the full cost of the planned expenses.
Sheet “Wage fund”
Personnel costs are planned on this worksheet.
Data is manually entered only in table cells highlighted in orange.
It means you indicate the position of the employee, his monthly wage, and the number of staff units. All other calculations are automatic. However, you may need to enter not all staff units from the first month. In this case, you need to remove the formula from the first column of the calculation (column D) and put the next formula in the month from which the particular staff unit appears at your enterprise:
Wage (Column B) * Number of units (Column C)
A similar operation is done in the positions table “Sales manager” and “2nd loader.” These staff units were introduced from the second month of the project. For all other months, the calculation will be made automatically.
The table is valid for the entire duration of the project (for 36 months). A part of the months is hidden in the table (you can see that by the thickening of the table cells divider line between columns I and AB). That was made for clarity, this hiding will not be in the file, but in all subsequent tables in this article, I will hide some of the columns by the same principle. I do that just for clarity, to remove some of the duplicate information.
Sheet “Prime cost”
That is the key sheet of this calculation. Here you manually enter data into several tables. In the following order:
Table “Purchase price for 1 kg, UAH”
Column “A” – the name of the materials and used components is entered here.
All other columns are the price per unit of raw materials, materials, and components used. The price of all fruits and vegetables is per kilogram, components priced per piece, per meter, per cubic meter. It depends on the material. You need to enter the price only in column B and the price data will be allocated to all months. You can change the price in any month and after that, as a result, the price in all other months will be changed automatically. Data can be entered manually into any table cell, therefore, the entire table is highlighted in orange.
Table “Wastage rate during processing purchased raw materials and components, %%”
In this table, column A is already filled in. The data comes into it from column A from the previous table and for the rest of the calculation, you will not need to enter the names of raw materials, materials, and components anywhere. Therefore, column A is not highlighted.
All next columns are highlighted in orange. You indicate how much waste you have had in the process of processing purchased raw materials and materials in these columns. Moreover, note that if half of the purchased raw materials fall in waste during processing, then you need to put not 50%, but 100% in this table (example in the table “Pineapple”). There is the ratio of waste here, which is taken not to the purchased raw materials, but to the already processed semi-finished products. It is necessary to fill in the table only for those types of raw materials for which there will be a waste. You can change the percentage of waste in different periods according to the same principle as the price in the table “Purchase price for 1 kg, UAH.”
Table “Product tray filling structure”
We do not touch column A in this table.
All other columns. Pay attention to the table heading in these columns, it is also highlighted. That is because in the table header you enter the name of the products you plan to produce. You also enter the name of the product once, and there is no need to do this anywhere else.
Directly in the field of the table, you enter the number of raw materials in the manufactured product. For example, in the product “Mix of 4 vegetables,” there will be:
● 0.125 kg tomato;
● 0.13 kg of cucumbers;
● 0.134 kg of bell pepper;
● 0.151 kg of mushrooms.
Moreover, materials in the following quantity will go to 1 piece of this product:
● 1/12 of the trays box (or 0.08 decimal). Since the box fits 12 pieces of any type of product;
● 1 tray;
● 0.5 meter of packaging film;
● 1 label;
● 2 forks;
● 1 napkin;
● 1 cubic meter of gas;
● 1 meter of adhesive tape.
Data is manually entered for all columns in this table, since each product can be unique, consisting of any combination of raw materials, materials, and components.
Table “Production prime cost of the tray Mix 4 vegetables” and all subsequent tables on this sheet
That is a fully automatic table, like all other tables on this sheet. You do not need to enter anything manually.
The upper table cell of column A – there is the name of the product here, the cost of which is calculated in the table.
Below, in column A – the names of raw materials, materials, and components are indicated.
In all other columns – the monthly prime cost of the product is indicated in the top cell of column A.
Pay attention to the title of the table. When you change the name of the products in the header of the table “Product tray filling structure” the name of the product changes automatically in the table “Production prime cost of the tray Mix 4 vegetables.”
In addition to that, the lines of the table change color to light green if the component listed in column A of that line is used in the product. That is made to immediately see which raw materials are used in the production of the product. For example, the product Mix 4 vegetables (look at the table above) uses tomatoes, cucumbers, bell peppers, and mushrooms, as well as components, and all these table cells are colored in green.
The product Tray 2 uses pineapple, grapes, pear, kiwi + components. That is why exactly these lines are automatically colored in light green (look at the table below).
All other tables on the “Prime cost” sheet are similar to the above two tables.
Fully automatic sheet. There is no need to manually enter any data here.
There are three tables on the sheet.
The first table contains the required volume of purchases of raw materials and materials in monetary terms. Volumes are shown every month for the entire period of the project.
The second table shows payments to suppliers based on the assumption that half of the deliveries are paid by the fact of the delivery (Actual payment), and the other half with a delay in one month.
The third table is the received accounts payable to the supplier formed based on the first two tables.
On this sheet you can see the warehouse movement of purchased raw materials, materials and components. The sheet is fully automatic, except for one indicator – the number of days for calculating the minimum warehouse balance.
The entire sheet is divided into identical blocks, in which the warehouse movement of all types of raw materials, materials and components is calculated. Each block contains three tables.
● warehouse movement in physical terms;
● calculation of the minimum warehouse balance;
● warehouse movement in monetary terms.
As soon as you enter this sheet, you will see the first two tables
The material movement in natural terms, in this case, kilograms is calculated in the first table. The table is fully automatic, and you do not need to enter anything manually.
The minimum warehouse balance is calculated in the second table. In order to do this, the number of days is manually entered in column A. It means that in this case the minimum warehouse balance is three days. The warehouse will always have the rest of this type of raw material, which is necessary for three-day of flawless operation. The calculation is carried out on working days. In the calculation, it is assumed that there are 22 working days in each month. The table cell where the data is manually entered is highlighted in orange.
The third table is situated in the same lines as the first table, but shifted from it to the right.
The movement of the same material is calculated here as in the first two tables, but in monetary terms, taking into account the price of the particular material from the sheet “Prime cost.”
All other blocks on this sheet are completely identical.
Fully automatic sheet.
There is the calculation of the required amount of raw materials, materials, and components for the production of the declared quantity of finished products.
The first table on the sheet shows the monthly production volume.
The data in the table is in physical terms, as in all other tables on this sheet.
The second table shows the total demand for raw materials and materials per month for all the types of finished products claimed in the first table (see above).
There are the tables below in which the demand for raw materials and materials for a particular product is calculated.
All other tables are the same. The data in the second table on this sheet is formed from the sum of the corresponding table cells for each type of material and component.
Sheet “FG warehouse”
This sheet calculates the movement of the finished goods in the warehouse. According to the logic, the construction of the tables on this sheet is the same as on the “Warehouse” sheet. The only difference is that the movement of raw materials and materials is described on the sheet “Warehouse,” and the movement of manufactured products is described here.
As you can see, everything is the same as on the sheet “Warehouse.” Moreover, there is also a table cell in column A of the second table, where you manually enter the number of days to determine the minimum inventory balance. Pay attention, working days are used for the calculation. There is also the assumption in the calculation that there are 22 working days in a month.
The third table on the sheet, similar to the “Warehouse” sheet, is shifted to the right relative to the first table and reflects the movement in the warehouse of the finished product in monetary terms.
The unique tables of the sheet “FG warehouse” are summarizing tables for the movement of goods in monetary terms.
These tables are shifted to the right and located above the third table in the described above block.
The first summary table is the “General warehouse report for finished products.”
It summarizes the data on the receipts and expenditures of all goods in monetary terms. Try to remember, finished products in the warehouse are considered at prime cost. Accordingly, the line “Expenditures” shows the shipment of goods to the buyer at prime cost, and this data is used on the sheet “Profit & loss account” in the line “Prime cost of the sold goods.”
The second summary table is “Reference data on product shipments at a prime cost in monetary terms.”
Actually, this table decrypts the line “Expenditures” from the previous table and shows exactly which products were sold.
Sheet “Product sales”
On this sheet, we determine the price and volumes of the sold products.
The first table is “Selling price.”
The price is manually set for each type of product here. Further, in all columns, the price set in column B is duplicated using links. You can change the price in any month of the project (in the corresponding column) by simply writing a number over the link, and the new price will be duplicated in all subsequent columns.
Column AL is the last column in the table and it automatically calculates the average monthly product profitability at the specified prices and the calculated prime cost.
The prime cost of products for profitability determination is located to the right of the above table.
The table is completed fully automatically.
The next table on this sheet is “Product sales in physical terms.”
You enter data of the volume of sales in tons, kilograms, pieces, etc. here. The data is entered in column B and then duplicated by links for all months of the project. You can change the sales volumes in the same way as the price, namely, manually enter data over the link in the table cell.
The next is a block of tables for product sales in monetary terms:
The entire block of tables is fully automatic.
The table “Product sales in monetary terms” shows the volume of sales per month throughout the project. The corresponding lines of the tables “Sales price” and “Product sales in physical terms” are multiplied to obtain the volume of sales in monetary terms.
The next table, expressed in one line, is “Returns.” It shows the volume of returned products for the period in monetary terms.
By subtracting the totals of the table “Product sales in monetary terms” and the table “Returns” we obtain the data in the table “Net sales in monetary terms.”
The actual cash flow is calculated further in the table “Cash inflow.” An additional condition is a payment by installments for 30 days. It means that money for the sold products will come only in the month following the sales month.
The last table in the block is “Accounts receivable.”
It reflects the accounts receivable from buyers formed as a result of sales.
Sheet “Product returns”
On this sheet, we determine the number of returnable products from buyers.
In the first table, we set the percentage of return on each product sold. Table cells where data is entered manually, as usual, are highlighted in orange.
The data is entered in column B and duplicated monthly for the entire project. The return percentage can be manually changed in any month. As it was described earlier, to do this, you need to write the return percentage on top of the link.
The second table is “Product return quantity, UAH”
The fully automatic table in which the number of returns in monetary terms is calculated. The total of this table falls on the “Product sales” sheet in the “Returns” table and reduces the volume of sold products for the planned period.
The third table is “Product return quantity, pcs.” The same information as in the second table, but in physical terms.
The fourth table is “The total number of semi-finished products return (for reference), kg”
The potential total receipt of raw materials in case of returned products unpacking is automatically calculated. This information is not used in further calculations. Those lines of the table with data on the return are highlighted in green.
Fifth table is “Returning semi-finished products from the tray Mix 4 vegetables.”
The potential receipt of raw materials is automatically calculated when the product “Mix 4 vegetables” is returned. Those lines of the table with data on the return are highlighted in green.
All of the following tables on the sheet are similar to the fifth table and they calculate the potential receipts of raw materials for each product in the project. The summation of data on these tables is shown in the fourth table on this sheet “The total number of semi-finished products return (for reference), kg.”
Sheet “Profit & loss account”
That is the main report showing the financial results of the project for the entire period as a whole and for each separate month in particular. How to draw up such a report you can read more here.
Let’s overview line by line:
Sales volume is the volume of products sold. Data from the sheet “Product sales” the results of the table “Product sales in monetary terms”;
Customer returns – data from the sheet “Product sales,” the “Returns” table.
Net sales volume is the difference between lines “Sales volume” and “Customer returns.”
Prime cost of the sold goods is the data from the sheet “FG warehouse,” table “General warehouse report for finished products” line “Expenditures.”
Gross profit is the difference between the lines “Net sales” and “Prime cost of sold goods.”
The gross profit rate,% determines how much gross profit is contained in the total sales. It is calculated as the ratio of the “Gross profit” line to the “Sales volume” line.
This section contains the fixed costs of the enterprise that are not directly related to the volume of production or sales.
Salary is staff payment. The amount is taken from the total of the table from the “Wage fund” sheet.
Accruals on wage funds are taxes on the payroll fund and calculated as 38% of the amount on the “Salary” line.
All subsequent lines in the “Operating costs” section are filled in manually. You describe the non-production costs that you will have during the project.
Total operating costs – the amount of the section “Operating costs.”
Operating income is the difference between the “Gross profit” lines.
VAT is the value-added tax. In this calculation, it is considered not as an indirect tax but as another type of cost making up 7% of sales.
Income tax is calculated as 5% of sales.
Net profit is the result of subtracting the lines “VAT” and “Income tax” from the line “Operating profit.”
Net profit margin,% determine how much net profit is contained in the total sales volume. It is calculated as the ratio of the “Net profit” line to the “Sales volume” line.
Sheet “Cash flow report”
The report is divided into three parts: operating activities, investment activities, and financial activities. That is the traditional division for such a report. You can find more information about the construction of this report here.
Let’s overview the table line by line:
Income from sales of goods, services – receipts of funds from buyers from the table “Cash inflow” from the sheet “Product sales.”
Payment to suppliers and contractors – payments for the supply of raw materials, materials, and components. The data comes from the second table from the “Purchase” sheet.
There are two lines left for additional costs in the direct manufacturing costs section.
Total direct costs – the amount for the direct costs section.
Payment of fixed expenses – such expenses are indicated in the operating expenses section on the sheet “Profit & loss account.” Pay attention, the payment of wages is shifted by one month since wages are paid with a delay of one month.
Payment of taxes – the sum of the lines “Accruals on wage funds,” “VAT,” and “Income tax” from the sheet “Profit & loss account.” The payment of taxes is also shifted by a month as well as the payment of wages.
Total fixed costs – the sum for the fixed costs section.
Net cash from operating activities – in this line the “Total income” and “Total direct costs” lines subtracted from the “Total fixed costs” line.
Purchase of capital assets – this line reflects the cash costs for initial investments from the “Investments” sheet.
Sale of capital assets – this line will contain data if the project involved the sale of fixed assets of the enterprise.
Net cash from investing activities – the difference between the lines “Sale of capital assets” and “Purchase of capital assets” where the line “Purchase of capital assets” is subtracted.
Financial section of the cash flow report
That is the only section of the report that is filled in manually. Therefore, the lines in the report are colored in orange.
In the line “Investments” – the data is entered when investments by business owners are needed. That happens when there is no money, according to the particular report. In this case, the cash balance figures in the lines “Monetary funds at the beginning of the period” and “Monetary funds at the end of the period” are colored in red.
As soon as there are enough monetary funds, the balance changes the color in black.
In the line “Withdrawals,” the data is entered when a decision is made to withdraw funds from the project.
Net cash from financial activities is the difference between the “Investments” and “Withdrawals” lines.
Monetary funds at the beginning of the period – the data in the cell comes from the line “Monetary funds at the end of the period” of the previous month.
Monetary funds at the end of the period – the value in the cell is formed by adding the lines “Monetary funds at the beginning of the period,” “Net cash from operating activities,” “Net cash from investing activities” and “Net cash from financial activities.”
All calculations on this sheet are automatic. The only table cell where data is entered manually is the cell A2 in the table header. It indicates the name of the enterprise.
The balance sheet can be hidden and look like this:
However, some sections of the asset can be expanded and look like this:
There is no reason to overview line by line sections of the balance sheet asset. The data gets automatically from the corresponding project sheets. Totals for all sections of the balance sheet asset are summarized in the line “Total assets.”
However, the balance liability is worth considering in more detail.
Additional investments are the data source for the line: sheet “Cash flow report” line “Net cash from financing activities.” Besides, this line is cumulative: the values from the previous period of this line are always added to the data entering into this line.
Financial result from the beginning of the year is also a cumulative line. It is determined as the sum of the value of this line from the previous month and the value of the line “Including for the reporting period” of the current month.
Including for the reporting period – this line shows data from the “Net profit” line of the “Profit & loss account” sheet. Determining the total for the “Equity capital” section, this line is not summed up.
Total equity capital – the sum of the lines “Capital at the beginning of the year,” “Additional investments,” and “Financial result from the beginning of the year.”
Accounts payable to suppliers is the data from the “Purchase” sheet.
Wage fund calculations are the data from the income statement. Sheet “Profit & loss account” line “Salary.”
Budget settlements – data from the sheet “Profit & loss account” the sum of the lines “Accruals on wage funds,” “VAT,” and “Income tax.”
Total of current liabilities – the sum of the lines “Accounts payable to suppliers,” “Wage fund calculations,” and “Budget settlements.”
Total liabilities are the sum of the “Total equity capital” and “Total of current liabilities” lines.
Validation line is the most important one on the sheet. It checks the equality of the asset and liability balance. If the line contains at least some digit, it means that the liabilities are not equal to the assets. It means you have violated the calculation algorithm somewhere.
There are two financial indicators on the “Balance” sheet. These indicators demonstrate the effectiveness of equity capital and investments. If you want, you can add a wide range of any other financial indicators.
Return on Equity (ROE) shows the profitability of a company’s equity capital. It is defined as the ratio of the line “Financial result from the beginning of the year” to the line “Total equity capital.”Return on Investment is defined as the ratio of the line “Financial result from the beginning of the year” to the line “Additional investments.”
Sheet “Full prime cost”
The full prime cost of production is calculated for reference on this sheet. The full prime cost is the manufacturing cost to which the fixed cost share has been added.
When you open this sheet, you will see such a picture.
These are calculations of the total prime cost of each product. Moreover, each table contains hidden lines to facilitate the perception of information.
Pay attention, the sheet starts from line 21. That means that there is a hidden part on the sheet. If you open it, you will see the table and a separate line:
The volume of product sales per month is shown in the hidden table. That is the full copy of the table “Product sales in physical terms” from the sheet “Product sales.”
The line below the table shows operating expenses (including VAT and income tax) from the “Profit & loss account” sheet. These tables are necessary on this sheet only to facilitate the preparation of formulas, so they are hidden in the file.
We will overview the table of the total prime cost of the product “Mix 4 Vegetables” and reveal the lines hidden inside the table:
As you can see, the table from the header to the “Total production prime cost” line is a complete copy of the production prime cost table from the sheet “Prime cost.”
The only difference is the last two lines.
Share of operating costs and taxes. This line defines the share of operating costs related to the released product in each month of the project. Depending on the unit weight of the product in total product sales for the month, the proportion of operating costs related to it varies.
Total full prime cost is the sum of the “Total production prime cost” and “Share of operating costs and taxes” lines.
This worksheet provides help for creating dynamic titles using the “CONCATENATE” formula. By changing on this sheet, for example, the table cell A8, “UAH” to “rub,” you will change the name of all headers in the file where the “Link” formula is applied.
Where to buy this file
If you are interested in this calculation, you can buy it in our store.