andrew@agcapitalcfo.com

Stage 5 (2021) “Supplier Waterfall”

Professional Excel Templates for your Business

Stage 5 (2021) "Supplier Waterfall"

This is an actual case from the Financial Modeling World Cup 2021, Stage 5 (May 21-24) competition. In this case, you must choose the optimal mix of suppliers for a project. Setting up the model the right way will determine the amount of time you spend answering the case questions.

Case description:

The objective of this case is to create a monthly financial model for a new business that will purchase and re-sell a single product from one of six different suppliers. The financial model will determine the cheapest suppliers to source products from to meet the first year of forecasted customer demand volumes. The model must ensure that the most affordable possible suppliers are used, considering volume rebates and the maximum order quantities. The orders placed with suppliers must meet the forecast demand, and products cannot be carried forward to be sold in future months.

The main challenge, in this case, is to identify the cheapest suppliers from which to source products to meet the forecasted customer demand volumes. This requires careful consideration of supplier costs, lead times, maximum order quantities, and volume rebates. The orders must be placed to meet the exact forecasted demand, without carrying forward products to future months. There may be insufficient suppliers to meet the customer demand due to the maximum order limits. Additionally, some of the assumptions change in questions 11 to 16, requiring careful attention to detail.

The downloadable file consists of the following:
-Task: Excel file with given case assumptions; PDF file with Case Materials; PDF file with Questions
-Solution: Excel file with full Solution Model; PDF file with correct Answers in Bold

This model awarded 550 points, while the whole 2h Stage allowed to earn 1,000 points in total.

For your questions/suggestions/consideration please reach out to the Organizing Committee of the Financial Modeling World Cup at info@fmworldcup.com
You can also buy tickets to participate in the future FMWC Stages at https://fmworldcup.com/shop/

 

 

Reviews

There are no reviews yet.

Be the first to review “Stage 5 (2021) “Supplier Waterfall””

Your email address will not be published. Required fields are marked *