An entity is a specific object of a business process that combines elements with similar characteristics inherent only to this object. For example, raw materials, energy resources, labor resources, etc., these are different entities.
An important task in calculating business plan of any project is to determine the total need for raw materials, materials, energy resources, piece-work, based on a given production program.
Always when developing a business plan and building financial and economic model, based on which a forecast of commercial performance is made The functionality is laid down for calculating and summarizing the general needs of any entity in the context of its elements.
The calculation is based on the binding of each type of product (planned production program) to the specified consumption rates of certain elements of the entity.
This is a very important argument of the synthetic calculation, on which the rest of the resulting data of the financial and economic section of the business plan are tied (calculation working capital, cash flow, project balance sheet).
Many people build their financial and economic models based on Microsoft Excel, because this is the simplest and most convenient mechanism for designing an open source business economy. Therefore, let's look at a simple example, the algorithm for solving this problem in Excel using its important built-in functions VPR and SUM.
To do this, we will create a total of 5 tables.
The first of them is the initial production program for the types of products with the corresponding volume of production.
A prerequisite is the product ID field, which is its identifier that determines its uniqueness. I.e., each identifier has a specific product assigned to it, which excludes its repeatability and non-uniqueness.
The same is true for the list of raw materials/material components that can be used in the production of goods. This is already table 2.
We arrange the rationing of all our types of products (goods) in table 3 according to the specified sample. The product identifiers and the raw materials assigned to them are put down manually, and the corresponding names are stretched across each column using the specified formulas (the VPR function). After that, we normalize our products by types of raw materials and materials, putting certain values in the last column of this table.
Table 4 should have a similar structure as Table 3. The only difference is that the resulting last column (consumption of raw materials/material per quantity of goods) will be output using the VPR function multiplied with the consumption rate indicated in Table 3.
In this case, the VPR function searches for the corresponding product by the identifier from Table 1 and outputs its volume, after which it is multiplied by the corresponding consumption rate.
As a result, we have a common need for raw materials/material elements by product type, but not yet grouped by each type.
We solve this problem in the calculation table 5, where in the last column, using the fiction , we sum up and reduce the total need for raw materials and materials for each of its types.
As a result, thus, based on this algorithm, it is possible to easily and simply solve the problem of calculating the total needs of any entity when building a financial and economic model of a business plan in Excel, regardless of the number of products and elements of the entity itself used in their production.
If necessary, you can take this xlsx file here.
