90.2K

Verified Solution

Link Copied!

For this weeks project, you will create a model in Microsoft Excel based on the case below. Use the example model in the Excel file from the Quantitative Analysis presentation for reference! (File will be made available on Tuesday at 5 PM CDT) Case Information: Your client is Apple and they have asked you to help calculate their profitability from AirPods Pro sales. They have provided you with the following information: Demand: The number of AirPods Pros purchased is represented by the following function: Volume Purchased = 30,000,000 - [90,000 * (price)] Costs: Apple produces AirPods Pros in two factories Factory 1 produces 60% of the total volume with a cost of $120 per unit produced (primarily driven by labor costs) Factory 2 produces 40% of the total volume with a cost of $180 per unit produced (primarily driven by labor costs) Apple incurs a procurement cost of $70 per unit to purchase the materials to produce the AirPods Pros Apple incurs a fixed cost of $250,000,000 regardless of how many items are sold (primarily from marketing and general administrative costs) Assignment Tasks: Complete the following items: Build a model in Excel that calculates the profitability of Apples AirPods Pro sales if Apple charges a price of $250 Change your model to calculate the profitability if Apple charges a price of $270 Change your model to calculate the profitability if Apple charges a price of $300 Optional (advanced): use online resources to learn how to use Excels Data Table function to show how the profitability would change from a price of $200 to $300 in increments of $10 (e.g., $200, $210, $220, $230, etc.) Criteria: To successfully execute this project, you should focus on the following: Model Accuracy: ensure you do the math first and your model accurately completes all of the calculations required in the case Dynamic Structure: your model should include a control panel with inputs that can be changed and affect the model calculations Clean Formatting: follow the guidelines provided in the Quantitative Analysis presentation on best practices for Excel model formatting (e.g., labels, lines, font color)

Answer & Explanation Solved by verified expert

See Answer