HELP solve this please, Add all formulas not just answers please. For last page, help fill in the missing areas with formulas

s

1 Assumptions and requirements for Goal Seeking Calculate Gross Profit and Totals in the yellow cells of the table. Use "Goal Seeking worksheet for the following Goal Seek of What-if analysis: a) If you want the gross profit to be $150,000 when the Target Units Sold for products B and C are given (4500 and 2300 units each) and they cannot be changed. Use Goal Seeking feature to find out how many units of Product A you need to sell to reach the $150,000 Total Gross Profit goal. Copy and paste as values your answer (cell D5) to J14 (highlighted in orange) in worksheet "Goal Seek & Scenario Manager". b)1) If you want the gross profit to be $200,000 when the Target Units Sold for products B and Care given (4500 and 2300 units each) and they cannot be changed Use Goal Seeking feature to find out how many units of Product A you need to sell to reach the $200,000 Total Gross Profit goal. Copy and paste as values your answer (cell DS) to J15 (highlighted in orange) in worksheet Goal Seek & Scenario Manager". 2) Create Scenarios using the data and formula in the table for Goal Seek. Create 4 different scenarios using Scenario Manager by changing the product pricing mix in order to determine their impacts to Total Gross Profit and Total Revenue. 1) Original Pricing Mix with no price change. Get Product A target units sold back to original 3,000 if it got changed while completing part 1). 2) Raise the price of Product A by $5.00 but keep prices of other products at original price 3) Raise the price of Product B by $5.00 but keep prices of other products at original price 4) Raise the price of Product C by $5.00 but keep prices of other products at original price To Create Scenario ( screenshots for demonstration only): Select Data / What if/ Scenario Manager/ Add Name the Scenario Select the cells with pricing mix of this scenario in Changing Cells Enter values for each of the changing cells for the pricing mix that described this scenario Add 3 more scenarios. A B D. E F H J K N O P 1 Constraint Target Unit Sold Unit Cost Unit Price Revenue Costs of Good Sold Gross Profit 3000 $40.00 $50.00 $150,000.00 $120,000.00 4500 $30.00 $45.00 $202,500.00 $135,000.00 2300 $25.00 $32.00 $73,600.00 $57,500.00 Units Sold fixed Units Sold fixed 2 3 4 Product Mix 5 Product A 6 Product B 7 Product C 8 Total 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Unit Sold 3,000 Original Product A target units sold is: Target units sold for Product A to achieve $150,000 Total Gross Profit: Target units sold for Product A to achieve $200,000 Total Gross Profit: [ D F H J K E Actual Y1 Unit Sold 3000 4500 Unit Price $50.00 $45.00 $32.00 Units To Be Sold G Projected Y2 Revenue 3300 $165,000.00 4950 $222,750.00 25301 $80.960.00 Costs of Good Sold $132,000.00 $148,500.00 $63.250.00 2300 Scenario 2 15% . B 7 8 Product Mix Unit Cost 9 Product A $40.00 10 Product B $30.00 11 Product C $25.00 12 13 14 Y2 P&L Forecast: 15 16 Total Revenue 17 Total COGS 18 Gross Profit 19 20 21 Operating Expenses 22 Salaries 23 Advertising 24 Miscellaneous 25 Total Operating Expenses 26 27 Earnings Before Taxes 28 Taxes 29 Net Profit $468,710.00 $343,750.00 $124,960.00 Scenario Summary Scenario 1 Increase in Units Sold (%) 10% Advertising as % of Revenue (%) Total Revenue ($) Net Profit ($) Compare Scenarios $70,306.501 $18,748.40 $4,687.10 $93,742.00 $31,218.00 $9,365.40 $21,852.60 20