Simple calculate open and closing balances

[#1]

Hi

Could someone explain the simplest method of calculating open and closing balances by Month for a product if we have the product # ,description and current stock in one table and a series of supply and demand records by due date in another table?

eg  ProductA

     Current-Stock    200

     March Demand   50

     March Supply     30        Need to calculate March closing Stock = 200 - 50 + 30 =  180

     April Demand    100

     April Supply        60       Need to calculate April Closing Stock = 180 - 100  + 60 = 140

     etc

I can produce a simple Pivot table with dimension Part and Month and expressions for the Demand and Supply columns but I don't know how to get the relative closing balance?

Many Tks

Dave

[CELAMBARASAN]

Hi,

     This should be done in load script then it will be easier.

     Can you tell me about the table structure?

Celambarasan

[#3]

Hi,

Please share sample data in Excel.

Then, it will become more easy and precise to respond.

[#4]

Tks Celambarasan

I’m a bit slow with handling this sort of thing

Product File has

Part#

Part-Description

Inventory-File has

Part#

Qty-On-Hand

Supply/Demand-MRP-File has

Part#

Due-Date

Supply/Demand-Indicator

Qty

As per below I can easily get the monthly Demand and Supply numbers but can’t get a running closing Balance

Does this help?

Regards

Dave

[#5]

Hi

Here is a sample of the output file with the problem highlighted in red

Regards

Dave

[CELAMBARASAN]

Hi,

     Check my understanding is correct?

     Qty-On-Hand is current stock which is the initial one.

     If you have data for more than one year(2009,2010,2011) then Qty-On-Hand is the stock during the start at 2009?

Celambarasan

[CELAMBARASAN]

Hi,

     Then try with this expression

     =Alt(Above([End-Bal]),0) + Supply - Demand

[End-Bal] is the Expression name of the End-Bal

Celambarasan

[#8]

Correct – Qty on-Hand is the current Inventory always maintained up to date

The Demand/supply records are generated by our MRP system and they will always be in the future

(The Year-Month generated table goes back into history for past movements (for a separate report on another tab) – I could generate a new Year-Month table just for the future periods if required

Tks

Dave

[#9]

Sorry Celambarasan – Still don’t get previous Closing balance reflected in next period closing balance – Am I doing something wrong?

Tks

Dave

[CELAMBARASAN]

Hi,

     Check by using supply and demand within [] square brackets or use the formula u used for Demand and supply.

     =Alt(Above([End-Bal]),0) + [Supply] - [Demand]

Celambarasan