You manage an electronics manufacturing company that produces three types of electronic devices: Smartphones, Tablets, and...

Transcribed Image Text:

You manage an electronics manufacturing company that produces three types of electronic devices: Smartphones, Tablets, and Laptops. . . ● Producing 1 Phone requires 4 minutes of assembly time and 2 minutes of testing time. Producing 1 Tablet requires 2 minutes of assembly time and 2 minutes of testing time. Producing 1 Laptop requires 4 minutes of assembly time and 3 minutes of testing time. The assembly department operates for 3 shifts a day, 7 days per week, while the testing department operates for 2 shifts a day, 7 days per week. Although each shift is nominally 8 hours, because of breaks, you plan on 7 working hours per shift, with each working hour comprised of 60 minutes usable for the assembly and test operations. The revenue per unit for phones, tablets, and laptops are $700, $500, and $1,000 respectively. The raw material cost per unit for phones, tablets, and laptops are $160, $100, and $300 respectively. The gross margin of each product is its revenue minus its raw material cost. For example, the gross margin of a tablet is $500-100-$400. Your objective is to maximize the total gross margin from all products sold. The weekly demand for phones, tablets, and laptops are 800, 700, and 1,200 units respectively. The cost of all raw materials purchased during a week cannot exceed the budget of half a million dollars. A. Create a spreadsheet model in standard LP form and use Solver to determine the optimal weekly production of the three electronics items to maximize the total gross margin. Although the quantities do not need to be integer, round them to integers in your spreadsheet. Label your tab as Q1A. B. Write an algebraic formulation of the model. You may imbed your algebraic formulation into your spreadsheet (using tab Q1B) or you may submit it as a separate ms-word file or PDF file. C. You are considering the implementation of a policy that limits the maximum number of laptops produced to be no more than 35% of the total products produced. In tab Q1C, implement this policy. Use Solver to determine the optimal weekly production quantities of the three electronic products with this policy. Calculate and highlight in tab Q1C the cost of this policy. The cost of this policy is the total gross margin determined in tab QIA (without the policy) minus the total gross margin determined in tab Q1C (with the policy). D. Assume for part D that you decided the cost of the 35% policy is too high. Consequently, use the model from tab QIA as a starting point for your model in tab QID. Suppose that the demand for phones will increase by 3 phones for each dollar spent advertising phones. Any money spent on advertising will consume some of the budget presently available for raw material costs. In other words, for Q1D, the half million dollar budget is available for the total of the advertising expenses and raw material costs. Money spent on advertising impacts gross margin both from the cost perspective (cost of advertising) and revenue perspective (if the advertising leads to increased sales). Use Solver to determine the optimal weekly advertising expenditures for phones along with the corresponding optimal production quantities of the three electronic products. You manage an electronics manufacturing company that produces three types of electronic devices: Smartphones, Tablets, and Laptops. . . ● Producing 1 Phone requires 4 minutes of assembly time and 2 minutes of testing time. Producing 1 Tablet requires 2 minutes of assembly time and 2 minutes of testing time. Producing 1 Laptop requires 4 minutes of assembly time and 3 minutes of testing time. The assembly department operates for 3 shifts a day, 7 days per week, while the testing department operates for 2 shifts a day, 7 days per week. Although each shift is nominally 8 hours, because of breaks, you plan on 7 working hours per shift, with each working hour comprised of 60 minutes usable for the assembly and test operations. The revenue per unit for phones, tablets, and laptops are $700, $500, and $1,000 respectively. The raw material cost per unit for phones, tablets, and laptops are $160, $100, and $300 respectively. The gross margin of each product is its revenue minus its raw material cost. For example, the gross margin of a tablet is $500-100-$400. Your objective is to maximize the total gross margin from all products sold. The weekly demand for phones, tablets, and laptops are 800, 700, and 1,200 units respectively. The cost of all raw materials purchased during a week cannot exceed the budget of half a million dollars. A. Create a spreadsheet model in standard LP form and use Solver to determine the optimal weekly production of the three electronics items to maximize the total gross margin. Although the quantities do not need to be integer, round them to integers in your spreadsheet. Label your tab as Q1A. B. Write an algebraic formulation of the model. You may imbed your algebraic formulation into your spreadsheet (using tab Q1B) or you may submit it as a separate ms-word file or PDF file. C. You are considering the implementation of a policy that limits the maximum number of laptops produced to be no more than 35% of the total products produced. In tab Q1C, implement this policy. Use Solver to determine the optimal weekly production quantities of the three electronic products with this policy. Calculate and highlight in tab Q1C the cost of this policy. The cost of this policy is the total gross margin determined in tab QIA (without the policy) minus the total gross margin determined in tab Q1C (with the policy). D. Assume for part D that you decided the cost of the 35% policy is too high. Consequently, use the model from tab QIA as a starting point for your model in tab QID. Suppose that the demand for phones will increase by 3 phones for each dollar spent advertising phones. Any money spent on advertising will consume some of the budget presently available for raw material costs. In other words, for Q1D, the half million dollar budget is available for the total of the advertising expenses and raw material costs. Money spent on advertising impacts gross margin both from the cost perspective (cost of advertising) and revenue perspective (if the advertising leads to increased sales). Use Solver to determine the optimal weekly advertising expenditures for phones along with the corresponding optimal production quantities of the three electronic products.