Яндекс.Метрика
qr-код - Как-то так стрелка - Как-то так
Leading Economist + ... пиктограмма - Как-то Так THIS     Economic literacy and web solutions
All on the topic: what, how and why. Actual things - in their own words.
Turn your quality WITH ME - BUTW !
If you don't like what you get, change what you give. © Carlos Castaneda     KAKTOTAK.BY - economic design and web development services (php/js - telegram, WordPress, Laravel, Vue, SEO)    Cooperation - on an ongoing basis    Be in the topic   -    SUBSCRIBE      My notes are ECONOMICS +  и  WEB Developer's Guide | php+js+seo   в Telegram   telegram  

How to calculate the total need of any entity in EXCEL when building a financial and economic model of a business plan

How to calculate the total need of any entity in EXCEL when building a financial and economic model of a business plan

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.

VPR and SUM UP - how to calculate the total need in EXCEL - 1

The first of them is the initial production program for the types of products with the corresponding volume of production.

VPR and SUM UP - how to calculate the total need in EXCEL - 2

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.

VPR and SUM UP - how to calculate the total need in EXCEL - 3

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.

VPR and SUM UP - how to calculate the total need in EXCEL - 4

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.

VPR and SUM UP - how to calculate the total need in EXCEL - 5

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.

VPR and SUM UP - how to calculate the total need in EXCEL - 6

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.

автор - Михаленко Р.
M R. Автор - kaktotak.by Specialization: financial and economic design - modeling of business, investment projects of the real sector, analysis and evaluation of efficiency, optimization of the management decision system.

A wide range of web-based competencies for solving business problems.

Subscribe to my telegram channel - My notes are ECONOMICS +
Improve your quality with me - what, how and why in economics in a simple way. Micro and macro aspects.

And also - WEB Developer's Guide | php+js+seo
Notes and native solutions to simple local tasks in PHP, JS. Something about Laravel, WordPress, Vue and SEO.

  Personal assistance in economic design and web development:

  • Financial and economic modeling, analysis, accounting, business planning
  • Comprehensive web development/project support on the web (php/js, seo – Laravel, WordPress, Vue, telegram, administration, content, advertising in Yandex Direct

  telegram или форма обратной связи

Administrator
377
0
Name
E-mail
Rating
Review

Currency Converter
RUB RUB-icon
USD USD-icon
EUR EUR-icon
CNY CNY-icon
BYN BYN-icon
UAH UAH-icon
KZT KZT-icon
SHORT- what is it about
ECONOMIC LITERACY
  Simple online solutions to problems of economics and finance  
I Want To Know Everything. Useful Tips