Scenario 100 Planning Spreadsheet: Aggregate Production Planning

Scenario 100 Planning Spreadsheet: Aggregate Production Planning

This document outlines the usage and strategic implications of the Scenario 100 Planning Spreadsheet, a critical tool for developing an Aggregate Production Plan to effectively execute the simulation.

Core Objective and Purpose

The primary goal of the spreadsheet is to facilitate monthly production decisions aimed at meeting as much customer demand as possible, while simultaneously maximizing the company's Earnings Before Tax (EBT). The spreadsheet provides a structured environment to plan production for different beer types, considering their profitability and market demand.

Spreadsheet Structure and Interface

To aid in plan development, the spreadsheet is color-coded:

• Yellow Cells: These represent inputs to the problem given to the user. Key inputs include:

  • Selling price of the beer.

  • Cost per case for the beer.

  • Beginning inventory in January.

  • A forecast of demand. It's crucial to understand that this forecast is a reasonable estimate, but not $100\%$ accurate.

• Light Blue Cells: These denote the decision variables that the user directly controls. Specifically, these are the monthly production quantities for Taj Mahal beer.

Beer Products and Production Allocation

The simulation features three beer products, each with a different gross profit margin, influencing scheduling priority:

• Taj Mahal Beer:

  • Has the highest gross profit, making it the most critical beer to schedule to avoid stock-outs.

  • Production quantities are directly inputted by the user in the light blue cells.

• Alicat Beer:

  • Possesses the second highest gross margin.

  • Production quantities are also directly inputted by the user.

• Edgar Ale:

  • Unlike Taj Mahal and Alicat, the production quantities for Edgar Ale are not directly inputted.

  • Its production is derived based on available brewery capacity.

  • The calculation is: Monthly Production Capacity (in cases) - Planned Production of Taj Mahal - Planned Production of Alicat.

  • This approach simplifies the decision-making process by reducing the number of variables from $36$ to $24$. This is a significant reduction in complexity.

Feasibility Constraint for Edgar Ale Production

If the combined planned production of Taj Mahal and Alicat exceeds the available monthly brewery capacity, the calculated production quantity for Edgar Ale will become negative.

• In Excel, this will be highlighted in red due to conditional formatting.

• A negative production quantity signifies an unfeasible schedule.

• Users must adjust the production values of Taj Mahal and Alicat to ensure that Edgar Ale's production quantity is never negative.

Earnings Before Tax (EBT) and Lost Sales

• Default EBT: The initial default plan for Taj Mahal beer yields an EBT of $90,335$.

• Primary Goal: The overarching objective is to maximize the company's EBT. Any changes made to the production plan should ideally result in an increase in this value.

• Lost Sales: The spreadsheet also reports the expected number of lost sales for each product. This metric is crucial for identifying areas where demand is not being met.

Demonstrating Plan Improvement: An Example Scenario

The default plan often presents opportunities for improvement. Consider the following example:

1. Initial Observation - Alicat Lost Sales: The default plan frequently shows a large number of lost sales for Alicat Ale.

   • Strategic Implication: Since Alicat Ale has a higher gross margin than Edgar Ale, a strategic shift is to prioritize Alicat production to minimize its lost sales.

2. Initial Observation - Taj Mahal Inventory: The default plan often mandates producing $4,000$ cases of Taj Mahal each month. This typically results in monthly ending inventory levels that are unnecessarily large for Taj Mahal.

   • Strategic Implication: Excessive inventory ties up resources without providing a proportional benefit. Reducing inventory levels can free up capacity and resources for more profitable uses.

Step-by-Step Improvement Example:

• Action 1: Reduce Taj Mahal Production:

  • Given the high inventory, the first step is to lower Taj Mahal's production quantities to more reasonable levels that still protect against stock-outs but optimize resource use.

  • Example: Reducing monthly Taj Mahal production from $4,000$ cases to $3,000$ cases for January through July.

  • Result: This adjustment brings the inventory down to a low of $1,698$ cases in July, which is considered sufficient to avoid stock-outs if closely monitored during simulation execution.

  • Impact on EBT: These changes increased the plan's EBT from the default $90,335$ to a significantly improved $126,455$.

• Action 2: Increase Alicat Ale Production:

  • The reduction in Taj Mahal production frees up capacity, allowing for an increase in Alicat Ale production, which helps address its seasonal demand and higher gross margin.

  • Example: Increasing Alicat Ale production to $7,900$ cases in January.

  • Result: This change will decrease lost sales for Alicat but might increase lost sales for Edgar Ale.

  • Rationale: Since Alicat has a higher gross margin than Edgar Ale, this trade-off is often strategically beneficial.

  • Impact on EBT: This specific change, prioritizing Alicat over Edgar, increased the EBT by over $700$.

Trial and Error and Team Collaboration

• Individual Task: Each team member is required to individually develop their own plan using trial and error to adjust the production quantities for Taj Mahal and Alicat Ale.

  • This exercise is crucial for developing an understanding of how the spreadsheet functions and how EBT responds to changes in the production plan.

  • It is important to understand the underlying mechanics, as individual results from previous attempts may not be easily recalled later.

• Team Task: After individual efforts, the team should collaborate to consolidate insights and develop a comprehensive plan where the EBT exceeds the EBT of each team member's individual plan.

• Log Sheet: The simulation includes a log sheet page that records every entry made into the spreadsheet. This page will be checked during grading.

  • A log sheet with $24$ or fewer entries indicates that trial and error was not adequately used to generate the results.

• Regular Saving: Users are strongly advised to regularly save their results to prevent the loss of good plans, especially when contemplating changes that might inadvertently decrease EBT.

Utilizing the Best Plan for Simulation Execution

The