Product Region Unit Cost Sales Volume Price per Unit Fixed Costs Variable Costs $(\%)$ Desired Profit Margin $(\%)$

Product A North $201005050001020$

Product A South $301506070001525$

Product A East $252005555001222$

Product A West $222505260001120$

Product B North $211205151001021$

Product B South $311606171001526$

Product B East $262105656001323$

Product B West $232605361001221$

Product C North $201105050001020$

Product C South $301406070001425$

Product C East $251905555001222$

Product C West $222405260001120$

Product D North $211305151001021$

Product D South $311706171001526$

Product D East $262205656001323$

Product D West $232705361001221$

Product E North $301506070001525$

Product E South $252005555001222$

Product E East $222505260001120$

Product E West $211205151001021$ Assignment $4$: What$-$if Analysis and Solver Techniques

You are provided with a dataset on the sales of a set of products in a set of regions. You need to create a Assignment$4\_[$YOURNAME$].$xlsx file and Assignment$4\_[$YOURNAME$].$docx file for your hand out.

In addition to the dataset, these are the columns you would need to use in your exercise:

$1.$ Total Cost:

$$ Formula: $=($Unit Cost $*$ Sales Volume$)+($Variable Costs $\%*$ Sales Revenue$)+$ Fixed Costs

$2.$ Sales Revenue:

$$ Formula: $=$ Sales Volume $*$ Price per Unit

$3.$ Profit:

$$ Formula: $=$ Sales Revenue $-$ Total Cost

$4.$ Profit Margin $(\%)$:

$$ Formula: $=($Profit $/$ Sales Revenue$)*100$

$5.$ Break$-$Even Point:

$$ Formula: $=$ Fixed Costs $/($Price per Unit $-$ Variable Cost per Unit$)$

Your tasks:

Complete the following tasks using the provided dataset.

$1.$ Product Cost Efficiency Improvement

$$ Objective: Reduce the total cost of Product A in the West region while maintaining current sales volume.

$$ Constraints:

$$ Unit cost reduction should not exceed $5\%.$

$$ Fixed costs can be adjusted within a $10\%$ range.

$$ Hints:

$$ Use What$-$if Analysis to experiment with different levels of unit cost and fixed cost reductions.

$$ Calculate the impact of these adjustments on total cost and profit margin.

$2.$ Price Optimization for Increased Profitability

$$ Objective: Find the optimal price per unit for Product B in the South region to maximize profit.

$$ Constraints:

$$ The price increase should not exceed $15\%$ of the current price.

$$ Sales volume is expected to decrease by $5\%$ for every $5\%$ increase in price.

$$ Hints:

$$ Apply the Solver tool to adjust the price per unit.

$$ Analyze how changes in price affect both sales volume and overall profitability.

$$

$3.$ Resource Allocation for Optimizing unit costs for enhancing profit margins

$$ Objective

$$ Use the allocated budget to reduce the unit costs of each product strategically, thereby increasing the overall profit margin.

$$ Constraints

$$ The total allocated budget for all products must not exceed $$10,000.$

$$ The reduction in unit costs should not lead to a decrease in sales volume for any product.

$$ The budget allocation should result in an increased profit margin for each product.

$$ Hints

$$ Use the Solver tool to determine the optimal allocation of the budget that maximizes the total profit margin across all products.

$$ Apply What$-$If Analysis to explore how varying levels of budget allocation to different products affect their profit margins.

$$ Ensure that the Solver's constraint for the total budget does not exceed the available amount and that sales volumes are maintained or increased.