8 Replies Latest reply on May 15, 2018 10:32 AM by Zach DeMascole

# Table with Inventory Balances at Points in Time

Hello everyone,

With your help, some of the things I have been able to compute are:

- Month/Year of the most recent actuals

- Month/Year of the second most recent actuals

- Month/Year of the prior year-end actuals

I now am trying to calculate:

- Ending inventory balance for the Month/Year of the most recent actuals

- Ending inventory balance for the Month/Year of the second most recent actuals

- Ending inventory balance for the Month/Year of the prior year-end actuals

Does anyone have any ideas how to do this?

My sample data set has actuals from November 2017 through April 2018 and forecast data for the future.

What I am trying to get is a table like this (based on the sample data):

TimeInventory Balance
Most Recent Actuals\$1,500
Second Most Recent Actuals\$1,800
Prior Year-End Actuals\$600

I thought it would be easy, but I am struggling.

I have attached a workbook that includes the formulas I have for computing Month/Year of most recent actuals, second most recent actuals, and prior year-end actuals.

I have also attached the sample data.

Thanks for all of your help!!

• ###### 1. Re: Table with Inventory Balances at Points in Time

Jim Van Sistine

Hey Jim - any ideas what I am running into?

• ###### 2. Re: Table with Inventory Balances at Points in Time

Hi Zach

see attached

you were right there just need to add some calculations

the formulas are

and

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 3. Re: Table with Inventory Balances at Points in Time

Zach DeMascole

Yes, what Jim did above!

• ###### 4. Re: Table with Inventory Balances at Points in Time

Jim,

Thank you so much!

I am now trying to calculate the change from prior month-end and change from prior year-end, but my attempt did not work.

I tried creating the following calculating fields and adding them as additional columns:

(Inventory on Max Date) - (Inventory on max date zz second most recent) = Month Change

(Inventory on Max Date) - (Inventory on max date zz prior) = YTD change

Is this not possible or is there a solution?

Thanks,

Zach

I have attached the workbook you used in which I attempted to add delta columns.

• ###### 5. Re: Table with Inventory Balances at Points in Time

Zach DeMascole

Since you haven't specified an ELSE value in your initial calcs (totally fine), you'll need to put some null handling in your delta ones.

ZN([inventory on max date])-ZN([inventory on max date zz second most recetn])

That forces a null result to be 0 and then it should calculate correctly.

Alternative would be to put ELSE 0 into your inventory calcs.

IF [Date]=[zzz Prior Year End Actuals] then [Inventory] ELSE 0 end

Either of these should work for what you want to do.

- Jim

• ###### 6. Re: Table with Inventory Balances at Points in Time

Thanks Jim!

That worked perfectly.

One last question:

Is there a way to stack the Delta from Prior Year-End label and value on top of the Delta from Prior Month-End label and value (like below) instead of label beside the value?

Attached the file as well!

Delta from Prior Year-End
900
Delta from Prior Month-End
-300
• ###### 7. Re: Table with Inventory Balances at Points in Time

Could you just do it all as text and hard code the labels you want?

1 of 1 people found this helpful
• ###### 8. Re: Table with Inventory Balances at Points in Time

Thanks Jim!

This worked perfectly!