Assignment_4
docx
keyboard_arrow_up
School
Conestoga College *
*We aren’t endorsed by this school
Course
8002
Subject
Industrial Engineering
Date
Jan 9, 2024
Type
docx
Pages
3
Uploaded by DukeKoalaPerson1041
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
Assignment4_[YOURNAME].xlsx file and Assignment4_[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. Break-Even Analysis for a New Product Launch
Objective
: Determine the break-even point for a new product, Product E, assuming it has similar
costs and pricing as existing products.
Constraints
:
Fixed costs are estimated to be 20% higher than the average of existing products.
Initial sales volume projections are uncertain, vary between 100 to 200 units.
Hints
:
Use What-if Analysis to calculate break-even points at different sales volumes.
Compare the break-even points to assess the financial risk of launching the new product.
4. 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.
Your interpretations and conclusions:
In the .docx file for each of the questions provide your interpretation and conclusions.
Good luck!
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help