View the step-by-step solution to:

OPMA 3308/5363 - Spring 2013 Assignment #4 Due: Wednesday, March 20 1. Given the information below, develop a Linear Programming model to determine...

I am not sure how to set up the formulas for solver in problem 1.
OPMA 3308/5363 – Spring 2013 Assignment #4 Due: Wednesday, March 20 1. Given the information below, develop a Linear Programming model to determine how many units to produce in each of the four quarters using straight time and overtime, and how many units to carry from quarter to quarter, to minimize the total production cost and carrying cost. Write out your LP model on paper or type it. Then enter and solve the LP model using Solver in Excel, like I did in class. Print out your final spreadsheet showing the optimal solution. Tips: (you need only 12 variables) -- Let S1 = # of units to produce in quarter 1 using straight time labor (also S2, S3, S4) -- Let V1 = # of units to produce in quarter 1 using overtime labor (also V2, V3, V4) -- Let N1 = # of units of ending inventory in quarter 1 (also N2, N3, N4) -- use 4 constraints to represent Sources of product = Uses of product in each quarter -- use 4 constraints to represent minimum ending inventory each quarter -- use 4 constraints to represent maximum ending inventory (capacity) each quarter -- use 4 constraints to represent maximum straight time production capacity each quarter -- use 4 constraints to represent maximum overtime production capacity each quarter -- in the Solver Parameters window, select Simplex LP in the ‘Select a Solving Method’ box Qtr Forecast 1 3000 2 1800 3 2400 4 3500 Paramet ers 300 current inv. Level $20 carrying cost rate $/unit/qtr $320 production cost per unit using straight time $380 production cost per unit using overtime 100 minimum ending inventory each quarter 500 ending inventory capacity 2500 straight time production capacity 600 overtime production capacity
Background image of page 1
2. A product has this indented bill of material: Level 0 1 2 3 Quantity X285 1 A10 3 T3 1 Z7 2 V1 2 J20 2 H8 1 U4 2 M30 3 P2 2 V1 1 This inventory status report has just been issued for the project: Item On Safety Lot Lead Time Code Hand Stock Allocate d Size (weeks) X285 250 50 0 LFL 1 A10 900 100 0 LFL 1 J20 850 100 10 LFL 1 M30 1,100 100 35 LFL 1 T3 750 300 60 LFL 1 V1 2,000 500 110 2,000+ 1 H8 500 300 90 750+ 2 U4 500 300 0 1,000+ 1 P2 1,000 300 105 500+ 1 Z7 2,500 500 250 2,000+ 2 a. Prepare MRP tables for all the items to cover a 6-week planning horizon if the MPS for product X285 shows an estimated demand or gross requirement of 80, 135, 280, 155, 210, and 275 units in weeks 1 through 6, respectively. Use Excel to prepare your MRP tables, using formulas to do the calculations. Print your completed Excel spreadsheet. b. Are the MRP schedules for all items feasible from a material supply perspective? If not, what might be done?
Background image of page 2
Show entire document
Sign up to view the entire interaction

Top Answer

The best way to approach your question... View the full answer

8441146.xlsx

Solution
Decision variables
Straight time production in quarter 1
Straight time production in quarter 2
Straight time production in quarter 3
Straight time production in quarter 4 x1
x2
x3
x4...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online