SIOP Simulation Spreadsheet for Ajax Aluminum

Task:

Task:

Instructions This simulation asks the C580 team to take on the roles of Controller, Sales Manager, and Operations Manager for an aluminum production facility. The company, Ajax Aluminum, and some particulars of the case and simulation model are described in the document linked below. Team Roles Points of concern among the key players in this simulation include: Controller Has Ajax allocated costs to the product families correctly? Why does this matter? Sales Manager Should Ajax increase or decrease price, and if so, how might sales and profitability be impacted? Operations Manager How should Ajax spend its capital budget?

How should Ajax redeploy manufacturing shifts and/or schedule weekends? For this assignment, your team should execute a set of decisions and update the simulation spreadsheet (including the Action Plan tab) as described below. Please submit the updated SIOP simulation spreadsheet along with a brief (~half page) narrative write-up covering the key points above. Also address the team's thoughts as to whether this kind of optimization could be achieved without high level coordination among accounting, sales, and operations. Please read the above linked PDF file and also carefully read the information below.

Once you start working the simulation and begin manipulating the spreadsheet, be aware that there are not a lot of end-user safeguards built in. You can easily overwrite formula cells, etc. Also, depending on the decisions you make, some manual copying and pasting of data may be required as instructed below. So work carefully and save frequently as you go. Finally, be sure to read the "Running Solver" section near the bottom of this page before you dive in and start making decisions. The team will start with a baseline plan as represented on the Model tab of the SIOP Simulation S18 Excel file, linked below. The current baseline product mix with forecasted revenue, cost and profit appear in cells A1:E8 on the Model tab. As you can see, this baseline plan creates an overall mix across various product families that is in total comprised of 241,809 pounds of aluminum. Reading the case, you will see that the Controller has built an overhead allocation model based on a projection of 350,000 pounds produced, and the Ajax Sales Team believes at baseline price levels there is an available market for 630,000 pounds of Ajax product (see base case pricing and demand on the Pricing and Market tab, cells A2:G8). Clearly your revised plan for the upcoming period needs to accommodate an increase in the overall poundage of products produced. Working together, the Controller, Sales Manager and Operations Manager must coordinate and sequence several decisions in order to maximize the plant's planned profit. Your team should work through a sequence of decisions that may be comprised of the following actions.

Decision Sequence Action Plan Before reviewing the list of possible decisions below and how to record them within each tab, please be aware that each time you make a discrete decision, you must record it on the Action Plan tab along with the Operating Plan poundage, Revenue, and Total Profit that result. A simple brief description of each decision in column B of this tab starting under the Baseline Plan (e.g. "Purchased a new saw") will suffice. Also, please be aware that after each decision you will need to run Solver (see Running Solver section below) to update your model results. Capacity Changes As described in the case, you have a $100,000 capital budget that can be applied on the Capacity tab to support your decisions. If you would like to purchase a machine, simply enter the number ordered in a yellow cell under column E. Do not purchase any additional machinery once your remaining budget hits zero. It is not necessary to use all of your budget. Labor Changes You do not have the ability to hire additional resources in this simulation, but there are three things you can do on the Labor tab to modify the application and efficiency of labor over the planning period: There is an overall aggregate limit of 24 shifts that can be scheduled per day across all production areas (Labor tab, column D).

You may not increase the overall number of shifts, nor can you schedule more than three shifts for any production area, but you can rearrange shifts among production areas. These decisions should be recorded in column E. For example, you could increase shifts for the Lab Testing Equipment from 2 to 3 (cell E14), but you would then have to reduce a shift in some other area to balance the total at 24. You can also request that up to six production areas work 7 instead of 5 days per week. The baseline plan shows weekends assigned for each of the three Extrusion Presses (cells G7:G9) and regular weekday schedules everywhere else. You may modify the yellow cells in column G to indicate up to six total production areas working 7 days per week. Finally you may schedule one Lean event during the planning period to improve productivity of one and only one of the production areas. Simply place an X in a yellow cell under column H to indicate the area you would like to improve. Pricing Changes On the Pricing and Market tab you may choose to increase or decrease your product family prices. To do so, simply indicate your choice on the drop-down in cell B16. Price change scenarios in cells A10:G14 indicate what the Marketing Team believes will happen to demand in optimistic through pessimistic scenarios for each level of price change. For example, their current estimate of an Ajax market size of 630,000 pounds at baseline prices is reflected in cell E13 (with a range of 580,000 to 680,000 pounds).

Marketing believes a price decrease of 5% would increase the market potential of Ajax to a target of 670,000 pounds (within a range of 630,000 to 720,000 pounds). If you make a pricing change, you must update the Model tab manually. Update your selling price by copying cells J3:O3 on the Pricing and Market tab and pasting the VALUES in cells B13:G13 on the Model tab. Also update available market demand by copying cells J7:O7 on the Pricing and Market tab and pasting the VALUES in cells B20:G20 on the Model tab. Changing Overhead Allocation The current overhead allocation method is described in cells B6:E13 on the Overhead tab. If you believe the alternate method is superior, you may instead use the overhead values from columns G and H in the alternative method table found in cells B16:I23 of this tab. Again, if you choose to do this you must manually transpose and replace the values in yellow on Model tab rows 27 and 28 with the correct values from the alternate overhead allocation table. Running Solver To run this simulation you must use the Solver add-in for Excel. To install Solver in Excel, go to File => Options => Add-ins. At the bottom of the pop up screen you will see a "Manage:" field showing Excel Add-ins. Click the "Go..." button to the right of this field, check Solver Add-in, and click OK. To be safe you may want to restart Excel after doing this. If successful, a Solver button should appear on the far right of the Excel display ribbon on the top Data tab. In the simulation spreadsheet, the Solver parameters are already set to maximize profit within the constraints of your team's decisions. After you make a change based on a decision as described under the sections above, you can go to the top Data tab in Excel, click Solver, and then click the "Solve" button along the bottom of the pop-up screen. Solver will (hopefully) generate a Solver Results pop-up screen indicating that it found a solution. The product mix in the simulation workbook (Model tab cells B18:G18) will now be updated, with impacts rippling through the workbook to show new financial results. If you would like, before clicking "OK" on the Solver Results pop-up, under "Reports" on that pop-up you can click "Sensitivity". This will generate a new spreadsheet tab to the left of your Model tab titled "Sensitivity Report". Of the various statistics on this report, the one most useful to you will be the Shadow Price listed under Constraints. This price (if > 0) indicates the additional revenue you could generate by having another hours of use from the bottleneck resource. To keep things tidy you can delete the Sensitivity Report tab each decision round after it has provided the information you need.