Excel formula or Custom Function to be written

  • Posted:
  • Proposals: 1
  • Remote
  • #2584011
  • Completed
Gadi B. has already sent a proposal.
  • 0


Experience Level: Entry
I am looking for a formula to be written, or custom function, in Excel, to enable a simple Inventory Life calculation, in days, based on FIFO methodology. The attached example show the calculation, with “staged” formula in two examples:

Example 1: With 240,000 units purchased in Feb 2020, and sold over 6 months from April 2020, the Inventory life starts at 7 months (214 days), and reduces down.

Example 2: The same as above, except after two months, an additional 240,000 units are purchased in Apr 2020, so the Inventory life continues to reduce for the first 240,000, but extends for the second 240,000:
• First 240,000 sold over six months, with an initial delay->Sep 2020
• Second 240,000 sold over six months, but not until the first 240,000 are all sold->Mar 2021

I am looking for a one line formula, in each example in rows 8 and 20 that will mimic the existing staged formula and be dynamic for changes to the timing etc..

I am unable to fathom this and wonder if this is not possible without a wider sheet/table to work with (which to be clear I do not). This must be done with the cell, with or without a VBA CF.

Happy to answer any questions - payment when completed.


New Proposal

Create an account now and send a proposal now to get this project.

Sign up

Clarification Board Ask a Question

    There are no clarification messages.