WORKOUT THE PROBLEM AND SHOW WORK EXCEL

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 $4.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 increased by 2 Sales volume increased by 4 3 percent discount on cost of percent percent goods # of units purchased/sold Selling Price/unit Annual Sales Amount Cost of purchase/unit Cost of Goods Sold Gross Profit or Margin Operating Expenses Operating Profit Increased Profit Percentage Change