How to control quarter end inventory number?

Shomei Shin
Shomei Shin Active Partner
Second Anniversary First Comment
edited April 2020 in Platform

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.

 

image

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.

 

  image

 

the result turn out to be as below,

image

 

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

image

Answers

  • Alexander Kappes
    Alexander Kappes Employee
    Fourth Anniversary 250 Likes 100 Comments 5 Answers

    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
    Shomei Shin Active Partner
    Second Anniversary First Comment

    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
    Alexander Kappes Employee
    Fourth Anniversary 250 Likes 100 Comments 5 Answers

    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
    Shomei Shin Active Partner
    Second Anniversary First Comment

    Hi, Kappes,

    Thanks for your comment. Understand.

    I will try as you advised.

     

    Thanks a lot for your help.

    Thanks,

    Shomei

Categories