Calculating Backlog Days

I'm hoping someone can shed some light on an alternate (more accurate?) way to calculate Backlog Days (BL)...

I have daily sales, daily production capacity, and resulting daily backlog. The backlog days is currently calculated as a snapshot in time based on (total end of day backlog) / (total available production). So if daily production is 500 units, day 1 sales are 1,000 units, you'd have 1.0 days backlog (1,000 units - 500 produced = 500 EOD backlog. 500 EOD BL / 500 available capacity = 1.0).

But this seems not totally accurate to me as it's just looking at that specific day and not taking into account future sales/backlog.

See attached image for a simple representation of what I'm working with. Could a formula take into account future volume/BL?


Attachment 851590

Screen Shot 2023-12-04 at 3.05.44 PM.png

Welcome to the forum.

Forum Guideline #4:

As this is a free forum where members are not allowed to ask for payment, please don't offer it, as it is contrary to the community ethos of the forum.

I have, therefore, edited the offer of a reward out of your thread title.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Is your forum profile showing the Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

Waiting for you to share a workbook and update your forum profile, as requested.

Backlog Day Calcs.xlsx

Attached workbooks.

Still waiting for the profile update with your version of Excel.

Because 365 has a LOT more new functions than any other version, that's why! I wouldn't ask you to clarify this if it didn't matter.

I shall have a look at the workbook.

Seems as if you already have what you need in row 21 of the file attached to post #5.
If those amounts are not what you want, then it might help to attach another file to a new post in this thread that manually shows the amounts you want to see.
Let us know if you have any questions.