Calculate Number To Reach Target-Part 2

Darkhyam and anyone on the forum,

My apologies.

Let me explain again how this works.

My company will assign my dept a target to achieve for competed items. This target is calculated based on the formula below:

=((14,500*0.36)+((C3*0.13)*2))*0.44*1.05

= 4163 items

All items in the above formula is fixed everymonth except the number 14,500. This is the total items forecasted to be received in my company for the month of December. Out of this number, my dept is expected to complete 4163 items.

At the end of the month , the same formula is used to determine whether my dept achieved the target or not. However, at the end of the month, actual received for the month of December is used instead of the forecasted amount. Assuming the actual received by the company is 15,502, then my dept should have completed 4440 as below:

=((15.502*0.36)+((C3*0.13)*2))*0.44*1.05

= 4440 items

Therefore, everyday we need to key in the total received by the company to check how many items need to be completed by my dept.

I need to create a formula to tell me how many items I need to complete everyday based on actual received everyday.

The tricky part is this. The target will vary everyday since actual received will differ.

In addition, my dept will not be held as breach of target if the total actual received by the company was less by more than 10% compared to the forecasted amount or more by 10%.

So if it actual forecasted was less than 10% ( say 6%), then we will still need to achieve that target based on that amount ( the target formula will need to calculated based on less than 6% which is 13,360) and the same applies if target is more but less than 10%

I hope this helps. I have reattached the amended spreadsheet to for a clearer picture.

Appreciate help from all.