Hello Experts,
I am working on a production forecast in an MS Excel file, where every month we add machines with different capacity levels: Level 1, Level 2, and Level 3.
- Level 1 and Level 2 Machines: These machines operate at 100% efficiency from the first month of purchase.
- Level 3 Machines: These machines have a unique efficiency progression:
- 50% efficiency in the first month of purchase.
- 75% efficiency in the second month.
- 100% efficiency from the third month onward.
Additionally, Level 3 machines have a seasonal production rate:
- From June to September 2024, they produce 300 units per month.
- From October to December 2024, they produce 400 units per month.
I need help to add a formula in the production table that calculates the efficiency rate and the seasonal production rate for every new purchase of a Level 3 machine.
Thank you for your assistance.
In the Efficiency Rate column for Level 3 machines (identified in the Machine Level column), you can use a nested IF statement to consider the purchase month: slope game
=IF(AND(Machine_Level=3, Purchase_Month=MONTH(TODAY())), 0.5,
IF(AND(Machine_Level=3, Purchase_Month+1=MONTH(TODAY())), 0.75, 1))
This formula checks two conditions:
Machine Level = 3: Ensures the formula applies only to Level 3 machines.
Purchase Month vs. Current Month:
If the machine was purchased in the current month (MONTH(TODAY())), efficiency is 0.5.
If the machine was purchased one month prior (Purchase_Month+1 = MONTH(TODAY())), efficiency is 0.75.
Otherwise (for all Level 3 machines purchased before the second month or Level 1 & 2 machines), the efficiency is automatically 1.
