Explore BrainMass

Optimization using excel solver

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

Question 6: 15 points
The biggest inventory problem at the Barko facility is the storage of boom sections for their various Knuckleboom models. There are two types of boom sections: short and long. The following table outlines the demand in the next 5 months and the projected purchase price for each type of boom section.

Demand May June July August September
Short booms 35 30 20 29 16
Long booms 37 11 16 33 10
Storage capacity 50 46 46 42 42
Purchase price
Short booms $850 $800 $900 $950 $900
Long booms $1,050 $1,100 $1,200 $1,200 $1,150

Due to the current storage rack system, each type of boom consumes the same amount of storage capacity and they can store up to 35. The carrying cost is 2.5% of the purchase price for that month. Currently there are 15 short booms and 18 long booms on hand. They can order up to 40 of each type each month. Create an Excel model to determine the optimum ordering strategy.

Question 7: 10 points
Pettibone currently has 3 people answering phones in their customer call center. The firm has been receiving complaints that customers have been waiting too long before speaking to a real person. Based on phone system data the following table describes the calls received per hour during certain times of the day.

Average calls per hour
8:00 AM 11:00 AM 34
11:00 AM 12:00 AM 41
12:00 AM 2:00 PM 33
2:00 PM 5:00 PM 27

Currently calls take an average of 5 minutes to handle each call. Actual service times are exponentially distributed and arrivals rates follow a Poisson distribution. Management wants customers to wait no longer than 3 minutes, on average, before they speak to a real person. Analyze the possible system improvements listed below (they can be combined) and make a single recommendation to management. Include the recommendation and data backing up this recommendation as a summary sheet in your Excel spreadsheet, not an additional excel file.

1. Automated phone system reduces arrivals by 20% - cost $2 million
2. Additional operators at a cost of $55,000 per year
3. Installation of additional software tools (decrease service time by 1 minute) - cost $1.5 million

© BrainMass Inc. brainmass.com October 25, 2018, 9:37 am ad1c9bdddf


Solution Summary

This solution answers the question asked.The solutions are solved in excel.

See Also This Related BrainMass Solution

Optimization Modeling In Excel (using Solver)

A marketing research group needs to contact at least 150 wives, 120 husbands, 100 single adult males, and 110 single adult females. It costs $2 to make a daytime call and (b/c of higher labor costs) $5 to make an evening call. Because of the limited staff, at most half of all phone calls can be evening calls. Determine how to minimize the cost of completing the survey.

Table lists the results that can be expected. For example, 30% of all daytime calls are answered by a wife and 15%of all evening calls are answered by a single male.

DATA for Problem
Person Responding % of Daytime Calls % of Evening Calls
Wife 30 30
Husband 10 30
Single male 10 15
Single female 10 20
None 40 5

Need the optimal solution only.
Please explain the results in plain, simple english - no technical jargon please. Thank you.

View Full Posting Details