Here is the Background info/Case assignment on Module 3:
Module 3 – Background
EBBD EMAIL – for Internal Use Only
From: Danny Wilco <email@example.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.
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.
Linear programming. (n.d.). Absolute Astronomy. Retrieved from http://www.absoluteastronomy.com/topics/Linear_programming
Linear programming: Introduction (n.d.). Purplemath. Retrieved from http://www.purplemath.com/modules/linprog.htm
- 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
- 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.
Download this PowerPoint to show you how to setup Solver [Setting up Solver Add-in]. Follow the directions. Then you will be ready to see the examples in try your own.
***NOTE: Spreadsheet in OpenOffice has Solver included. Go to the Tools menu and click on Solver.
- 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]
- Linear Programming Formulation in Excel (n.d.). YouTube. Retrieved from http://youtu.be/TNLqtmkK4EA
- LP Formulas in Excel (n.d.). YouTube. Retrieved from http://youtu.be/wQhNEMvh3MM
- Using Solver to Optimize LP (n.d.). YouTube. Retrieved from http://youtu.be/9tHqa8EV3rc
- 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]
- Once you know what your task is from the case assignment, you can experiment with these spreadsheets with a similar, simpler situation.
- Download this Excel file: [EBBD Exercises: Flair Furniture]
- 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.
***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
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
Ask.com ; Inland Waterways Transportation, retrieved from http://www.answers.com/topic/inland-waterways-transportation
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
Nationalatlas.gov. 2002 Data: Overview of U.S. Freight Railroads, retrieved from http://www.nationalatlas.gov/articles/transportation/a_freightrr.html
American Association of Railroads. (https://www.aar.org/)
American Association of Railroads. Railroad statistics, retrieved from https://www.aar.org/StatisticsAndPublications/Documents/AAR-Stats-2013-04-17.pdf
American Association of Railroads. (2015). Today’s railroads. Retrieved from https://www.aar.org/todays-railroads
Over the Road Trucking (OTR)
American Trucking Association (2015). Retrieved from http://www.trucking.org/
eHow.com. About Trucking Companies, retrieved from http://www.ehow.com/about_4586182_trucking-companies.html
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
EBBD EMAIL – for Internal Use Only
From: Danny Wilco <firstname.lastname@example.org>
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.
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.
Assignment Expectations of the written report – write the report to your boss, Danny Wilco
The report should thoroughly address these aspects in depth and breadth:
- Problem situation: clearly elucidate the problem situation at EBBD
- Assumptions: what are the assumptions that need to be made and your critical evaluation
- Solution: What is your solution for Order Quantities? Discuss how you developed the Solver solution. Keep in mind that your audience is not too technical and do not need a lot of detail on this.
- Make sure you attach the Excel file.
- You should refer to the Excel file when necessary.
- Recommendations: what do you propose for EBBD in terms of using this method in the future and for the other product lines?
- Justification & Explanation: clear reasoning as to why the recommendations were made
- Writing style & Organization: well-formed sentences and paragraphs, well organized with flow of reason, and good use of language that pertain to concepts and terminology
- Use of references & citations: Be sure appropriately cite sources within the paper and in the end reference list.