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.

 

[Jerry Sullivan]

It's not clear to me whether your term "the age of my queue" refers to a single value (like a weighted average = 42 days) or a collection of values (1000 items at 1 day, 800 items at 2 days.... 1 item at 92 days).

In either scenario, even if your assumption of completing 50 items a day is correct, that doesn't seem to be enough information to forecast the future "age of my queue". Wouldn't closing 50 items that are 100 days change the age of your queue differently than closing 50 items that are 10 days outstanding?

 

[Michaelm1122]

So today the queue is at 59, and there are still 50 items left to close, the age is based on today's date minus the create date of the inventory.12/9/2015. So I have an assumption of what can be closed each day, 50. But the inventory changes from day to day so some days only a few items can come in, but other days the number can be higher like 350, which would take, 7 days to work through. So if I only had 5 items in the oldest inventory today, and 45 for 12/10, I could get through today and tomorrow and the age would be 57 tomorrow.

 

[Jerry Sullivan]

Sorry, but I don't understand parts of your description.

So today the queue is at 59, and there are still 50 items left to close,...

Does this mean you have 50 items left to close in your entire queue or just the items (inventory) from 12/9/2015?

But the inventory changes from day to day so some days only a few items can come in, but other days the number can be higher like 350, which would take, 7 days to work through.

When items come in today, are they given today as a create date?
Is the queue always processed from oldest create date- so 50 items are taken off today, they will all be from 12/9 or 12/10?

So if I only had 5 items in the oldest inventory today, and 45 for 12/10, I could get through today and tomorrow and the age would be 57 tomorrow.

Just to confirm I'm understanding your system...Your queue is at 59 days today (2/9/16) because the oldest item create date is 12/9/15. If you close all the 12/9 and 12/10 items today, that will drop the queue age by 2 days to 57 at the end of today, but tomorrow (2/10/16) the age will be 58?

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.

If the queue is always processed from oldest create date and you assume 50 items closed per day then your queue will be reduced by 50 items x N working days in a month.
If we assume 20 work days in a month, you'll close 1000 items in the queue. If the queue currently has more than 1000 items in it, then it would be fairly simple to calculate the oldest queue date after those 1000 items are removed and consequently the age of your queue will be in one month. The unknown number of new items wouldn't be a factor in that calculation unless you currently have less than 1000 items in your queue.

If you'd like some help with formulas to calculate that, then please post a small screenshot showing the layout of your data.

 

[Michaelm1122]

How do I add an image or attachment?

 

[Michaelm1122]

So the output look like this typically we run the report on Friday to show the age of the queue each day, let assume we are looking at next Monday.

Feb 15th - Monday 62 days, Tuesday, 63 days Wed, 63 days, Thursday 63 days Fri, 63 days,
Feb 22nd - Monday, 65 days, Tuesday, 4 days, Wed 63 days, Thursday 62 days Friday 63 days,



This is then used to chart what the queue will be for everyday looking a couple months into the future/

The manual part s that in order to update everyday I have to manually look at the number of items in the queue at the oldest and then use my assumption of how many we will close each day to figure out what the age will be on Tuesday, Wed, Thurs, Fri, etc. Example

Day 83
64 Items

 

[Michaelm1122]

Day 82- 258 Items, but I can only close 50 a day, however some days the queue may only be like 5, or 7 and some days there are none. So I am trying to figure out how to do this with a formula or something so I don't have to take the 64 from day 83 -50 leaves Leaves 24 for tomorrow which is now day 84 and add that to the next day which may be 5, or 200, so I may be able to move up a couple days or it may take me several days to get through that batch which will age everyday.

 

[Jerry Sullivan]

How do I add an image or attachment?

See the "Posting Aids" section at the bottom of this post: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


I think I understand what you are trying to accomplish. Seeing how your data is organized will help develop formulas that you can use without having to adapt.

 

[Michaelm1122]

That would be awesome!

 

[Jerry Sullivan]

Are you going to add an image or screenshot?