Forum Discussion
add a calculated column
Hi,
I need help with the implementation of a new column. In this column, the realized sales must be substracted from the planned sales (from the past quarters and current quarter) and distributed to the following quarters.
Examples:
Planning in the first quarter:
| quarter | realized sales | planned sales | planning |
| Q1 | 96.550€ | 100.000€ | |
| Q2 | 0€ | 100.000€ | 101.150€ |
| Q3 | 0€ | 100.000€ | 101.150€ |
| Q4 | 0€ | 100.000€ | 101.150€ |
| total | 96.550€ | 400.000€ | 303.450€ |
the sales in the first year are 96.550€. The difference between the realized and planned sales is 3.450€. The 3.450€ are distributed on the planned sales of the next quarters. (1.150€ are added to the planned values of the next quarters.)
when we are in the second quarter the planning should look like this:
| quarter | realized sales | planned sales | planning |
| Q1 | 95.550€ | 100.000€ | |
| Q2 | 101.900€ | 100.000€ | |
| Q3 | 0€ | 100.000€ | 100.775€ |
| Q4 | 0€ | 100.000€ | 100.775€ |
| total | 198.450€ | 400.000€ | 201.550€ |
3.450€ are missing from the first quarter. The difference between realized and planned sales from the second quarter are -1.900€ (100.000€ - 101.900€ = -1900€), so 1.900€ more were achieved than planned. The difference between realized and planned sales from Q1 and Q2 is 1.150€ (realized sales: 3.450€ + (-1.900€) = 1550€). 1550€ must be distributed over the quarters 3 and 4.
Thanks!
5 Replies
Hi,
I need help with the implementation of a new column. In this column, the realized sales must be subtracted from the planned sales (from the past quarters and current quarter) and distributed to the following quarters.
Examples:
Planning in the first quarter:
quarter realized sales planned sales planning Q1 96.550€ 100.000€ Q2 0€ 100.000€ 101.150€ Q3 0€ 100.000€ 101.150€ Q4 0€ 100.000€ 101.150€ total 96.550€ 400.000€ 303.450€ the sales in the first year are 96.550€. The difference between the realized and planned sales is 3.450€. The 3.450€ are distributed on the planned sales of the next quarters. (1.150€ are added to the planned values of the next quarters.)
when we are in the second quarter the planning should look like this:
quarter realized sales planned sales planning Q1 96.550€ 100.000€ Q2 101.900€ 100.000€ Q3 0€ 100.000€ 100.775€ Q4 0€ 100.000€ 100.775€ total 198.450€ 400.000€ 201.550€ 3450€ are missing from the first quarter. the difference between realized and planned sales from the second quarter are -1900€ (100.000€- 101.900€= -1.900), so 1900€ more were achieved than planned. The difference between realized and planned sales from Q1 and Q2 is 1550€ (realized sales: 3450€+(-1900€) = 1550€). 1550€ must be distributed over quarters 3 and 4
Thanks!Hi,
I need help with the implementation of a new column. In this column, the realized sales must be subtracted from the planned sales (from the past quarters and current quarter) and distributed to the following quarters.
Examples:
Planning in the first quarter:
quarter realized sales planned sales planning Q1 96.550€ 100.000€ Q2 0€ 100.000€ 101.150€ Q3 0€ 100.000€ 101.150€ Q4 0€ 100.000€ 101.150€ total 96.550€ 400.000€ 303.450€ the sales in the first year are 96.550€. The difference between the realized and planned sales is 3.450€. The 3.450€ are distributed on the planned sales of the next quarters. (1.150€ are added to the planned values of the next quarters.)
when we are in the second quarter the planning should look like this:
quarter realized sales planned sales planning Q1 96.550€ 100.000€ Q2 101.900€ 100.000€ Q3 0€ 100.000€ 100.775€ Q4 0€ 100.000€ 100.775€ total 198.450€ 400.000€ 201.550€ 3450€ are missing from the first quarter. the difference between realized and planned sales from the second quarter are -1900€ (100.000€- 101.900€= -1.900), so 1900€ more were achieved than planned. The difference between realized and planned sales from Q1 and Q2 is 1550€ (realized sales: 3450€+(-1900€) = 1550€). 1550€ must be distributed over quarters 3 and 4
Thanks!"The 3.450€ are distributed on the planned sales of the next quarters."
Which quarters? The remaining quarters of the same year? If so - what should happen in Q4?
No idea if this is what you are after?
See attached.
