ISYE 3350 SC Game Report 2

Supply and Conquer

Ryan Waltman, Erick Hohler, Ethan Mahon, and William Villasana

November 13th, 2022

Introduction

The context of this project revolves around the selling of an industrial chemical. Through this product, our group was assigned the goal of maximizing the total profit within two years. Using what we learned in class with forecasting methods and with the use of excel models, we set out to create a profitable and optimal combination of the set parameters. The customizable parameters in this simulation included the purchasing of additional capacity to our factory, the finished goods inventory threshold that triggers the production of a new batch of product in the factory, the batch size that the factory produces, the transportation type and size, choosing which states you can transport produced goods to, and the ability to build new factories and warehouses in different states. By the end of the deadline, our Team Supply and Conquer was able to achieve the highest rank out of all the other competing groups by amassing the highest profit of $23,400,001.61. Through this report, we will explain in a deeper context how our team was able to execute this project.

Strategy Analysis

For the creation of our team’s strategy, we first created a demand forecasting model, a capacity forecasting model, and, finally, a financial forecasting model. These models helped in selecting our initial parameters, and the changing order points throughout the simulation. These models were also modified from the last game to better apply to the new parameters made available.

Demand Forecasting Model

By using past demand, our team created an exponential smoothing with a trend model. Our team used the same predicted demand for Calopeia as it was the same as the first supply chain game. The historical demands of Tyran and Entworpe were extended to match the 730 days as only about 90 days of data was given for the new states. The demand for Sorange was forecasted using the data given and a line of best fit following a linear trend in excel (as Sorange’s demand increased by a consistent factor). Then, using that line, we found roughly what the average demand should be each day and then used the average variation to create a randomly generated forecast of demand that fluctuates around the trend line. Regarding Fardo, its demand was excluded due to the state’s demand being unprofitable. Our team came to this conclusion because of significant transportation costs that would have increased the losses we would incur against any potential profit. All calculations relating to demand and lost demand excluded Fardo. All demands were made to decrease by a consistent linear factor for the last 30 days (to consider a new technology impacting demand at the end of the game). This newly created prediction of demand for each state gave our group a better insight into the cyclical and varied nature of the demand. This in turn helped us smooth out the demand curve to make a better-educated estimate of what the next two years would look like.

Capacity Forecasting Model

Using the forecasted demand, our team used the numerous variables described in the supply chain game to create a capacity forecasting model. This model recorded the day, daily capacity, predicted demand, the various inventories (factory inventory, inbound inventory, warehouse inventory, inventory threshold, sold inventory), and lost demand. Then, by using numerous formulas to predict the output of each category, statistical figures (like the total amount of sold products or remaining inventory at the end of each year) were calculated. The model was modified in the second supply chain game as the lost demand calculation was slightly inaccurate.

Financial Forecasting Model

After the creation of a working capacity forecasting model, our team shifted our focus toward the most important model, the financial forecasting model. Since the capacity forecasting model produced useful results like the number of products sold on any given day, figures like revenue and production costs were calculated. We based our financial sheet on categories allotted in the supply chain game (ex. starting cash, revenue, interest, capacity expansion cost, production cost, etc.). After the completion of the financial forecasting model, we used an Excel solver tool to calculate the optimal combination between the given parameters and double-checked them using our own reasoning and understanding of the model and the variables. This model was modified in the second supply chain game to take into consideration different transportation costs based on the state the batch is traveling to.

After creating the model and finding the highest profit we could, we recalculated the expected profit by hand using the parameters that gave the expected maximum profit from our model to see if our model predicted the maximum profit roughly around what our hand calculations estimated. Our model initially predicted a profit of around $17 million, and our calculation check predicted around $25 million. After the game commenced, we found a flaw in how our model calculated the profit and addressed it giving our new predicted profit at roughly $19 million, an improvement since our actual profit ended up at $23.5 million. Through this sanity check of our numbers, our team found that our actual profit was in between our hand calculations and model profits, with our model consistently underpredicting but accurately following the trend.

Parameters, Explanation, and Interpretation

The parameters chosen for the simulation problem are as follows:

Factory and Warehouse Creation: 0

Our team decided not to create any new factories or warehouses in the other states. We calculated that the initial cost of any new factories or warehouses cost more during the duration of the simulation than expanding the capacity of the already-built factory in Calopeia. Another consideration for creating a new factory is the 90 days to build the new factory and then the 90 days to expand the capacity at the new factory. The extra 90 days of construction versus just expanding the capacity of the already constructed factory in Calopeia leads to more lost demand that neglects any potential gains from cheaper transportation costs.

Capacity Expansion: 91

While the model chose an optimal expansion of 91 units per day, this number is logically sound when considering other mathematical views. We forecasted that we would have a large amount of lost demand if capacity wasn’t expanded. In order to account for this, we increased our capacity significantly compared to the first supply chain game. However, most of this increase was due to an increase in overall demand when compared to the first game. By expanding capacity, the historical data showed lost demand averaging 19 units per day which is a better figure compared to the first game’s 39 units per day. As a result, we lost less demand per day while also increasing capacity to account for higher demand, so our overall profit margins increased. While not all lost demand could be accounted for due to the large initial capacity expansion costs, increasing by 91 units per day (versus a larger expansion to account for a greater total of lost demand) is a logical compromise between lessening lost demand and avoiding large capacity expansion costs. This initial capacity expansion was expensive and brought our group’s ranking down significantly at the start, but this occurrence was expected due to the large initial cost of expanding the capacity. Even with the temporary large initial expense, the longer-term profit motive neglected this fact.

Finished Goods Inventory Threshold Schedule:

Day 730-1000: 2404
Day 1001-1167: 996
Day 1168-1419: 2043
Day 1410-1460: 0

In our first run of the game, we did not change the order point at all, except at the beginning. Rather, we selected the optimal order point at the start of the game for the entire game if it stayed consistent. In the second game, we realized changing the order point throughout the game would help us to optimize our production through the changes in the data, specifically the demand. The day intervals above were selected based on the changes in demand throughout the simulation. Each interval of data was analyzed to determine the optimal order point. We then changed the order point at approximately the beginning of each time interval. The last interval, days 1410-1460, has an optimal order point of 0. The reason 0 was selected is that demand sharply decreased linearly as described in the information given before the start of the simulation. Therefore, we decided not to produce batches of products that would not be sold to generate a profit and would only contribute towards warehouse storage costs and wasted production costs.

Truck Quantity/Batch Size: 200

The truck quantity or batch size was chosen due to the one-time cost of trucks. A truckload costs $15,000 if transported from the same state, or $20,000 if transported to a different state on the same continent. Because truckloads cost the same regardless of the amount of product on said truck, it is logical to maximize the amount of product on each truck. Since a truck can hold a maximum of 200 units of product per truckload, our group decided to stick with a truck quantity/batch size of 200 units.

Transportation Method: Truck only

We chose to transport the product by truck exclusively over mailing for economical and logistical reasons. Mail shipping was very inefficient due to the price for a product to be moved. For instance, after doing a breakeven analysis comparing a truckload’s cost to a mail order, we have concluded that it would be against our main interest to include it as a method of transporting our product because of the better benefits that truck shipping provided.

Data Interpretation

As briefly described in the demand forecasting model section, the historical demand followed a seasonal cycle for Calopeia, consistent demands for Tyran and Entworpe, a consistent increase for Sorange, and all demands gradually decreasing to 0 during the last 30 days. Knowing this, future demands were predictable with the assumption that all factors would remain constant. The consistent demand cycle allowed our group to use the exponential smoothing trend forecasting method, leading to an accurate model. Overall, there was a difference of approximately $4,400,000 more profit than predicted from our model. A difference was to be expected as the actual demand was larger than expected (specifically Sorange’s demand was much larger than predicted originally). However, $4,400,000 is a difference of approximately 23% from what we predicted our earnings to be which is significant and likely to have occurred due to our exponential smoothing with trend model not calculating the demand to perfect accuracy (as expected, as forecasting is only a prediction). Additionally, our model underpredicted our profit in the first supply chain game, so our team expected to achieve a higher profit than our model’s prediction. In the future, the goal would be to have measures to account for large variations in demand and to have more data, if possible, on states with only 90 days of historical data in order to mitigate differences between predicted profit and actual profit.

The inventory levels changed throughout the simulation based on the amount of demand and the order point selected. Warehouse inventory was consistently high at the beginning of the simulation due to a large order point. Inventory sharply declined once the order point was changed to a smaller value once demand started to falter some (largely due to Calopeia’s cyclical demand). After the order point was changed a third time, warehouse inventory continued to stay constant at a lower level, eventually crashing to zero near the end of the simulation once the order point was changed to zero (due to demand linearly decreasing on the last 30 days). Our team struggled to manage a smaller warehouse inventory, the first supply chain game, but due to our changes in the order point, warehouse inventory was kept in order.

Game Monitoring and Adjustments

To monitor the game, each group member kept track of changes in the simulation through scheduled shifts. If a member thought a change was needed, we all agreed that the group member should notify the group and give their reasoning before gaining permission for adjustment. Before the start of the second supply chain game, our team calculated the optimal days to change the order points, and by what amount. Whichever group member was assigned to that day and time had the responsibility to change the order point. Not too long into the game we quickly realized that we had dropped in standing almost immediately. However, as previously stated in this report, it was expected given the initial purchases that we had made to start the game. As the simulation continued, our profit margin rebounded for the better and allowed us to quickly exceed our initial investments, bringing our group to first place for most of the simulation.

Conclusion

When the deadline was reached, our group achieved the highest rank in the second supply chain game. Our team won with a lead of over $10 million, ending with about 76% more profit than the team in second place. We conclude that we were able to attain this rank by relying on our coursework in forecasting methods and in breakeven analysis, by creating a reasonable forecast model using the variables given to us, and by changing the order point throughout the simulation.

Regarding a potential situation where we reattempt the game, we would likely continue to use our current model. However, if we had the time to make improvements, we would improve our current model as certain figures from our model continued to be off compared to the actual results from the simulation on both supply chain games. On top of that, it would be very beneficial to improve on demand forecasting regarding variation to mitigate gross differences between predicted and actual profits. Not only would we work to improve our model, but we would also go back and investigate the various transportation methods not taken to see if a better shipping combination could be found. Additionally, some changes to our alpha and gamma numbers for the use of forecasting demand with exponential smoothing with trend could see adjustments based on the new demand data given from the simulation.

Overall, like the first supply chain game, our team continued to learn the value of historical data to make future changes. Even when using forecasting methods on past data, your predictions will always be wrong. However, our team learned that forecasted data gives a good insight into what the future will hold. Additionally, our team learned that building new factories and warehouses does not always lead to more profit, even with cheaper transportation due to the high initial building costs and the time to construct said buildings.