Age of Queue each day based on 1 assumption

Michaelm1122

New Member
Joined
Aug 12, 2014
Messages
12
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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?
 
Last edited:

Michaelm1122

New Member
Joined
Aug 12, 2014
Messages
12
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

New Member
Joined
Aug 12, 2014
Messages
12
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

New Member
Joined
Aug 12, 2014
Messages
12
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.
 

Forum statistics

Threads
1,084,743
Messages
5,379,565
Members
401,613
Latest member
KarimK9

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top