Again, use Excel to calculate Unit Profit for each product (in cells B8:I8) and Total Profit (in
Question:
Again, use Excel to calculate Unit Profit for each product (in cells B8:I8) and Total Profit (in cell B19).
From the first sensitivity report (Optimal Solution), find the maximum allowable advertising budget and enter this number into the advertising budget RHS constraint cell C33. This is, in effect, increasing the advertising budget.
Run Excel Solver for this change in the Advertising Trade-off Tab (only) to get another optimal production plan and get another sensitivity report. Analyze the tactical and strategic information provided by the Solver solution and answer the following:
oHow is the objective (profit) impacted by the additional advertising budget as compared to the profit shown in the Optimal Solution Tab? And, what is the impact to the product demand constraints (cells B28 - B31)?
- What is the trade-off between increased advertising expenditures and profit for Hawley Co. - is expending more advertising budget worth the additional profit?
- How would an increase in advertising budget affect the optimal production plan?
- Assume each department claims to require a proportional amount of advertising budget in order to attain the projected sales for each of their products. Calculate the portion of advertising budget for each product as a percentage of total Sales - determine how much advertising budget each product should be allocated. Now, allocate the new advertising budgets for each product proportionally in cells B15 thru E15. Do Not Re-Run Solver! What is the profit impact and are there any issues between the RHS and LHS demand constraints - is this a viable approach?
Expert Answer:
Heres the stepbystep solution using Excel 1 Calculate the Unit Profit for each product in cells B8I8 C3C4C5C6C7 2 Calculate the Total Profit in cell B19 SUMB8I8 3 Find the maximum allowable advertisin... View the full answer
