Dear All,
Please help me to used the formula in excel as in the table below.
In the excel we have the daily target which already set, however if the sell is not reach the target, then variance value needs to divide by the total remaining days.
For example, on 1-Aug-2022 the daily target is $2,000, then the sale achievement is not $1,500. So the variance value to achieve is $500, then $500/6 days left, thus each day need to increase the target by $83.33. So the target in 2-Aug-2022 is $2,083.33. and If the same in 2-Aug-2022 is $3000, then the revised target should be lower in 3-Aug-2022. However, I want to maintain the same value as "Daily Target".
Please see the below table, so I want to know what formula is for this kind of table.
Please help me to used the formula in excel as in the table below.
In the excel we have the daily target which already set, however if the sell is not reach the target, then variance value needs to divide by the total remaining days.
For example, on 1-Aug-2022 the daily target is $2,000, then the sale achievement is not $1,500. So the variance value to achieve is $500, then $500/6 days left, thus each day need to increase the target by $83.33. So the target in 2-Aug-2022 is $2,083.33. and If the same in 2-Aug-2022 is $3000, then the revised target should be lower in 3-Aug-2022. However, I want to maintain the same value as "Daily Target".
Please see the below table, so I want to know what formula is for this kind of table.
| Book1 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 3 | Date | Daily Target | Actual Sales | Achieved % | Variance | Revised Target | Revived Target (We want) | Days Left | Remark | ||
| 4 | 1-Aug-22 | $ 2,000.00 | $ 1,500.00 | 75% | $ (500.00) | $ 2,000.00 | $ 2,000.00 | 7 | |||
| 5 | 2-Aug-22 | $ 2,000.00 | $ 3,000.00 | 150% | $ 916.67 | $ 2,083.33 | $ 2,083.33 | 6 | |||
| 6 | 3-Aug-22 | $ 2,000.00 | $ 1,000.00 | 50% | $ (1,000.00) | $ 1,916.67 | $ 2,000.00 | 5 | If the "Revised Target" is lesser than the "Daily target", then the revise target should be return to same value the same as "Daily Target" | ||
| 7 | 4-Aug-22 | $ 2,000.00 | $ 2,000.00 | 100% | $ (145.83) | $ 2,145.83 | $ 2,145.83 | 4 | |||
| 8 | 5-Aug-22 | $ 2,000.00 | $ 3,000.00 | 150% | $ 756.94 | $ 2,243.06 | $ 2,243.06 | 3 | |||
| 9 | 6-Aug-22 | $ 3,000.00 | $ 3,000.00 | 100% | $ - | $ 2,986.11 | $ 3,000.00 | 2 | If the "Revised Target" is lesser than the "Daily target", then the revise target should be return to same value the same as "Daily Target" | ||
| 10 | 7-Aug-22 | $ 3,000.00 | $ 3,000.00 | 100% | $ - | $ 2,972.22 | $ 3,000.00 | 1 | If the "Revised Target" is lesser than the "Daily target", then the revise target should be return to same value the same as "Daily Target" | ||
| 11 | $ 16,000.00 | $ 16,500.00 | 103% | ||||||||
Sheet1 (2) | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D4:D11 | D4 | =C4/B4 |
| E4:E10 | E4 | =C4-G4 |
| F4 | F4 | =B4 |
| G4 | G4 | =B4 |
| F5:F10 | F5 | =-SUM($E$4:E4)/H5+B5 |
| G5,G7:G8 | G5 | =-SUM($E$4:E4)/H5+B5 |
| B11:C11 | B11 | =SUM(B4:B10) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| D11,D4:E10 | Cell Value | <0.95 | text | NO |
