iorewhat.blogg.se - Business budget template for numbers

#Business budget template for numbers how to#
#Business budget template for numbers install#

The selling expenses – the amount of expenditures for distribution of goods (logistics, shipping, advertising, etc.).

The margin 1: The GROSS Profit / The total sales (in percentage terms of dirty profit).

The GROSS profit: The total sales -The total purchases – The total bonus.

The total amount has purchased goods from suppliers. The structure of the table «The general report on the turnover of the firm» on the sheet «results»: It is calculated using the function = IF (The number of > 2 bonus strap Sales * 2% bonus discount 0).

The bonus % 2 – there is the discount for the second border.

Higher border compared to the previous border, which gives you to the opportunity to get the large share of discount. Using the function = IF (The number of > border 1 of the bonus strap Sales * 1% bonus discount 0).

#Business budget template for numbers how to#

How to calculate the discount for the first border? It`s clearly visible on the sheet «sales». The place for discounts at overcoming the first border. The place for installation discounts at overcoming of the first border. The structure of the table «Terms of the bonus system» on the worksheet «results»: 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. On the second sheet we set to the bounds for achieving of the bonus and corresponding to the percentages of discounts.

The profit is calculated: Sales - Purchase amount - Bonus.

The formula for calculating of the discount in Excel:

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

Bonus % - is the discount on a specific product, which has overcome the number of a certain boundary bar discounts.

Sales – is the amount for which goods sold.

The selling price = the purchase price + 15% margin.

The number of sales – is the number of product which was sold to a specific customer for 1 year.

The purchase amount is the amount of product multiplied by its price.

The purchase price – the prices at which a distributor buys products from suppliers.

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

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.

Sales – there are contains to the history of the movement of funds over the past year for a particular client.

The project budget in Excel consists of two sheets: You need to note the formulas that reference another worksheet before you calculate the discount percentage in Excel.īudgeting of the enterprise to Excel, with consideration of loyalty

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. Our budget model with bonuses will be fairly simple, but effective. And clients will be able to choose for themself to several options of bonus rebates.

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

#Business budget template for numbers install#

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. 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). The size of the discount depends from the size of the quantitative limit: the more goods are purchased, the bigger the 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 quantity of purchased of the particular product, which gives to the client the opportunity to get the certain discount.

The terms and the interest rate of the bonus system is determined by two parameters: Such a low margin economically is justified by tight competition and is justified by the large turnover (as in many other distribution companies).įor customers offer to bonus system of remuneration and the percentage discount on the purchase for large customers and resellers. It makes the margin on the goods of 15% of their cost price and thus sets the selling price.

Our firm serves about 80 clients, and the range of products is about 120 positions in the price list. The data for budgeting income and expenses