Another Excel question (1 Viewer)

[NajehHejan]

I tried to find the Excel thread, but to no avail. Ok to merge with that if the mods can find (no luck with search function).

Is there a formula to show when you will run out of something?

For example, you have 100,000 widgets inventory on hand. You have a 10 week demand forecast from the customer, broken down into weekly buckets. So say A3 is the inventory, and the weekly demand buckets are B3 through K3. What formula could I put into L3 to tell me when I go negative or to zero?

TIA.

 

[odin33]

Easiest way to see this given your example is to simply run a total underneath, or QOH

So A3 is the 100k. B3 is month 1 forecast, B4 should read =A3-B3

Cell C4 should read =B4-C3. Once this one is entered you can copy and paste it into C4... the end.

 

[NajehHejan]

Easiest way to see this given your example is to simply run a total underneath, or QOH

So A3 is the 100k. B3 is month 1 forecast, B4 should read =A3-B3

Cell C4 should read =B4-C3. Once this one is entered you can copy and paste it into C4... the end.

Yes, that would work, but I’m hoping there is a way to do this without adding an extra line. I’m hoping for a formula I can put at the end of the row. My conundrum is I am working with dozens of items on a single spreadsheet. Each unique widget has its own row, but I don’t want to have to add another row for each item.

 

[Brony]

I would do a whole new set of columns with weekly ending inventory. But rather than just taking the exact weekly inventory, I'd do =MAX(0,Previous Week Inventory - Forecast). That will give me 0's in all the weeks where inventory is negative.

Then in a new column to the right, I'd do =MATCH(0,$N3:$X3,0) and that would give me column # with the first week of zero or negative inventory.

 

[Donkey Derp]

I tried to find the Excel thread, but to no avail. Ok to merge with that if the mods can find (no luck with search function).

Is there a formula to show when you will run out of something?

For example, you have 100,000 widgets inventory on hand. You have a 10 week demand forecast from the customer, broken down into weekly buckets. So say A3 is the inventory, and the weekly demand buckets are B3 through K3. What formula could I put into L3 to tell me when I go negative or to zero?

TIA.

If you only have a few weeks paste into L3:

=IF((A3-B3)<=0,"WEEK 1", IF((A3-sum(B3:C3))<=0,"WEEK 2",IF((A3-SUM(B33))<=0,"WEEK 3",IF((A3-SUM(B3:E3))<=0,"WEEK 4",IF((A3-SUM(B3:F3))<=0,"WEEK 5",IF((A3-SUM(B3:G3))<=0,"WEEK 6",IF((A3-SUM(B3:H3))<=0,"WEEK 7", IF((A3-sum(B3:I3))<=0,"WEEK 8",IF((A3-sum(B3:J3))<=0,"WEEK 9",IF((A3-SUM(B3:K3))<=0,"WEEK 10","DOESN'T RUN OUT"))))))))))

not elegant for 1 minute of thinking, but should work. If you are forecasting out many more weeks, a macro would be more appropriate, but for 10 wks this is fine. Check my ( ), on phone.

eta: you can sub "WEEK 1" with cells of specific week dates if you need of course

 

[NajehHejan]

I would do a whole new set of columns with weekly ending inventory. But rather than just taking the exact weekly inventory, I'd do =MAX(0,Previous Week Inventory - Forecast). That will give me 0's in all the weeks where inventory is negative.

Then in a new column to the right, I'd do =MATCH(0,$N3:$X3,0) and that would give me column # with the first week of zero or negative inventory.

Worked great – thanks!

 

[NajehHejan]

I tried to find the Excel thread, but to no avail. Ok to merge with that if the mods can find (no luck with search function).

Is there a formula to show when you will run out of something?

For example, you have 100,000 widgets inventory on hand. You have a 10 week demand forecast from the customer, broken down into weekly buckets. So say A3 is the inventory, and the weekly demand buckets are B3 through K3. What formula could I put into L3 to tell me when I go negative or to zero?

TIA.

If you only have a few weeks paste into L3:

=IF((A3-B3)<=0,"WEEK 1", IF((A3-sum(B3:C3))<=0,"WEEK 2",IF((A3-SUM(B33))<=0,"WEEK 3",IF((A3-SUM(B3:E3))<=0,"WEEK 4",IF((A3-SUM(B3:F3))<=0,"WEEK 5",IF((A3-SUM(B3:G3))<=0,"WEEK 6",IF((A3-SUM(B3:H3))<=0,"WEEK 7", IF((A3-sum(B3:I3))<=0,"WEEK 8",IF((A3-sum(B3:J3))<=0,"WEEK 9",IF((A3-SUM(B3:K3))<=0,"WEEK 10","DOESN'T RUN OUT"))))))))))

not elegant for 1 minute of thinking, but should work. If you are forecasting out many more weeks, a macro would be more appropriate, but for 10 wks this is fine. Check my ( ), on phone.

eta: you can sub "WEEK 1" with cells of specific week dates if you need of course

I tried this one too but my Excel said too many “if” statements for my type of document. I was trying Excel 2013 – not sure why it didn’t allow me to do it. But thanks for the effort!

 

[Donkey Derp]

I tried to find the Excel thread, but to no avail. Ok to merge with that if the mods can find (no luck with search function).

Is there a formula to show when you will run out of something?

For example, you have 100,000 widgets inventory on hand. You have a 10 week demand forecast from the customer, broken down into weekly buckets. So say A3 is the inventory, and the weekly demand buckets are B3 through K3. What formula could I put into L3 to tell me when I go negative or to zero?

TIA.

If you only have a few weeks paste into L3:

=IF((A3-B3)<=0,"WEEK 1", IF((A3-sum(B3:C3))<=0,"WEEK 2",IF((A3-SUM(B33))<=0,"WEEK 3",IF((A3-SUM(B3:E3))<=0,"WEEK 4",IF((A3-SUM(B3:F3))<=0,"WEEK 5",IF((A3-SUM(B3:G3))<=0,"WEEK 6",IF((A3-SUM(B3:H3))<=0,"WEEK 7", IF((A3-sum(B3:I3))<=0,"WEEK 8",IF((A3-sum(B3:J3))<=0,"WEEK 9",IF((A3-SUM(B3:K3))<=0,"WEEK 10","DOESN'T RUN OUT"))))))))))

not elegant for 1 minute of thinking, but should work. If you are forecasting out many more weeks, a macro would be more appropriate, but for 10 wks this is fine. Check my ( ), on phone.

eta: you can sub "WEEK 1" with cells of specific week dates if you need of course

I tried this one too but my Excel said too many “if” statements for my type of document. I was trying Excel 2013 – not sure why it didn’t allow me to do it. But thanks for the effort!

That shouldn't be a problem in newer versions of Excel - they can hold up to 64 IF statements if I recall correctly. Did you copy it straight from FBG and paste in the while formula box (the white box to the left of the fx in bold)? I copied it into an Excel spreadsheet and worked so not sure the issue with yours.

 

[odin33]

I would do a whole new set of columns with weekly ending inventory. But rather than just taking the exact weekly inventory, I'd do =MAX(0,Previous Week Inventory - Forecast). That will give me 0's in all the weeks where inventory is negative.

Then in a new column to the right, I'd do =MATCH(0,$N3:$X3,0) and that would give me column # with the first week of zero or negative inventory.

I am so ripping this off for use on a rainy day.

Thanks.

 

[bcdjr1]

Now if you do conditional formatting so that when MATCH(0,$N3:$X3,0)=COLUMN(), you can get Excel to highlight the column where you start to get negative inventory.

 

[NajehHejan]

Now if you do conditional formatting so that when MATCH(0,$N3:$X3,0)=COLUMN(), you can get Excel to highlight the column where you start to get negative inventory.

Holy crap that would be awesome – can you elaborate on how I would do this?

 

[Donkey Derp]

Now if you do conditional formatting so that when MATCH(0,$N3:$X3,0)=COLUMN(), you can get Excel to highlight the column where you start to get negative inventory.

Holy crap that would be awesome – can you elaborate on how I would do this?

Click on B3 (a cell with inventory). Go to home->under styles there is "Conditional Formatting"->Manage Rules

Click "New Rule"

Click "Use a formula to determine which cells to format"

Click on the small looking Excel icon with red arrow (under "Format values where this formula is true)

In the white box, type =IF(COLUMN()=$Y3,TRUE,FALSE) [make sure there are no dollar signs in front of the 3]

Click the small looking Excel icon with down arrow

Click Format...

Go to "Fill", choose whatever color you want

Click "Ok"

Click copy on cell B3

Highlight rest of inventory weeks C3 through K3, Home->Paste->Paste Special->Formats (I assume each cell looks the same)

**$Y3 is whatever cell you put El Floppo's formula in

 

[El Floppo]

wait... I've got my own formula? nice.

 

[tri-man 47]

wait... I've got my own formula? nice.

Mmm, you're more of a tool than a formula.

JK, good buddy.

 

[ShaHBucks]

Now if you do conditional formatting so that when MATCH(0,$N3:$X3,0)=COLUMN(), you can get Excel to highlight the column where you start to get negative inventory.

Holy crap that would be awesome – can you elaborate on how I would do this?

Click on B3 (a cell with inventory). Go to home->under styles there is "Conditional Formatting"->Manage RulesClick "New Rule"

Click "Use a formula to determine which cells to format"

Click on the small looking Excel icon with red arrow (under "Format values where this formula is true)

In the white box, type =IF(COLUMN()=$Y3,TRUE,FALSE) [make sure there are no dollar signs in front of the 3]

Click the small looking Excel icon with down arrow

Click Format...

Go to "Fill", choose whatever color you want

Click "Ok"

Click copy on cell B3

Highlight rest of inventory weeks C3 through K3, Home->Paste->Paste Special->Formats (I assume each cell looks the same)

**$Y3 is whatever cell you put El Floppo's formula in

2nd.. Or 3rd

 

[sbonomo]

Now if you do conditional formatting so that when MATCH(0,$N3:$X3,0)=COLUMN(), you can get Excel to highlight the column where you start to get negative inventory.

Holy crap that would be awesome can you elaborate on how I would do this?

use the conditional formating function with his formula. Very easy logic statement.

 

[NajehHejan]

Now if you do conditional formatting so that when MATCH(0,$N3:$X3,0)=COLUMN(), you can get Excel to highlight the column where you start to get negative inventory.

Holy crap that would be awesome – can you elaborate on how I would do this?

Click on B3 (a cell with inventory). Go to home->under styles there is "Conditional Formatting"->Manage Rules

Click "New Rule"

Click "Use a formula to determine which cells to format"

Click on the small looking Excel icon with red arrow (under "Format values where this formula is true)

In the white box, type =IF(COLUMN()=$Y3,TRUE,FALSE) [make sure there are no dollar signs in front of the 3]

Click the small looking Excel icon with down arrow

Click Format...

Go to "Fill", choose whatever color you want

Click "Ok"

Click copy on cell B3

Highlight rest of inventory weeks C3 through K3, Home->Paste->Paste Special->Formats (I assume each cell looks the same)

**$Y3 is whatever cell you put El Floppo's formula in

WOW - THANKS!