1. As a purchasing manager for Affordable Depot, you collaborate with the marketing, accounting, and engineering departments and strategically manage your procurement activities. You purchase air- conditioner smart thermostats from its manufacturer @ $27.50 per unit and sell to customers for $33 per unit. Operating expenses include storage, transportation, marketing, overhead, etc. and add up to S4.50 per unit. According to a reliable forecast, the current annual sales volume is 120,000 thermostats. You have 3 options: a. Increase the selling price by 2 percent (sales volume will decrease by 2 percent), b. increase sales volume by 4 percent by incurring additional marketing expense of $20,000 (everything else remains the same as the base scenario), or c. work with the manufacturer and get a 3 percent discount on the current cost of goods (everything else remains the same as the base scenario). Compute the percent change in profit for each option with respect to the base profit. Which of the three options will you choose and why? Write your decision in words. You are required to create an Excel worksheet and use formulas where applicable. Do not use your calculators. Upload your worksheet on Blackboard as Homework 2. Base Scenario Selling price Sales volume 3 percent increased by 2 increased by discount on percent 4 percent cost of goods # of units 120.000 120,000x.98= Focus search 50% 81 'F Clear worksheet and use formulas where applicable. Do not use your calculators. Upload your worksheet on Blackboard as Homework 2. Base Scenario Selling price Sales volume 3 percent increased by 2 increased by discount on percent 4 percent cost of goods # of units 120,000 120,000x.98= purchased/sold 117,600 Selling $33 33x1.02= 33 33 Price/unit 33.66 Annual Sales Amount 120,000x33= 3,960,000 117600x33.66 =3.958,416 Cost of 27.5 27.5 purchase/unit Cost of Goods 120,000x27.50=117600x27.50= Sold $3,300,000 $3,234.000 Gross Profit or 3960000- 3958416- Margin 3300000= 3234000- 660,000 724.416 Operating 4.5x120000= 4.50x117600- Expenses 540,000 529 200 Operating 660000- 724416- Profit 54,000=120000529200=195216 Increased 195216- Profit 120000=75216 Percentage 75216 Change