Posted: April 9th, 2015

Semester ProjectOPM 305

Semester ProjectOPM 305

Purpose:        Demonstrate an ability to use Excel to build simple decision models. Using Excel, you will build a set
of models designed to simulate a series of decisions along a supply chain.

Format:        Keep everything in one Excel workbook, composed of several worksheets. Be sure to label the
tab at the bottom of each worksheet with an appropriate short title. Each worksheet will be a separate model.

Worksheet #1    TITLE PAGE

The first worksheet will contain your name, date, “OPM-305-50,”
and your student ID number

The first worksheet will also contain a cell labeled “ANSWER” followed by a number.
In this “number” cell you will place the result of calculations you make on other worksheets.
Your final answer here will be copied from Worksheet #8.

The first worksheet will also contain a cell labeled “INTERPRETATION.”
In this cell you will type a short statement of what your answer means in a practical sense. Here you should also describe briefly any unusual (or unexpected) circumstances that you find, as well as what decisions you made to manage those circumstances.

Worksheet #2    Input Values

Worksheets 3 through 8 (below) will contain various models, one per worksheet. Each model must be generalized so that it works for any set of input values, not some specific set. You must use Excel features to calculate these values; do not simply use Excel as a typewriter.

Worksheet #3    Product Mix (Linear Programming)
What is the optimum mix of products that you should build?
i.e., how many products of each style should be produced?

Markon Furniture Factory builds four styles of tables: modern (round and square) and traditional (round and square).
They have a contract with the Sleep Late hotel chain to sell certain quantities of each table style (stated as units per year, or annual demand). Markon also wants to sell as many additional tables as their resources will permit, up to a maximum of three times the contracted quantities per table style.
Each style of table requires certain quantities of wood and steel per unit. Each style also requires certain amounts of money (production cost) per unit.
[Markon actually operates two factories, at Farmington and Salem. We will assume that all costs are identical and processes are fully interchangeable, so that you may treat Worksheet #3 as a single factory. This fact will become important in Worksheet #5.]
Inputs:        (1)     revenue-contribution coefficients [given]
(2)     contract (demand) quantities [given]
(3)     resource-utilization coefficients [given]
(4)     resource availability quantities [given]
Process:    Using Solver in Excel, find the optimum product mix for Markon, as well as their total expected revenue and expected production cost.
Outputs:    (1)     Total Annual Revenue for Markon
(2)    Annual production quantities of four styles of tables
(3)    Annual total production quantity (sum of four styles)
(4)    Annual material requirements of steel and wood
(5)    Annual production cost
(Hint: Assume “production cost” includes the cost of purchasing raw materials plus all the costs of processing them into tables.)

Worksheet #4    Inventory
How much to order? How many times per year? What will it cost?

Steel and wood are purchased from various suppliers throughout the year, and must be stored in Markon’s warehouse until needed in the factory. Because steel and wood come from different suppliers and must be stored under different conditions, there are different costs of ordering and holding wood and steel. (Hint: this means you will need two separate inventory models.)
Inputs:        (1)     the outputs from Worksheet #3 (as needed)
(2)    Costs of ordering and holding steel and wood [given]
Process:    Using the basic method, calculate EOQ, orders per year, and Total Annual Inventory Cost. Do separate calculations for steel and wood, then combine the costs into a Total Annual Inventory Cost for both materials.
Outputs:    (1)     EOQ for steel and EOQ for wood
(2)    Orders per Year for steel and Orders per Year for wood
(3)    Total Annual Cost for steel, Total Annual Cost for wood, and Total Annual Inventory Cost.
Worksheet #5    Transportation
How much does it cost to distribute the raw materials?

Wood comes from suppliers in Joplin and Knoxville, with varying transportation costs and supply quantities at each supplier. Steel comes from suppliers in Chicago and Pittsburgh, with varying transportation costs and supply quantities at each supplier. Wood and steel are shipped to Markon’s two plants at Farmington and Salem.
Inputs:        (1)     the output from Worksheets #3 and #4 (as needed)
(2)    Annual supply of steel at Chicago and Pittsburgh [given]
(3)  Annual supply of wood at Joplin and Knoxville [given]
(4)  Annual demand at Farmington and Salem [given as a fraction of the Demand you calculated in Worksheet #3]
(5)    Transportation costs per unit, all paths [given]
Process:    Using Solver in Excel, find the optimum annual transportation plan for the sources and destinations in Markon’s supply-chain “market.”  Then calculate the total annual cost of transporting wood and steel to Markon.
Outputs:    (1)     Transportation quantities (units) for each feasible path.
(2)    Total annual transportation costs for Markon.

Worksheet #6    Expected Profit
How much profit does Markon expect to make?

Markon’s annual profit is calculated according to the formula:

Annual Profit  =  Total Annual Revenue  –  Total Annual Cost

where Revenue is derived from the four table styles, and where Total Annual Variable Cost is the sum of Production, Inventory, and Transportation costs.
Markon has a bank loan that must be repaid annually. This amount is a fixed obligation and does not vary with production quantities.

Inputs:        (1)     the output from Worksheets #3, #4, and #5 (as needed)
(2)  Annual debt service (bank loan) cost [given]
Process:    Calculate Annual Profit using the formula above.
Outputs:    (1)     Total Annual Profit

Worksheet #7    Owner Income Goals
How many units are needed to let the owner reach a personal goal?

Mark Onzonia (Markon’s owner) wants to take a minimum of $100,000 out of the business each year for his personal salary. Calculate the number of units of each table style he must produce to reach his income goal and then sum the four styles to get a total production quantity.
[Hint: You may use a break-even model for this calculation, modified for $100,000 instead of $0 profit. You will also need to do a weighted-average or Expected Value calculation to break down the total costs and revenue into the four table styles. Remember that cost and revenue calculations on your previous worksheets were in annual totals; here, they are per-unit.]

Inputs:        (1)     the output from Worksheets #3 through #6 (as needed)
(2)  Annual debt service (bank loan) cost [given]
Process:    Calculate goal quantities (similar to break-even) using the formula in Taylor chapter 1.
Outputs:    (1)     Total annual production quantities per table style
(2)  Total annual production quantity, all tables

Worksheet #8    Net Surplus/Deficit Profit
Are the owner’s personal goals realistic?

Now compare the total Expected Profit you calculated in Worksheet #6 with the owner’s $100,000 goal, using the formula below:

Net Surplus or Deficit  =  $100,000 – Expected Profit

Inputs:        (1)     the output from Worksheets #3 through #6 (as needed)
Process:    Calculate surplus or deficit using the formula above.                                                        Outputs:    (1)     Net Annual Surplus or Deficit

Worksheet #1    ANSWER CELL
Your answer will be Net Annual Surplus or Deficit from Worksheet #6.
Copy this number from Worksheet #8 to Worksheet #1.

Method:    Models for this project should be variations of the same models you have done earlier as class exercises. The difference is we are now “chaining” them together to simulate a series of related decisions. Be sure to make your models general enough to work with any set of values, within the limits described above.

Using the web site file labeled “Project Individual Values,” locate your name and use your personal values as inputs to your models. Assuming that you have already validated your models, you will simply plug in the assigned numbers, revise and interpret your models as needed, and read your answer.

HINT #1:    Design your models so that the output of one “flows” automatically to the input of the next (wherever it is needed). If you do that, then the “answer” cell on Worksheet #1 will be automatically populated with the correct answer. When you change values on any worksheet, corresponding values will automatically change on subsequent worksheets.

Final Instructions:    Models for this project should be variations of the same models you have done earlier as class.

1    Build and solve your models using the instructions above.

2    When you are satisfied with your work, click your cursor on the cell at the top left corner of
Worksheet #1 (cell A1). Then click “save,” name your file, and end Excel. (This way, when I open the     file, it will open on the page with your name and answer.) Your saved file name should consist of your course number and  class section, last name, and first name (Example: “305-50 Smith Susan”).

3    Send an email to [email protected] with the subject line “305 Project” and attach your saved file.
No other content is necessary.