Got stuck with a budget issue for my company. Any help is appreciated.
THE TOTAL IN F3 AND F5 SHOULD BE THE SAME, 500.
The question is why the total in cell F4 is 537,5 and not 500? since 50% of 100 is 50 and 100% of 200 is 200 and 50% of 75 is 37,5 and 200% of 125 is 200 (50+200+37,5+250=537,5 and not 500)
My guess is that in E4, it should not be 200% adjustment for it to become 500, but something else. But the logic still tricks me somehow.
This is what the excel sheet look like.
- A sales person has a yearly budget, divided in to quarters. If he fails a quarterly budget, there are %-adjustments up/down the next quarter so the year result becomes the same. This is for commision calculation purposes,
- The budget total for the year is 500 (as seen in cell F2)
- Row-2 means how much % of the budget should be completed in that quarter. So in B2 that is 20% means 20% of 500=100 which goes to B3.
- Row-3 are the adjustments, so b4 means he only made 50% of the amount in b3. Same in D4.
THE TOTAL IN F3 AND F5 SHOULD BE THE SAME, 500.
The question is why the total in cell F4 is 537,5 and not 500? since 50% of 100 is 50 and 100% of 200 is 200 and 50% of 75 is 37,5 and 200% of 125 is 200 (50+200+37,5+250=537,5 and not 500)
My guess is that in E4, it should not be 200% adjustment for it to become 500, but something else. But the logic still tricks me somehow.
This is what the excel sheet look like.
| A | B | C | D | E | F | |
| 1 | quarter | q1 | q2 | q3 | q4 | total |
| 2 | % of budget should be finished | 20% | 40% | 15% | 25% | 100% |
| 3 | sales budget | 100 | 200 | 75 | 125 | 500 |
| 4 | % adjustments up and down | 50% | 100% | 50% | 200% | |
| 5 | sum after % adjustments | 50 | 200 | 37,5 | 250 | 537,5 |