How to control quarter end inventory number?

Shomei Shin

Hi, This is Shomei Shin from Zeal Japan.

I am working on a solution development for customers.

I am encountering one issue that the behavior is not my hope.

Could expert solve this problem?

Thanks in advance who evolve this discussion.

The details are shown as below.

1) Data View is for planning on PSI( Production/Sales/Inventory), the planning is by monthly.

the first line is sales actual, the second line is for sales forecast, the third line is for production plan, then

the fourth line is for Month end inventory, that is calculated from last month end inventory and current month sales/production forecast numbers.

Current Month end Inventory = last month end Inventory - current month sales forecast + current month production plan

2) Quarter based PSI

We also need to show quarter/year based inventory. Quarter based Inventory means quarter end inventory.

As per above, Q1 inventory should be the month end inventory of 19/6, but when I add quarter into column axis.

the result turn out to be as below,

Q1 inventory become summary of the months in the quarter, that is no meaning, need show the quarter end inv, so

I want to set 1st quarter as inv of 19/6 (-7745, not -15502).

Could you help me about how to set Q1 (inv) to inventory of June (the month of quarter end)?

I used NEXCEL to calculate month end inventory, if I reuse N####excel to set quarter numbers, the new formula will replace

the old one formula.

inventory calculation formula

Find more posts tagged with

Accepted answers

All comments

Alexander Kappes

Dear shomei shin,

thanks for your request.

I think the easiest solution for you will be using little Matrix Cube.

Inside this you define your Quarter End Months. Just fill this Matrix for the wihsed Month with value 1.

Afterwards you can use this Cube inside your Layout and multiply with the figures cube, all other Months wouldn´t been shown because they are multiplied by zero.

Finally you can change your Axis. My suggestion will be using no column axis. Instead this you can also use Detail By. Using this you have the opportunity to detail the first cube by month and the Quarter based cube by Quarter.

Hope it helps

regards

Alexander Kappes

Shomei Shin

Dear Kappes,

thanks for your quick reply.

I am sure I truly understand all things.

I am working on V10.5.1, and using Board Client.

Do you mean the matrix cube is the cube you called "Quarter End Months",

that need customize "time Range", right?

the cube is looking like 4,5,6, Q1,7,8,9,Q2,10,11,12,Q3,1,2,3,Q4?

Do you have an example to show?

thanks,

Shomei

Alexander Kappes

Dear shomei shin,

sorry I don´t really have some sample.

Inside the matrix you mark the months which should be shown in the quarterly view. The trick is just done multiplying the figures cube with the matrix, that you only see the figures you need for your analysis.

regards

Alexander Kappes

Shomei Shin

Hi, Kappes,

Thanks for your comment. Understand.

I will try as you advised.

Thanks a lot for your help.

Thanks,

Shomei