Age of Queue each day based on 1 assumption

[Michaelm1122]

I am trying to figure out how to automate or simplify a manual report. I was to track the age of queue of items from one day to the next based on an assumption of how many items we can get through. So for example I have 100 items currently at 58, and then 350 at day 57 and so forth. The assumption is that I can close 50 items a day, but I need to figure out based on a varying inventory from day to day and an assumption of what can be completed each day of what the age will be in the future. The goal is to show the age in a chart so that I see what the age of my queue will be in one month.

 

[Michaelm1122]

Sorry I thought you had enough info is there anyway I can send you my worksheet? I Can't figure out how to attach on this website

 

[Jerry Sullivan]

Michael, Thanks for sending a copy of your file. Here's a screen shot of part of your worksheet for the benefit of others that find this thread...

Excel 2013
	A	B	C	D	E	F	G	H
5	Monday - January 11th
6	Age	83	82	81	78	77	76	75
7	Row Labels
8	Queue	64	258	279	237	563	536	304
9
10	Production	275				(day 82)
11
12	Tueday - January 12th
13	Age			83	82	79	78	77
14	Row Labels
15	Queue			47	279	237	563	536
16
17	Production	275				(day 82)
Sheet1

Your worksheet has about 30 of these sets of data showing the forecasted queue for each day.

If your objective is to create a chart that shows the forecasted age of the queue for each of the next 30 days, it would seem that the minimum inputs needed are:

1. The make up of the queue today (Rows 5:8)
2. The daily production or items to be cleared each day (B10, B17...)

Note that since your daily production is not constant (you have variations from 275 farther down the sheet), this calls for a slightly different approach than if you assumed a constant 50 items/ day.

Do you have control over the way this data is organized, and would you be open to changing that if it made the process faster and easier to maintain?

Are the other 29 sets of data needed for your use for other purposes, or are they only being used as a means for you to make the 30 day forecast?

 

[Michaelm1122]

Jerry yes I have control over the way the data is organized. The age of the queue is essentially the oldest item in the queue minus todays date. The method that you see here is the inefficient way you are helping me solve for. The production per day is set a week at a time based on the amount of resources available to work the queue in a given week that is why you see a change in the production number. You are correct to assume we are only interested in the tail of the data each day essentially what day are we working the queue at each day in the future. The forecast is updated once a week with the current inventory numbers for each day, and the updated assumption of what will get closed each week. The challenge is that on any given day we may be able to get through that days worth of inventory and move to the next day and possible three day ahead, or if they volume is high it may take two or three days to work through that days batch of inventory.

 

[Michaelm1122]

Ideally it would be great if I could take this all the way in to the future and use as sort of a goal seek to see what our production needs to be to drive down the age of the queue. So the inventory fluctuates each day and we have new inventorycome in every day. However the part we have control over is the production, if I add resources, gain efficiencies, the volume drops and can drive the age of the queue down. So for example if I can get production to 1000 a day I can drive the age down. This is the part that is forecasted potentially making all inventory dates in the future relevant

 

[Jerry Sullivan]

Michael, Here's a simpler process for you to consider.

List inventory by inventory create date as shown here. For dates, don't use text strings like "Monday - January 11th", but rather: 1/11/2016. This allows you use the dates to make calculation. You can use number formatting to customize the way the date values are displayed.

Excel 2013
	A	B	C	D	E	F	G
1	As of Start of Day
2	Monday - January 11	10/20	10/21	10/22	10/25	10/26	10/27
3	Queue	64	258	279	237	563	536
4	Queue Running Total	64	322	601	838	1401	1937
Sheet1

Cell Formulas
Range	Formula
B4	=N(A4)+B3

Then use a table like this to enter your assumptions about daily production and calculate the age of the queue based on those assumptions.

Excel 2013
	A	B	C	D	E	F	G
7		Forecast	Running	Match	Queue	Age of	Remaining
8	Production Day	Production	Production	Column	Date	Queue	Items
9	Monday - January 11	275	275	1	10/21	82	47
10	Tuesday - January 12	275	550	2	10/22	82	51
11	Wednesday - January 13	275	825	3	10/25	80	13
12	Thursday - January 14	275	1,100	4	10/26	80	301
13	Friday - January 15	275	1,375	4	10/26	81	26
14	Monday - January 18	100	1,475	5	10/27	83	462
15	Tuesday - January 19	300	1,775	5	10/27	84	162
16	Wednesday - January 20	300	2,075	6	10/28	84	166
17	Thursday - January 21	300	2,375	7	10/29	84	118
18	Friday - January 22	300	2,675	8	10/31	83	118
19	Monday - January 25	300	2,975	11	11/4	82	63
20	Tuesday - January 26	300	3,275	12	11/5	82	106
21	Wednesday - January 27	300	3,575	13	11/8	80	78
22	Thursday - January 28	300	3,875	14	11/9	80	393
23	Friday - January 29	300	4,175	14	11/9	81	93
24	Monday - February 01	325	4,500	15	11/10	83	412
25	Tuesday - February 02	325	4,825	15	11/10	84	87
26	Wednesday - February 03	325	5,150	16	11/11	84	113
27	Thursday - February 04	325	5,475	17	11/12	84	70
28	Friday - February 05	325	5,800	18	11/15	82	74
29	Monday - February 08	350	6,150	19	11/16	84	531
30	Tuesday - February 09	350	6,500	19	11/16	85	181
31	Wednesday - February 10	350	6,850	20	11/17	85	666
32	Thursday - February 11	350	7,200	20	11/17	86	316
33	Friday - February 12	350	7,550	21	11/18	86	304
Sheet1

Cell Formulas
Range	Formula
A9	=A2
A10	=A9+IF(WEEKDAY(A9)=6,3,1)
C9	=B9+N(C8)
D9	=MATCH(C9,$B$4:$BN$4,1)
E9	=INDEX($B$2:$BN$2,1,D9+1)
F9	=A9-E9
G9	=INDEX($B$4:$BN$4,1,D9+1)-C9

 

[Michaelm1122]

It is working pretty well, for some reason however it is giving me #N/A errors just on the D9,E9,F9, and G9? All of the rest of the rows have data filled in.

 

[Jerry Sullivan]

Michael, That's my bad Match will a match_type parameter "less than" will error if the lookup value is less than the first value in the lookup.

I hadn't anticipated the scenario of the first day production not clearing the first day of inventory.

Just change the formula in D9 this and copy down:

=IFERROR(MATCH(C9,$B$4:$BN$4,1),0)

 

[Michaelm1122]

Can you give me a brief explanation of how the formulas work to accomplish this task? Thanks

 

[Jerry Sullivan]

Can you give me a brief explanation of how the formulas work to accomplish this task? Thanks

Here's an explanation based on the screen shot in Post #15 (with corrected formula in Post #17):

[B]B4: =N(A4)+B3  [/B]
Calculates running total of queue items. 
The N() function avoids an error if non-numeric value is in referenced cell.

[B]A9:=A2 [/B]
Carries down start date to ensure production table starts on same date as the inventory in Rows 2:4

[B]A10: =A9+IF(WEEKDAY(A9)=6,3,1) [/B]
Lists dates and skips weekends. If previous date was a Friday, returns 
next Monday's date, otherwise returns the next date.

[B]C9: =B9+N(C8) [/B]
Calculates running total of forecasted production. The N() function avoids an error 
if a non-numeric value is in referenced cell.

[B]D9: =IFERROR(MATCH(C9,$B$4:$BN$4,1),0) [/B]
Finds the largest value in the Queue Running Total (Row 4) that is less than or equal 
to the lookup value (the Running Production Total for that date). Returns the 
Column number for that matched item. The IFERROR part handles the scenario 
that no values in the Queue Running Total are less than the lookup value.

[B]E9: =INDEX($B$2:$BN$2,1,D9+1) [/B]
Returns Queue Date by looking in the Inventory Dates (Row 2). The Queue Date 
will be one Column to the right of the Column found by the Match formula in D9.

[B]F9: =A9-E9 [/B]
Calculates Age of Queue by subtracting Queue Date (10/21/2015) 
from Production Date (1/11/2016)

[B]G9 =INDEX($B$4:$BN$4,1,D9+1)-C9 [/B]
Calculates how many items will be remaining from the Queue Date's inventory. 
Subtracts the Running Production Total from the Queue Running Total for 
the Queue Date and returns the difference.

Just ask if you'd like me to elaborate further on any of these.