Return minimum increases between date ranges?

I'm working on a product availability flow. In the left column there's a set of sequential dates, and in the right-hand column there's a balance of available product. What I'm trying to do is write a formula or series of formulas that will reflect how many of that product is available on each given date.

Example.PNG

Let's say I get 4000 widgets in January, April, and July.

In May, I have an order for 7000, and in July I have an order for another 4000. I wan this sheet to show that I have 1000 widgets available for sale after the January shipment, but that's it.

Please see the attached spreadsheet for an example of what I'm trying to accomplish. If this can be done, I haven't found a way to do it yet.

Originally Posted by Kaper

I think you shall have 2 data columns (3 including date). In one coulmn you shall list how many widgets you got (will get) and in second received orders.

PS. try to use the same figures in description (in post) and in sample file. otherwise it's misleading.

Hi, sorry for the confusion. I've got a new example uploaded here.

There are three source columns:
Widgets on-hand - Our starting inventory is row 3. Each row below this subtracts orders and adds new units for each period. A negative number means we've over-sold for that period.
New product - New inventory coming to our site, available for sale
Orders - Customer orders for the product

The final yellow column is the number I'm trying to arrive at via formula. It displays the most that a customer can order for a given time period. In the first set of numbers (columns B-E), there are only 894 widgets available from 6a18 through 11b18 even though we have quite a lot of widgets on hand. This is because inventory we receive in 8a18 is reserved for orders in 10b18.

Hopefully this helps.

Example2.PNG

Originally Posted by CAntosh

Can you clarify the timeframes a bit? If we look at 6b18 in the post# 3, my understanding is that the expected result is 894 because there are 1833 on hand and it considers the next new product entry and the next TWO order entries: 1833+2325-64-3200=894. Why? Why does it include one future new product entry and two future order entries? Is there a rule about how far into the future data should be incorporated into the "Available for sales" calculation? Should only the next "X" number of future data be considered? Your table doesn't indicate when future reservations become known and enter the calculations.

Thanks for taking a look. The numbers in column B reflect a salable inventory of widgets over a period of time. The quantity of 736 in row 3 is how many widgets we have available right now. Orders and new inventory arrivals are grouped together by half-month periods, and the calculation is done here. I call this batch of numbers an inventory flow. This is used to plot out the next eighteen months of sales and orders. In the real-world workbook where this graph lives, there are separate worksheets to track current inventory, orders, and incoming production.

The reason why I want to show 894 from 6a18 through 11b18 is because even though we have additional inventory on-hand, it has to be reserved for orders that come later on. This becomes easier to see when looking at the data in line-graph form:
WidgetGraph.PNG

Even though I have 4,094 widgets on hand from 8a18 through 10a18, that entire quantity isn't available for sale. If I were to take an order for 4,00 widgets in September, that would mean I'm oversold for the order of 3,200 in October. Similarly, I only have 3,844 to sell between 12a18 and 6b19. If I took an order for 5,000 in December, then I'm oversold when the March order comes in.

I'm beginning to think that I need to use a series of cells to manually indicate which periods the spreadsheet needs to calculate. If I type in a value that corresponds to 6a18, 12a18, and 7a19, I can probably get the calculations to work out.... I was just hoping there might have been another way of doing this.

Here's another version of the spreadsheet with live formulas for inventory, orders, and new product arrival.

I think I understand what you're getting at, and all I can say is that we don't necessarily place holds on inventory in the same sequence as the orders that come in.

To say it another way, all of our inventory goes into a giant bucket of widgets available for sale. Once the widgets get here, there's no differentiation between the 1,737 that get here 6a or the 2,325 that arrive 8a. Since that's the case, the widgets used to fulfill the 10b order for 3,200 may come from both the 6a and 8a batches.

The order for 1,250 is being factored in, but due to the sequence in which orders leave / new inventory arrives it would be subtracted from the batch of 4,200 widgets that come onsite in 12a.

If you have the spreadsheet open, try putting in order for 850 widgets to leave 8b. Some of the numbers in column B will be reduced by 850, but nothing will go negative. This is acceptable, even though the new order you just entered will ship before the 3,200 in 10b. The math here reserves enough inventory to cover later orders. That 3a order for 1,250 you refer to is still fine because a new batch of widgets is arriving in December that will cover that order.

This is why we internally refer to it as a "flow", because we're not selling 1:1 against a batch of widgets. We're selling against the sum of all the widgets we have at given date periods.

Originally Posted by CAntosh

EDIT: I definitely overthought this. Even simpler, and in a non-array formula:

=MAX(MIN(B4:B$39),0)

Wow. I never realized something like this was possible. It'll take me a minute to sit down and work through the logic of this formula.

I don't exaggerate when I tell you this will save me a few hours every week after I get it implemented properly. Thanks!