Stage 3 (2023) “Untapped Potential”

Case description:

You are the new finance manager at Jar Jar Drinks, a new brewery based in Toronto, Canada. The brewmaster and owner is quite a decent modeller himself, but unfortunately no longer has the time to run the brewery’s financial model (as he is busy running the brewery) and therefore has hired you to take over.

He has asked you to assist with the demand forecasting and brewing optimisation of the brewery, but before he trusts you, he has also set you a variety of data management questions to do with the lifetime history of the beers he has drank before.

Part 1 – You have to analyze the given beer data and answer various questions related to the brewmaster’s beer consumption history. You need to work with the provided spreadsheet, which includes information such as beer name, brewery name, beer type, alcohol by volume (ABV%), rating scores, brewery location, and global rating scores. The challenge is to accurately analyze the data and derive insights from it.

Part 2 – You have to forecast the demand for Jar Jar Drinks’ beers throughout the year. The demand for different beer styles varies seasonally, and you need to use the brewmaster’s assumptions about the demand changes to calculate the demand in the brewpub for each beer. The challenge is to make efficient production decisions based on the available tanks, fulfill contracts, maintain a good selection for the brewpub, and consider revenue opportunities from additional production and discounted sales.

This is an actual case study that was presented to the participants of the Financial Modeling World Cup 2023, Stage 3 (April 21-24).

The downloadable file consists of the following:
–Task: PDF file with Case Materials; PDF file with Questions; Excel file with Assumptions
–Solution: Excel file with full Solution Model; PDF file with correct Answers in Bold