Logistics Supply Chain Management Course Module 3 Case assignment

Here is the Background info/Case assignment on Module 3:

Module 3 – Background

Optimizing Inventory/Transportation

Virtual World

EBBD EMAIL – for Internal Use Only

To: You

From: Danny Wilco <dwilco@ebbd.com>

Subject: Re: Quarterly Product Ordering Decisions

Jones was reading an article in a back issue of Logistics Manager about optimizing inventory and supply chains. And I have some knowledge of an optimization technique called linear programming or LP. We think that there is a way we can use this technique with our new forecasting information to develop a quarterly product ordering and inventory plan to optimize our profits.

Here’s the article that Jones was reading:

Anonymous. (2010, Sept.). Supply chain standard: Tooled up to optimize [sic]. Logistics Manager. Retrieved from ProQuest.

Check out this article. It does not pertain precisely to our situation, but the concept of optimization is what we are interested in. We want to be able to plan for quarterly order quantities based on the forecast of sales, the forecast of cost inflation, and our inventory storage capacity. Knowing what to purchase and hold given the future costs, should help us to maximize our profits.

I will send more details shortly.

~DW, VP LogOps.

Learning Wizard

Case 3 Resources

Here is some (a lot of) background information to help you learn how to do LP. Follow these guidelines and you will be a wiz yourself in no time.

What is LP? Read these resources to gain an understanding. Then do the learning exercises.

Learning Exercises

1. Learn the Basics from this PowerPoint.Download this PowerPoint with info on the basics of Linear Programming: problem formulation and graphic solution in 2 dimensions – the Flair Furniture example. [Linear Programming Models: Graphical & Computer Methods]

NOTE: 2-D problems are the easiest to setup and solve, and you can see the solutions graphically. Higher dimension problems, with three or more decision variables, sometimes up into the thousands, are possible. The method is basically the same – find the corner points and evaluate the objective function – but it is more complex and takes more time and computing power.

****WATCH THIS VIDEO THAT REVIEWS THE POWERPOINT – IN YOUTUBE*** http://youtu.be/0HBHjHVE1XY

2. You will need to setup Solver Add-in in your Excel. This comes as part of the Excel Package. You just need to make sure you have it setup and ready to use.

***NOTE: Spreadsheet in OpenOffice has Solver included. Go to the Tools menu and click on Solver.

3. View some examples and then try the Greenberg Motors problemDownload this Excel Spreadsheet file with examples of different LP problems: [Product Mix Problem Example: DJJ Enterprise]

The first Tab in this spreadsheet file is DJJ Enterprise. Watch these three YouTube videos to see how this problem is formulated, setup and solved in Excel with Solver.

• YouTube LP example: DJJ Enterprise [3 parts]
• Now download this PowerPoint file that provides more examples including the Flair Furniture Example. [LP-Solver&Examples].
• Review each example in the PowerPoint and explore the setup and solution in Excel.
• Try the Greenberg Motors problem on your own. Once you think you have it solved, download this Excel File to see a solution. [GreenburgMotors-Solution]
• Now focus on the problem for EBBD.
• Once you know what your task is from the case assignment, you can experiment with these spreadsheets with a similar, simpler situation.
• Try the following:
• Heuristics 1 Tab: First try to find the optimal combination of products to order using your best guess and any heuristics that you can develop. This is an adaptation of the Flair Furniture problem.
• Heuristics 2 Tab: Now try heuristics to solve this problem, similar to the EBBD situation with Quarterly ordering quantities.
• Solver Tab: Try to develop the solver solution for this problem. It is very similar to the EBBD problem in the assignment.
• WATCH THIS VIDEO ON SOLVING THE EEBD EXERCISE – IN YOUTUBE: http://youtu.be/LwGXaM6WVq8
• IF YOU WANT TO CHECK YOUR SOLVER SOLUTION TO THE EBBD EXERCISE, SEND AN EMAIL TO YOUR INSTRUCTOR ASKING FOR THE EBBD EXERCISE SOLUTION FILE.
• You should be able to solve the EBBD problem if you have completed all of these exercises. Make sure you download the Excel file from Danny Wilco with the Data for Case 3 – on the Case page in his email.

***NOTE: THIS MODULE IS NOT ABOUT ECONOMIC ORDER QUANTITY (EOQ). THE CASE SOLUTION MUST USE LINEAR PROGRAMMING AND EXCEL/SOLVER.***

The article that Danny Wilco was talking about, that Jones discussed with him:Anonymous. (2010, Sept.). Supply chain standard: Tooled up to optimise [sic]. Logistics Manager. Retrieved from ProQuest.

• SLP 3 Resources – Modes of Transportation

Waterways

U.S. Army Corps of Engineers. (2010). The U.S. Waterway System, Transportation Facts & Information; Navigation Center, retrieved from http://www.ndc.iwr.usace.army.mil/factcard/factcard10.pdf

American Society of Civil Engineers. Report Card for America’s Infrastructure, retrieved from http://www.infrastructurereportcard.org/fact-sheet/inland-waterways

Texas Transportation Institute. (2009). A Modal Comparison Of Domestic Freight Transportation Effects On The General Public, retrieved from http://www.nationalwaterwaysfoundation.org/study/FinalReportTTI.pdf

All Modes – Provides Cost Comparisons by Mode and Other Comparisons

Center for Climate & Energy Solutions. Freight Transportation Comparison, retrieved from http://www.c2es.org/technology/factsheet/FreightTransportation

Module 3 – Case

Optimizing Inventory/Transportation

Virtual World

EBBD EMAIL – for Internal Use Only

To: You

From: Danny Wilco <dwilco@ebbd.com>

Subject: Re: Quarterly Ordering Decisions

Here is what we would like you to do. We want you to use the Import Beer product line as a test case for developing an optimization program. Use the Excel file that we have developed with the forecast information for the import beer product line. Other information that you will need has been provided by Accounting: inventory holding costs, product costs and price markups.

This information with the Sales Forecast and costs and prices has been provided in the Excel file. [EBBD-ImportsData]

We want you to develop a method that will determine the optimal ordering quantities so that we maximize the gross profits and also take into account inventory holding costs.

After you have developed this method, write a short report to management explaining what you did and how this method can be used going forward on a quarter by quarter basis and for the other product lines.

Also attach any calculations or analysis that you did in an Excel file.

Let me know if you have any questions.

~DW, VP LogOps.

Learning Wizard

If you have done all of the exercises successfully you should be able to easily complete the EBBD assignment from Wilco. Be sure to watch the videos.

Download the EBBD-ImportsData Excel (click on link above) file and look at how it compares to the practice examples in the EBBD Exercises spreadsheet.

Setup the decision cells, the inventory cells and inventory constraints. Then add the calculations for income and costs. Make sure you get the timing right for which inventory is sold in which quarter. You could develop intermediate cells that calculate the costs, just to help you keep track of this. Be sure to use the quarterly inventory holding cost rate. Then calculate the gross profit cell.

Then, open up Solver and add the aspects to it. Make sure that you add a constraint that keeps your inventory values positive.

Once you have the correct solution, save your Excel file and write the report.

Upload the Report and the Excel file with the solution to Case 3 Dropbox.