#How to link cells in excel budget how to

The same way we can construct to the matrix for the indicator «NET Profit» and make for it to the desired conditional formatting. Thus, we have calculated to the optimal discounts for the border 1 with the current conditions of the border 2 and its bonus.īonus 2 and level for border 2 in the same way expect, only don't forget to fill in the parameters correctly references $B$4 $B$5 are respectively. To do this, conditional formatting allows us to make a selection of data from the Excel table according to the criteria. We can easily manage by the ratio of the quantity and discount and balance between the benefit for the customer and the seller. Now we can clearly see to the corridor for our profits, which we can't go out for saving to the profit in certain limits. Specify to the borders from 7% to 8%, and specify to the desired format.

Choose to the tool: «HOME»-«Conditional Formatting»-«Highlight Cells Rules»-«Between».

We allocate to the obtained results, and this is the cell range E3:M17.

The analysis what if in Excel data tableįor the analysis using the data visualization we add to the conditional formatting:

In front of us once 135 options (all options should set to the format of cells in %). For calculating discounts of quantitative discount border 2 in the boundary settings you should to show to the references on $B$4 and $B$5 are respectively.Ĭlick OK and the entire table is populated with the performance indicators «Margin 2» under appropriate conditions of bonus systems. In the bottom field we refer to the value of the cell boundaries of percentage bonuses $B$3.Īttention! We expect to the optimal discounts for quantity 1 at current rates of boundary 2.The top field we fill by the absolute reference to the cell with a boundary strip of bonuses number $B$2.The dialog box «Data Table» will appear on the input parameters: Now for creating to the table data, you should choose to the «DATA», the section tools «Data Tools», tool «What-if Analysis» and option «Data Table». Then we allocate to the range of cells D2:M17. Now in the cell D2 we enter the formula is the same as for the calculation of the indicator «Margin 2»: = B15/B8 (the number format of cell is %). The string must contain a quantitative limit values for power-UPS, for example, all numbers from 100 to 500 in multiples of 50, and interest bonuses ranging from 3.0% to 10.0% of a multiple of 0.5%.Īttention! The cell (in this case D2) of the intersection of the row and column with filled values must be empty: as in the figure. The schema of the source data on the similarity of the «Table of Pythagoras».

#How to link cells in excel budget how to

To build such table you need to read the previous article: how to create a budget in Excel. The budget model company and the terms of the bonus system.