Help please! I attached the problems that I need help with.

Module 3 Individual Problems M3_IND1. A furniture cabinet maker produces two types of cabinets, Mission and Rustic, that house and hide LCD TVs. The resource requirements and profit for the two types of cabinets are shown below. Model Mission Rustic Resource Requirements and Profitability Materials ($/unit) Labor (hrs./unit) Profit ($/unit) 900 12 300 700 6 200 The firm has a budget of $180,000 to spend on materials. The firm has 1,800 labor hours are available for use. What is the best combination of furniture cabinets to be made? Solve this two decision variable problem using the LP Graphing utility. a) b) c) d) What is the profit (value of the objective function) for the optimal solution? How many Mission models should be produced? How many Rustic models should be produced? Is the production of 100 Mission models and 150 Rustic models feasible (not asking if it is optimal). Does it fall in the feasible region? e) Is the production of 50 Mission models and 150 Rustic models feasible (not asking if it is optimal). Does it fall in the feasible region? 1 M3_IND2. The production department for an aluminum valve plant is scheduling its work for next month. Each valve must go through three separate machines during the fabrication process. After fabrication, each valve is inspected by a human being, who spends 12 minutes per valve. There are 450 inspection hours available for the month. The time required (in hours) by each machine to work on each valve is shown in the following table. Also shown are the minimum number of valves that must be produced for the month and the unit profit for each valve. DEPARTMENT V231 PRODUCTS V242 V784 DRILLING MILLING LATHE MINIMUM OF EACH PRODUCT TYPE NEEDED PROFIT ($/UNIT) 0.60 0.60 1.10 0.30 0.55 0.60 0.45 0.52 0.50 0.35 0.52 0.65 200 300 700 450 $14 $10 $11 $12 V906 CAPACITY OF EACH DEPARTMENT (hours) 700 950 1100 Formulate and solve the problem in Excel to determine the number of each product to manufacture that meets the requirements and maximizes profits. a) What is the maximum profit based on your optimal solution (the value of the objective function)? b) How many V231's should be manufactured based on your optimal solution? c) How many V242's should be manufactured based on your optimal solution? d) How many V784's should be manufactured based on your optimal solution? e) How many V906's should be manufactured based on your optimal solution? f) What is the total number of hours used in the drilling department based on your optimal solution? g) What is the total number of hours used in the milling department based on your optimal solution? h) What is the total number of hours used in the lathe department based on your optimal solution? i) What is the total number of hours used in the inspection department based on your optimal solution? 2 M3_IND3. A snack company packages and sells three different canned party mixes that contain a total of 1 lb. of nuts. These three different products (Plain Nuts, Mixed Nuts, and Premium Mix) include a mix of four possible types of nuts (peanuts, cashews, almonds, and walnuts). The table below show the number of lbs. of each ingredient in each product type, the amount of ingredient available, and the revenue generated by selling each type of product. What should their production plan be to maximize their revenue? There is on additional piece of information that impacts their production plan and should be included in your formulation. Past demand indicates customers purchase at least twice as many cans of Plain Nuts as Mixed Nuts. Your formulation should include a constraint that states that the number of cans of Plain Nuts produced should be at least two times the number of cans of Mixed Nuts produced. Formulate and solve the problem in Excel to determine the number of each product to produce that meets the requirements and maximizes revenues. (Note: Consider this an average amount of cans produced - the number of cans does not need to be an integer). INGREDIENTS PEANUTS (lbs./can) CASHEWS (lbs./can) ALMONDS (lbs./can) WALNUTS (lbs./can) REVENUE ($/UNIT) PRODUCT PLAIN MIXED PREMIUM NUTS NUTS MIX 0.85 0.45 0.15 0.3 0.2 $2.25 INGREDIENT AVAILABILITY (lbs.) 600 300 0.1 0.3 80 0.15 $3.65 0.5 $7.85 100 a) What is the maximum revenue based on your optimal solution (the value of the objective function)? b) How many cans of Plain Nuts should be produced based on your optimal solution? c) How many cans of Mixed Nuts should be produced based on your optimal solution? d) How many cans of Premium Mix should be produced based on your optimal solution? e) After producing the number of cans of each product as suggested in your optimal solution, which of the ingredients has not been totally used by your production plan? 3 M3_IND4. A gear manufacturer is planning next week's production for four types of gears. Because there are limited resources in the plant for production, the manufacturer can outsource the gears by purchasing these gears from a regional supplier. The regional supplier can supply a maximum of 400 units of each type of gear. The table below shows the exact demand for the gears, the revenue per unit, and the outsource cost per unit if the gears are purchased from the supplier. The manufacturer generates the same revenue per unit for the gears regardless of whether the gear is manufactured in their plant and then sold to their customers or outsourced from their supplier and then sold to their customers. GEAR TYPE GEAR A PRODUCT GEAR B GEAR C Demand Revenue 650 $13.75 $9.20 500 $12.50 $9.75 Outsource Cost 450 $16.90 $11.00 GEAR D 550 $18.50 $11.75 When the gears are manufactured in the own plant, the gears must be processed through three different departments: forming, hardening, and deburring. The table below shows the processing time (in hours) for each type of gear in the departments as well as the capacity for each department and the cost per hour for processing the gears in those departments. The cost per hour for processing the gears is provided so that you can calculate the manufacturing cost. PROCESS GEAR A (hrs./unit) GEAR B (hrs./unit) GEAR C (hrs./unit) GEAR D (hrs./unit) Forming Hardening Deburring 0.37 0.30 0.40 0.43 0.25 0.37 0.45 0.31 0.42 0.52 0.40 0.32 DEPARTMENT CAPACITY (hours) 500 400 400 COST ($/hr.) $9.50 $8.75 $7.90 Formulate and solve this problem in Excel to determine the production and/or outsource plan which will meet the requirements and maximize the profit. (Hint: processing costs in the second table effect only the profit for the gears that are manufactured and not the gears that are outsourced) a) How much profit does the company for all gears they make and buy in your solution (the value of the objective function)? (enter to the nearest integer) b) If you could add one hour of capacity to any department to increase profit - adding one hour of capacity to which department would generate the biggest increase in profit: Forming, Hardening, or Deburring? c) Which of the following constraints have slack? (Choose all constraints with slack): Forming, Hardening, or Deburring d) In your solution, how many Gear C's should the company make? e) If the cost per hour of the hardening process increases to $12/hr. - how many Gear D's should the company make with this new process cost? 4 M3_IND5. An investor wishes to invest all of her $13.5 million in a diversified portfolio through a commercial lender. The types of investments, the expected annual interest rate for the investment, and the maximum allowed percentage of the total portfolio that the investment can represent are shown in the table below: INVESTMENT EXPECTED INTEREST Low-income mortgage loans Conventional mortgage loans Government sponsored mortgage loans Bond investments Stock investments Futures trading 7.20% 6.00% MAXIMUM ALLOWED (% of total portfolio) 15% 30% 8.00% 20% 5.45% 8.90% 9.80% 15% 20% 15% She wants at least 30% of her total investment in non-mortgage instruments. Furthermore, she wants no more than 45% of her total investment to be in high-yield and high risk instruments (i.e. expected interest rate of investment is 8% or greater). Formulate and solve this problem in Excel to determine how her money should be diversified in a manner which will meet the requirements and maximize the amount of interest income. (Hint: Make sure that the LHS and RHS of constraints are the same units) a) What is the expected total interest income generated from the investment strategy (the value of the objective function)? b) Based on your solution, how much should be invested in government sponsored mortgage loans? c) Based on your solution, how much should be invested in stock investments? d) If you could increase the maximum allowed for the investments (in order to increase overall return) - which would you choose: conventional mortgage loans, bond investments, or governmental sponsored mortgage loans. e) If the return on low-income mortgage loans was reduced to 5%, how much should be invested in these low-income mortgage loans based on your new solution? 5 M3_IND6. A student project at WCU was initiated to try to determine the impact of implementation of new technologies. The students want to survey both distance and residential undergraduate students in the four different years at Western (first year, sophomore, junior, and senior). They have estimated that it will cost them $5.50 to survey first year and sophomore residential students and $8.00 to survey junior and senior residential students. The cost to interview distance students is slightly higher. It will cost $6.75 for first year and sophomores and $9.50 for junior and seniors. For statistical validity they want to interview at least 900 students. They feel that there are certain criteria that they must adhere to: At least 25% of first year students surveyed should be distance students At least 20% of sophomore students surveyed should be distance students At least 35% of junior students surveyed should be distance students At least 40% of senior students surveyed should be distance students No more than 35% of all the students surveyed should be first year students Juniors and seniors should be at least 45% of the students surveyed Each of the eight types of students must be represented in the survey by at least 10% of the total interviews Formulate and solve this problem in Excel to determine the number of each type of student that should be surveyed that meets the requirements and minimizes the cost to carry out the interviews. a) What is the minimum cost in your optimal solution (the value of the objective function)? b) If the cost of surveying first year and sophomore residential students increases from $5.50 to $7.00 - what is the new minimum cost in your optimal solution? 6