Managing the Supply Chain

LOG 501: Managing the Supply Chain

Introduction

For this case assignment, I have received a project from Mr. Danny Wilco. The project is to use Import Beer product line as a test case for developing an optimization program. Accounting has provided me with: Inventory holding socts, product coasts and price markups. I will take the provided information and develop a method that will determine the optimal ordering quantities so that the company can maximize the gross profits and I will also take into account the inventory holding cost.

Problem

Mr. Wilco, I am going to optimize the inventory for the Import Beer product line. The Import Beer product line will be utilized as a test case for an optimization program. I will provide a spreadsheet that will show maximization for the gross profits while taking into account the inventory holding costs. Hopefully, the final results can be used going forward on a quarter by quarter basis and for the other product lines that the company utilizes.

Solution

Table 1 shows the initial Imported Beer product data:

Table 1 - Initial Imported Beers Data

The spreadsheet is broken down by product codes, decision cells, Inventory Cells, and cost per unit. There was a markup factor included and for this scenario 140% was the value, which was provided to me by accounting. Accounting also provided the inventory holding cost and the product cost. Once, I received the required information, I setup decision and inventory cells. I also provided the inventory constraints, which were to be utilized in my final product in table 2.

Solver Solution

Table 2 shows the end results once all data was obtained and utilized. When utilizing the solver solution I had to add my constraints which were:

* Inventory constraint - 190

* Ending inventory values - all had to be non-negative

Once all data was added to the spreadsheet, I was able then to use the solver solution. I had to manipulate the order quantities so that they would keep the Cap Usage under 190. How I came to the amounts being order was basically a hit or miss, I checked the ending inventory and as I moved numbers around, I still ensured that I was still within the inventory constraints.

Within the finding solutions I had to tweak and retweak numbers to see how they fight within the factors of the spreadsheet. In the end result,