Budgeting of the enterprise in Excel with discounts

The budget for the next year is based with operation of the business: sales, procurement, production, warehousing, accounting, etc. The budget planning is a lengthy and difficult process, because it covers the major part of the framework of functioning of the organizations.

For a good example, we consider to the distribution firm and make it a simple enterprise budget example in Excel (the example of budget can be downloaded via the link below the article). In the budget, you can plan the cost of the bonus discounts for customers. It allows you to simulate to variety of loyalty programs while controlling to costs.



The data for budgeting income and expenses

Our firm serves about 80 clients, and the range of products is about 120 positions in the price list. It makes the margin on the goods of 15% of their cost price and thus sets the selling price. Such a low margin economically is justified by tight competition and is justified by the large turnover (as in many other distribution companies).

For customers offer to bonus system of remuneration and the percentage discount on the purchase for large customers and resellers.

The terms and the interest rate of the bonus system is determined by two parameters:

  1. The quantitative boundary. The quantity of purchased of the particular product, which gives to the client the opportunity to get the certain discount.
  2. The percentage discount. The discount is a percentage that is calculated from the sum, on which was acquired by the client in overcoming of the quantitative boundaries (strap). The size of the discount depends from the size of the quantitative limit: the more goods are purchased, the bigger the discount.

In the annual budget bonuses are referred to the section «sales of planning», so they affect on important indicator of the firm – is the margin (the profit figure as a percentage of total revenue). Therefore, an important task is the ability to install to several options of bonuses with different borders on the levels of implementation and the corresponding % bonus. It is necessary that the margin is kept within in certain limits (for example, not less than 7% or 8%, because it is the profit of the company). And clients will be able to choose for themself to several options of bonus rebates.

Our budget model with bonuses will be fairly simple, but effective. But first, let's prepare to the report of the movement of funds for a specific customer, to determine if we can give him discounts. You need to note the formulas that reference another worksheet before you calculate the discount percentage in Excel.



Budgeting of the enterprise to Excel, with consideration of loyalty

The project budget in Excel consists of two sheets:

  1. Sales – there are contains to the history of the movement of funds over the past year for a particular client.
  2. Results – there are the terms for the bonuses and simple account of the results of operations of the distributor, which determines to the forecast of indicators the attractiveness of the customer for the firm.

The movement of funds by customers

The structure of the table «Sales in 2015 on the client» on the sheet «sales»:

Sales in 2015.
  1. The product – Name of the goods.
  2. The purchase price – the prices at which a distributor buys products from suppliers.
  3. The purchase amount is the amount of product multiplied by its price.
  4. The number of sales – is the number of product which was sold to a specific customer for 1 year.
  5. The selling price = the purchase price + 15% margin. The formula of markup:
    6. selling price.
  6. Sales – is the amount for which goods sold.
  7. Bonus % - is the discount on a specific product, which has overcome the number of a certain boundary bar discounts. The formula:
    8. Bonus.
  8. The bonus amount – is the amount of discounts that the customer receives at overcoming of the quantitative border of a specific product (the value of the cells in that column reference is obtained from the cell calculation of bonuses on the sheet «Results»). The formula for calculating of the discount in Excel:
    9. calculating of the discount.
  9. The profit is calculated: Sales - Purchase amount - Bonus.

The budget model of the enterprise

On the second sheet we set to the bounds for achieving of the bonus and corresponding to the percentages of discounts.

The following table is the basic form of the budget of income and expenses in Excel with the overall financial performances of the firm for the annual period.

The structure of the table «Terms of the bonus system» on the worksheet «results»:

    results.
  1. The border bonus strap 1. The place for installation discounts at overcoming of the first border.
  2. Bonus %. The place for discounts at overcoming the first border. How to calculate the discount for the first border? It`s clearly visible on the sheet «sales». Using the function = IF (The number of > border 1 of the bonus strap [number]; Sales * 1% bonus discount; 0).
  3. The border of the bonus strap 2. Higher border compared to the previous border, which gives you to the opportunity to get the large share of discount.
  4. The bonus % 2 – there is the discount for the second border. It is calculated using the function = IF (The number of > 2 bonus strap [number]; Sales * 2% bonus discount; 0).

The structure of the table «The general report on the turnover of the firm» on the sheet «results»:

  1. The total volume of sales. The total amount of the product sold.
  2. The total purchase. The total amount has purchased goods from suppliers.
  3. The total bonus. There is the total amount of discounts.
    4. total bonus.
  4. The GROSS profit: The total sales -The total purchases – The total bonus.
    5. GROSS Profit.
  5. The margin 1: The GROSS Profit / The total sales (in percentage terms of dirty profit).
    6. margin 1.
  6. The selling expenses – the amount of expenditures for distribution of goods (logistics, shipping, advertising, etc.).
  7. The management costs – total costs of staff salaries, taxes, etc.
  8. The NET profit (net income) – GROSS Profit – The cost of implementing – The management Costs.
    9. NET Profit.
  9. The margin 2 - NET Profit / Total sales (in percentage terms).
margin 2.

The ready template of the company's budget in Excel

So we have a model of the enterprise budget in Excel that is dynamic. If the edge of the plank bonus is at 200, and the bonus discount is 3%. This means that last year the client bought goods in quantity 200 units, and at the end of the year he will receive to the bonus 3% discount of the cost. If the client purchased 400 units of a certain product, so he took the second boundary bar bonuses and gets a discount for 6% already.

Under these conditions, the indicator «Margin 2» is changed, that is, the net profit of the distributor!

Read also the continuation of this article: How to calculate discounts in Excel?

The head of a distribution firm must find to the optimal levels of edge strips for providing discounts for customers. You need to choose so that the indicator «Margin 2» was at least in the range of 7% - 8%.

Download enterprise budget-bonus (the sample in Excel).

Not to look for the best solution at random, and make no mistakes, we recommend to read to the following article. There is described how to do in Excel to the simple and efficient tool: The data table in Excel and the matrix numbers. Using «data table» it is possible in the automatically regime to visualize the optimal conditions for the client and distributor.


en ru