Worksheet #2 Input Values

 

I will post an Excel file to the class website (OPM305.com) on March 4 which contains a set of unique individual values for everyone in the class. These values will be your input values that you need to solve your models. Any time after March 4, locate that file and download it to your computer.

 

COPY the headings and your individual values from the “Project Individual Values” file you downloaded into Worksheet #2.

 

If you do this, you will not make a mistake re-typing the input values. Now you can simply reference the input value cells from your other worksheets when you need them.

 

Before March 4, you may start building and validating your models. Then when your data values are ready, all you will need to do is copy your data, run your final models, and turn in your assignment.

 

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”).