Sprint 3 INV04

Hi, Could someone please help me with the module below?

 

I am struggling with the Percentage Full result that I get and I think it is because I am doing something wrong in the Beginning Inventory line.

I am pulling BI from DAT01 which gives a straight line across all months. What am I doing wrong?

I also could use a suggestion for the conditional formatting of the % Full line.

 

Lkishko_1-1590167357438.png

Lkishko_2-1590167458383.png

Thanks for the help!

Comments

  • Hi @Lkishko 

    Ok I see.

    Let's think about this from a business perspective. We bought a business and we had some inventory included in the sale.

    So at the end of time period 0, we had that specific inventory. We are "Given" that value, we don't calculate it.

    Then at the beginning of the month after, the business sold some items from the inventory and purchased some more so at the beginning of time period 1 we have another "beginning" inventory for the month that we can calculate. 
    Beginning Inventory at current period = Ending Inventory at previous period + any inventory we added - any inventory we sold.

     

    So, you have 2 types of beginning inventory, One is the very first one that you can't calculate and is given as an input for the system.

    and another beginning inventory at the beginning of each month. This one you calculate based on what you sold and what you purchased.

    In the example, the Inventory in DAT01 is the absolute beginning inventory (input)

    The beginning Inventory you calculated in INV01 is your "monthly" beginning inventory.

    Which one do you think is more appropriate when you are trying to calculate a monthly percentage?
    It is INV01 inventory.

    I know I could have just said change the inventory from DAT01 to INV01. That might have solved the issue now but it doesn't explain the thinking process and I believe we should use these model-building activities to learn.

     

    If after you adjusted the inventory you still have questions please let me know.

  • @einas.ibrahim Thank you, that is helpful to think through and I was able to figure that part out actually, but I am still struggling with getting the correct output. It seems like I use the INV01 Module for Beginning Inventory and sum based on distribution center. 

    There might be another variable I am missing because later when I check the Distribution Center Summary Dashboard, my "Percentage Full" outcomes are wrong. 

     

    Lkishko_0-1590177933381.png

    Lkishko_1-1590178036194.png

    For Percentage full I am using Total Beginning Inventory / Distribution Center Capacity.

     

    Please let me know if you can see my mistakes here.

     

     

     

     

  • @Lkishko 

    You and me both buddy 😀
    I don't think these dashboard grids have the right numbers. I just posted a question about that here.

    So let's see what the community or academy will say. I'll keep you posted.

  • Hi @einas.ibrahim ,

     

    Can you please guide me with Formula used to get Beginning Inventory in INV04 as i am getting very weird numbers in the result.

     

    I have used INV01 module as source based on sum from SYS08 Supplied by.

     

    Is this correct or i am missing somethings and need to rework the formula.

     

    gauravj6_0-1596998257107.png

    Thanks and Regards

    Gaurav

  • Hello @gauravj6 

    I know what you mean by weird numbers, I faced the same issue too and I believe it's because of the initial data provided.

    The way you have formed the formula is correct.

    So keep on 🙂

     

  • Thank you for the feedback.

    Regards
    Gaurav
  • Hi @einas.ibrahim 

     

    I have seen the Screenshots provided by @Lkishko  & @gauravj6  both used correct formula and which is same as mine as well but both have a different value for total inventory line item and mine values for total inventory is also different from them, so how can we validate our values, since the review file do not hold correct values.

     

    kunal_311_1-1597352822458.png

     

     

  • Hi @kunal_311 

    I'm not sure about the screenshots for others you mentioned so I can't speak to that. I do recall though getting crazy numbers for percentage full as well.
    It depends on what values we change - such as forecast override or placing a purchase order with different quantities and shipping methods.

    I am not even sure that reloading the files given throughout the training will fix the issue. The good news is, and for that particular reason - you get clean files in the exam to ensure we all have the same data.

    Go ahead, you are so close 😊 

  • @einas.ibrahim 

     

    Hi,

     

    I am stuck in making this formula as well. I have been using the INV01 as the beginning inventory, but when i do a SUM of the SYS08 Distribution Center module within the % Full line item, i get percentages that are over 100,000%. Any ideas or advice on how to change the formula so I get the right outputs?

  • Hi @ifeldman 

    Please re-read my last response on the issue of "crazy %".

     

    Regarding this phrase in your post ==> "but when i do a SUM of the SYS08 Distribution Center module within the % Full line item, i get percentages that are over 100,000%."

     

    IF I understood you correctly, then please keep the following in mind

    • You should get the beginning Inventory from INV01 for all the SKUs in a particular distribution center
    • Then get the capacity of each distribution center
    • Finally divide these 2 numbers,. You don't need to do SUM again for the % Full Line Item
  • Hi @einas.ibrahim,

     

     I think I am running into the same issue here with my Total Beginning Inventory making my Percentage full over 100% capacity. See screenshot below.

    Dpsullivan1_0-1623189451835.png

     

    I feel like the issue is my Total Beginning Inventory in INV04 Distribution Center Capacity is taking all 4 weeks of January for example and summing the beginning inventories which I don't want. Like you said in your earlier posts, inventory goes up and down, but the beginning inventory in each week shouldn't be summed together and I feel like my INV04 is doing this because it's time setting in monthly.. I passed the level 2 sprint 3 exam and I'm pretty sure it said my formula was correct.. so I'm not sure why I can't get the correct values to show. Any ideas what I'm missing here? Appreciate the help, I've been stuck on this for some time.. 

  • Hello @dsulliv8 ,

    Thank you for your question. It's been a long time since I worked with the L2 model. You are correct the beginning inventory for the month, like January, should not be the sum of the 4 weeks in the month. The beginning inventory of a month should equal the beginning inventory of the first week of the month.

    You should look at the Time Summary for beginning inventory, It shouldn't be SUM, If I recall correctly, it should be the "Opening Balance" Look into this and let me know if you still have questions.

  • Hi @einas.ibrahim,

     

     Thank you so much for the help, it works now!