How to forecast in Excel

[goodmachine]

I have a forecasting question that I would like to solve in Excel. I need to be able forecast what the Inventory Completed should be in the future weeks to be at 90% SLA.

• Date: represents a business week in a calendar month
• Week: represents the first week in a calendar month (i.e., does not start on the 1st of the month, but on the first Monday of the calendar month)
• Inventory completed: represents all of the inventory completed that month (total of all inventory that met/did not meet 7 day SLA)
• < 7 Days: inventory aging less than 7 days
• > 7 Days: inventory aging greater than 7 days
• Status: Yellow if the % at SLA is less than 90%; Green if the % at SLA is 90% or greater

For my forecast, I want to know what volume we must complete to get our SLA at 90%, so the Status moves from Yellow to Green.



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Week #[/TD]
[TD]Inventory Completed*[/TD]
[TD]< 7 Days[/TD]
[TD]> 7 Days[/TD]
[TD]% at SLA[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]9/4 - 9/8[/TD]
[TD]1[/TD]
[TD]1000[/TD]
[TD]850[/TD]
[TD]150[/TD]
[TD]85%[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]9/11 9/15[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/18 - 9/22[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/25 - 9/29[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 416"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*SLA is 7 days[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[istiasztalos]

Hi,

Your model would depend on multiple variables (and seems to be more like a business question, not an Excel calculation one -- or I just don't understand what you are looking for, in your table which column would you like to fill with formula?)

How much is your incoming work that would will have to complete in 7 days?

Are those aging < 7 days completed or open work items?
- If they are incomplete, how many of those can you still complete within SLA?
-If the complete, how many incomplete items do you have? (a.k.a what is your backlog? especially those that will fail the SLA upon completion)