Juice Company Model RRS feed

  • Question

  • I am trying to build a model for a client who makes have a few outlets where they make instant juices (by mixing various ingredients with water). Each outlet is given 100KG of each raw material while each particular juice item has a unique recipe which is uniformally followed. Each outlet at the day end submits two type of information: how many and which juices were sold in the day, which and how much raw materials were used in the day.

    Based on the information available I have made the following model. Please help me adjust the model to come up with the following analysis:

    1. Outlet wise, items sold (Product Name/Qty)
    2. Outlet wise, budget raw materials consumption (i.e. how much raw materials should have been consumed as per the recipe cards in order to produce the juice products actually sold)
    3. Outlet wise, actual raw materials consumption (Raw Material Name/Qty)
    4. Outlet wise, variance in consumption of raw materials (i.e. the difference between actual raw materials consumption and budget raw materials consumption)
    5. Outlet wise, percentage variance in consumption of raw materials
    6. Outlet wise, costly variance analysis (EXPLAINED LATER)
    7. Outlet wise, inventory management (opening, utilized, closing) of all the raw materials
    8. Outlet wise, revenue earned (I will adjust it for the dates later myself)
    9. Outlet wise, revenue earned to raw material cost analysis
    10. Outlet wise, gross profit analysis (revenue-raw materials cost)

    Costly Variance Analysis: Two outlets might be make the same kind of mistakes resulting in same variance %. But there might be a difference in the raw materials consumed due to the difference in products sold at each outlet. Since each raw material is priced differently, any one outlet will be making more loss as compared to the other outlet making the same variance %.

    Please feel free to download(https://ufile.io/sfrbf)my model and play around with it.


    Monday, July 24, 2017 11:44 AM


  • Hi Asimsidd, please take a look at my reply to your other post. This will lay a solid foundation to your analysis needs.
    Monday, July 31, 2017 9:45 AM
  • Hi,

    That download link does not work.  Can't most of the questions stated above be resolved by using Pivot Tables (via the Data model/DAX formulas)?  The best way to move forward would be to highlight which of the questions above are you not able to resolved with DAX formulas.  

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, September 2, 2017 1:41 AM

All replies