Answered step by step
Verified Expert Solution
Question
1 Approved Answer
excel spreadsheet Part 1: Goal Seek worksheet in assignment file includes 3 products: desktop, laptop, and tablet. Base price and COGS and other variable expenses
excel spreadsheet
Part 1: Goal Seek worksheet in assignment file includes 3 products: desktop, laptop, and tablet. Base price and "COGS and other variable expenses" for each unit of these products are listed Other highlighted cells are reserved for the following: Units sold: Enter 25 for desktop, 82 for laptop, and 112 for tablet. Discount: Enter initial values of 10% for all 3 products Revenue: Multiply units of each product sold by its base price and apply the discount. Margin: The difference of revenue and "COGS and other Margin: The difference of revenue and "COGS and other variable Expenses" for each product. Marketing: Enter $1500 for marketing cost. Total Margin: Subtract marketing cost from the sum of all 3 product margins. Total Margin: Subtract marketing cost from the sum of all 3 product margins. Your goal is to stay competitive but make the minimum target margin. Use Excel Goal Seek feature to obtain the following results: - Change the discount percentage for desktop to achieve a $25,000 margin. Click OK in the Goal Seek status pop-up to save the results. - After saving the previous status change the laptop discount percentage to achieve a $30,000 margin. Click OK in the Goal Seek status pop-up to save the results. - After saving the previous status change the tablet discount percentage to achieve a $35,000 margin. Click OK in the Goal Seek status pop-up to save the results. Part 2: Copy your results from part 1 into a new worksheet or create a worksheet copy as the base information for part 2 . The store is planning on simulating sales for a certain number of each product under different prices, costs, and discounts, then compare the results of different scenarios. Scenario1: Selling 500 desktops at 20\% discount, 1000 laptops at a 10% discount, and 1500 tablets at a 5% discount for all products. Base prices are $690,$950, and $650 respectively. "COGS and other variable costs" are $440 for desktop, $715 for laptop, and $452 for tablet. Spend $1,000 for marketing Scenario 2: Selling 500 units of each product, switching to new suppliers that raise the "COGS and other variable expenses" to $490,$749, and $559 for desktop, laptop, and tablet, respectively. Base prices remain the same as option 1 . Spend $4000 on marketing and no discount for any product. Scenario 3: Selling 200 desktops, 400 laptops, and 800 tablets at base prices of $650,$1050, and $345 respectively. "COGS and other variable expenses" are also $500, $896, and $300, respectively. There will be a 10% discount for laptops, but not for desktops and tablets. Marketing expenses are $3,500. Compare these 3 scenarios by creating a scenario summary Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started