use the info below and Linear programming, to come up with the most optimal aggregate Production plan via EXCEL you may need to set up constraints Costs Regular Time Labour 15 Overtime Labour 22 Subcontracting cost (per unit) 74 Inventory carrying cost (per unit per month) 3 Material cost (per unit) 40 Selling Price (per unit) 125 Hiring Cost (per new employee) 1000 Lay off cost (per laid off employee) 2000 Starting Inventory 5000 Desired Ending Inventory 5000 Overtime Limit (number of units produced per employee) 10 Production Capacity (per employee per month) 80 units Starting Workforce 250 workers Labour hours per unit 2 MAX overtime per month per employee, HOURS 20 of working days per month 20 of hours per day 8 3 4 PERIOD 5 0 (Starting) 6 7 8 9 A 10 11 12 13 14 15 16 1 2 3 35 Total Cost 36 Total Revenue 37 Total Profit 4 5 6 7 8 9 10 11 12 17 18 19 Related Costs 20 Period 21 22 23 24 25 26 27 28 29 30 31 32 33 Total over all periods 34 B D E C of employees In Units Ht ( Hired) Lt ( Laid off) Wt (Workforce Ot (Overtime It (Inventory Ct (Subcontracted Pt (Production Demand Max OT Available Production Capacit Actual Sales 250 5000 F G H 10000 11000 16000 19000 22000 25000 31000 28000 22000 19000 13000 12000 K Ht ( Hired) Lt ( Laid off) Wt (Workforce) Ot (Overtime It (Inventory Ct (Subcontracted Pt (Production Actual Sales 1 2 3 4 5 6 7 8 9 10 11 12

Question: use the info. below and Linear programming, to come up with the most optimal aggregate Production plan via EXCEL. you may need to set up

use the info. below and Linear programming, to come up with the most optimal aggregate Production plan via EXCEL.

you may need to set up constraints

Costs
Regular Time Labour	15
Overtime Labour	22
Subcontracting cost (per unit)	74
Inventory carrying cost (per unit per month)	3
Material cost (per unit)	40
Selling Price (per unit)	125
Hiring Cost (per new employee)	1000
Lay-off cost (per laid off employee)	2000

Starting Inventory	5000
Desired Ending Inventory	5000
Overtime Limit (number of units produced per employee)	10
Production Capacity (per employee per month)	80 units
Starting Workforce	250 workers

Labour hours per unit	2
MAX overtime per month per employee, *HOURS*	20
# of working days per month	20
# of hours per day	8

4 PERIOD 5 0 (Starting) 6B DH # of employees In Units Ht (#Hired) Lt (# Laid off Wt (Workforce Ot (Overtime It (Inventory C

3 4 PERIOD 5 0 (Starting) 6 7 8 9 A 10 11 12 13 14 15 16 1 2 3 35 Total Cost 36 Total Revenue 37 Total Profit 4 5 6 7 8 9 10 11 12 17 18 19 Related Costs 20 Period 21 22 23 24 25 26 27 28 29 30 31 32 33 Total over all periods 34 B D E C # of employees In Units Ht (#Hired) Lt (# Laid off) Wt (Workforce Ot (Overtime It (Inventory Ct (Subcontracted Pt (Production Demand Max OT Available Production Capacit Actual Sales 250 5000 F G H 10000 11000 16000 19000 22000 25000 31000 28000 22000 19000 13000 12000 K Ht (#Hired) Lt (# Laid off) Wt (Workforce) Ot (Overtime It (Inventory Ct (Subcontracted Pt (Production Actual Sales 1 2 3 4 5 6 7 8 9 10 11 12

Step by Step Solution

★★★★★

3.37 Rating (163 Votes )

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

In order to create the most optimal aggregate production plan using the information provided and the linear programming approach in Excel several step... View full answer

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Organizational Behavior Questions!

1. Use the internet to come up with an example of a highly leveraged firm and a lowly leveraged firm. Include the firms name, their total assets, total liabilities, and total equity according to...

Suppose that the total number of units produced by a worker in t hours of an 8-hour shift can be modeled by the production function P(t): P(t) = 27t +12t2 - t3 (a)Find the number of hours before...

As a chief engineer, you need to come up with the cash flow estimate for a newly proposed production line. Initially, the system is designed to have a maximum capacity (C max )of six million parts to...

The data in Table P-13 are closing stock quotations for the DEF Corporation for 150 days. Determine the appropriate ARIMA model, and forecast the stock price five days ahead from forecast origin t =...

The age distribution for the employees of a highly successful "dot-com" company headquartered in Atlanta is shown in the next table. An employee is to be randomly selected from this population. a....

The raw data for Problem 22, from Freund (1979), appear below. For the model of Problem 22, a. Fit the model. b. What discrepancies do you note between the results of (a) and the data summary...

Software Development Inc. (SDI) produces and markets software for personal computers, including spreadsheet, word processing, desktop publishing, and database management programs. SDI has annual...

The Great North Woods Clothing Company is a mail-order company that processes thousands of mail and telephone orders each week. They have a customer service number to handle customer order problems,...

Refer to Exercise 13.76. a. Use a variable selection program to obtain the best four models of all possible sizes using R2adj as your criterion. Obtain values for R2, MSE, and Cp for each of the...

Consider the following calendar: Miller's Department Store commenced business on September 1. It is open every day except Sunday. Its total payroll for all employees is $6,000 per day. Payments are...

Use a computer to graph ph z and |z| . Discuss what you observe.

Complete the table (show calculations): Each value is in BTU. AKE Q Process 1 1. AU 2. Process 2 Determine whether the process is a power or refrigeration cycle. 0 200 -100 50 Which of the two...

A 7 0 - year - old male experience his chest pain and is found to have blockage in one of his arteries. What is the most likely diagnosis?

You have a room that is 4 m long, 7 m wide, and has a ceiling height of 3 m. The floor is hardwood, = .15 at 500 Hz. The walls are gypsum wallboard (GWB), = .30 at 500 Hz. The ceiling is acoustic...

A company's debt is given by a bond that will mature in two years. After two years the company will terminate all activity. The company unlevered equity value in two years can be $17 millions with a...

Create cause and affect fish bone diagram The Saber acquisition project is well underway. The team leads for the 3.0 Administration work stream has come to you with an issue that will require...

Quincy Corp. began operations on January 1, 20x1. The corporate charter authorizes the issuance of 500,000 shares of $10 par common stock and 100,000 shares of $50 par, 5% preferred stock.

1. What type of contact is between the Ridpath Sandstone and the Kavalier Shale? How did you determine this? (Refer to the Geologic Time Scale, which is Figure 1 in the Geologic Dating lab, to help...

The identification of national objectives for students and the creation of tests to measure whether students were meeting those objectives was the basis for the creation of (choices: the Common Core...

Note : This program will let you practice on exceptions and I/O in Java. A Chinese restaurant wants to have a computer-based search system that will display a recipe for each dish. The owner of the...

ACCT1002 Introduction to Financial Accounting Ben and Eric are opening a comic bookstore to be registered as E&B Comic Bookstore Co. There is no other competing comic bookstore in the area. Their...

Ben and Eric are opening a comic bookstore to be registered as E&B Comic Bookstore Co. There is no other competing comic bookstore in the area. Their fundamental decision is how to organize the...

A Chinese restaurant wants to have a computer-based search system that will display a recipe for each dish. The owner of the restaurant wants the system to be flexible in that its recipes are stored...

Question 4 needs to be answered using the totals with the initials "E". Thank you Scenario: Karen and Yanique are opening a jewellery store with no competition in the area from which they intend to...

1 a. The owners are desirous of comparing serval financial transactions and possible outcomes to assist in guiding their decision-making process. They assume that the company will be formed on...