Solved by Expert Tutors
Solved by Expert Tutors
Question

For this assignment, please complete problem number 8, from the end Chapter 11 of your textbook.

Use "Solver" to set up and answer all three parts. Save all parts in three separate worksheets (e.g., part a, part b and part c) in a single spreadsheet file (make sure that your Excel file name includes your last name).
Once you set the problem in a spreadsheet, parts b and c should be quite easy to complete as the only variations will entail different decision variables and constraints.
Please view the Camtasia video file "Aggregate Planning" as a guide (located in week 6 Reading and Resources section).
Hint: This assignment is pretty much based on the video that I have prepared for you on aggregate planning. All parts need to be completed using Solver. All three parts a, b, and c are almost identical in terms of set up. The only differences lie in identifying the correct decision variables and constraints from Solver. Let's look at part "a". The problem states to use level production and supplement with overtime as needed. This means every month we will be producing 5000 units and if short to use overtime. Note that subcontracting does not play a role here and there is no constraint on the number of units produced in overtime. So, once you set the problem up in Excel and invoke Solver, what would your decision variables be? They will be the rows for "Regular production" and "overtime production". What are your constraints? Make sure that one of your constraints equates the regular production to equal 5000 units (remember we are using a level production). Anything else? Not really just add the non-negativity constraint and check the box "assume linear model". Don't also forget to ensure that your "ending inventory" row will be greater than or equal to zero. You are done with part "a". Approach parts "b" and "c" in a similar fashion. For part "b" you are told to use a combination of overtime, inventory and subcontracting. So, your decision variables will be the regular production, overtime production, and subcontracting. Regarding the constraints you should have three of them right? One for the regular production (<=5000), one for the overtime production (<=500) and another one for the subcontracting (<=500). Also, don’t forget the non-negativity constraint and ensuring that ending inventory must be greater than or equal to zero. Finally for part "c", you are told to use overtime, regular production and inventory. So, what are your decision variables in this case? Regular production and overtime production. What are your constraints? Regular production <= 5000 units, and overtime production <=750 units. Note that once you set the problem up and solve it, you may see some sort of an error message stating that the linearity assumption is not satisfied. Don't be alarmed by this message since we know for a fact that the model is indeed linear. When certain Excel functions such as "SUMPRODUCT()" are used in building the model, sometimes Excel "guesses" that your model may be non-linear. That is the rationale behind it. You may also see certain values in Excel such as 6E-5. This is literally zero as this value is actually 6 divided by 10 to the power 5 or 0.00006. Quite often, if you click on Solve again from the Solver window, the error message about linearity disappears and all values in scientific notations get rounded to zero.  Make sure to revisit the PowerPoint slides in week 5 that elaborate on the differences between the various interfaces of Solver based on what version of Excel you use.  

Background image of page 1
Sign up to view the entire interaction

Step-by-step answer

or nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie con

congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie c

Solution.xlsx
facilisis. Pellentesque dapibus efficitur laoreet. Nam ri m ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque risus an inia pulv amet, con

Subscribe 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.

  • -

    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
Let our 24/7 Statistics and Probability tutors help you get unstuck! Ask your first question.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes