Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please help with questions 14 to 24 thank you! Step Instructions 16 17 18 19 20 Make the Direct Marketing 2 worksheet active. Create a
Please help with questions 14 to 24 thank you!
Step Instructions 16 17 18 19 20 Make the Direct Marketing 2 worksheet active. Create a scenario named Best Case, using Number of Ads and click Rate. Enter these values for the scenario: 40000, and 6.5%. Create a second scenario named Worst Case, using the same changing cells. Enter these values for the scenario: 10000, and 1%. Create a third scenario named Most Likely, using the same changing cells. Enter these values for the scenario: 10000, and 6.83%. Generate a scenario summary report using Gross Profit and Net Income. Return to the Direct Marketing 2 worksheet. Load the Solver add-in if it is not already loaded. Launch Solver and set the objective to calculate a net profit of $20,000. Use Number of Ads and click Rate (B4:B5) as changing variable cells. Set a constraint to ensure Number of Ads purchased is less than or equal to 40,000. Set a constraint to ensure Click Rate is less than or equal to 7%. (Mac users should enter the value in decimal form. Example.07) Solve the problem. Generate the Answer Report Create a footer on all worksheets with your name on the left side, the sheet name code in the center, and the file name code on the right side. 21 22 23 24 25 14 Complete the two-variable data table and format the results with Accounting Number Format with two decimal places. Apply a custom number format to make the formula reference appear as Net Profit. 15 Insert = Calibri ~A? 11 abe ht LI General HPA o LO 9X Delete Fm IN Analyze Data Paste BIU += + 40 $ %) , .00 y v Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Analysis F1 X for A B D E F G H 1 J K L M N P Direct Marketing 2.0% 2.5% 3.0% $5,000.00 2.0% 2.5% 3.0% 3.5% 3.5% 2 3 Inputs 4 Number of Ads 10000 5 Click Rate 6.83% 5 7 Parameters 8 Design Fee $ 2,000.00 Cost Per Ad $ 2.25 0 Total Clicks 682.9268293 1 Profit Per Click $ 12.50 2 Gross Profit $ 8,536.59 3 Net Profit $ 5,000.00 4 5 6 7 4.0% 4.5% 5.0% 5.5% 6.0% 6.5% Gross Profit Net Profit $ 2,500.00 $ 50.00 $ 3,125.00 $ 562.50 $ 3,750.00 $1,075.00 $ 4,375.00 $1,587.50 $ 5,000.00 $2,100.00 $ 5,625.00 $2,612.50 $ 6,250.00 $3,125.00 $ 6,875.00 $3,637.50 $ 7,500.00 $4,150.00 $ 8,125.00 $4,662.50 4.0% 4.5% 5.0% 5.5% 6.0% 6.5% 10000 15000 20000 25000 30000 35000 40000 $ 50.00 $1,075.00 $ 2,100.00 $ 3,125.00 $ 4,150.00 $ 5,175.00 $ 6,200.00 $ 562.50 $1,843.75 $ 3,125.00 $ 4,406.25 $ 5,687.50 $ 6,968.75 $ 8,250.00 $1,075.00 $2,612.50 $ 4,150.00 $ 5,687.50 $ 7,225.00 $ 8,762.50 $ 10,300.00 $1,587.50 $3,381.25 $ 5,175.00 $ 6,968.75 $ 8,762.50 $ 10,556.25 $ 12,350.00 $2,100.00 $4,150.00 $ 6,200.00 $ 8,250.00 $ 10,300.00 $ 12,350.00 $14,400.00 $2,612.50 $4,918.75 $ 7,225.00 $ 9,531.25 $11,837.50 $ 14,143.75 $16,450.00 $3,125.00 $5,687.50 $ 8,250.00 $10,812.50 $13,375.00 $ 15,937.50 $ 18,500.00 $3,637.50 $6,456.25 $ 9,275.00 $12,093.75 $ 14,912.50 $ 17,731.25 $ 20,550.00 $4,150.00 $7,225.00 $ 10,300.00 $13,375.00 $16,450.00 $ 19,525.00 $ 22,600.00 $4,662.50 $ 7,993.75 $11,325.00 $14,656.25 $ 17,987.50 $ 21,318.75 $ 24,650.00 8 9 20 1 -2 -3 -4 5 -6 27 Direct Marketing Range Names Direct Marketing 2 + 1 M Cupboard Styles Lens Analysis 13 for =B13 A A B D E F G G H J L M N o P Q R 1 Direct Marketing 2.00% 2.50% 3.00% Net Profit 10000 2.00% $50 2.50% $563 3.00% $1,075 3.50% 2 2 3 Inputs 4 Number o 10000 5 Click Rate 5.00% 6 7 Parameters 8 Design Fe $ 2,000.00 9 Cost Per A $ 2.25 10 Total Click 500 11 Profit Per $ 12.50 12 Gross Prot $ 6,250.00 13 Net Profit $ 3,125.00 14 4.00% 4.50% 5.00% 5.50% 6.00% 6.50% Gross Profit Net Profit $7,666 $4,286 $9,582 $5,857 $11,498 $7,429 $13,415 $9,000 $15,331 $10,571 $17,247 $12,143 $19,164 $13,714 $21,080 $15,286 $22,997 $16,857 $24,913 $18,429 3.50% 4.00% 4.50% 5.00% 5.50% 6.00% 6.50% $1,588 $2,100 $2,613 $3,125 $3,638 $4,150 $4,663 15000 20000 25000 30000 35000 40000 $1,075 $2,100 $3,125 $4,150 $5,175 $6,200 $1,844 $3,125 $4,406 $5,688 $6,969 $8,250 $2,613 $4,150 $5,688 $7,225 $8,763 $10,300 $3,381 $5,175 $6,969 $8,763 $10,556 $12,350 $4,150 $6,200 $8,250 $10,300 $12,350 $14,400 $4,919 $7,225 $9,531 $11,838 $14,144 $16,450 $5,688 $8,250 $10,813 $13,375 $15,938 $18,500 $6,456 $9,275 $12,094 $14,913 $17,731 $20,550 $7,225 $10,300 $13,375 $16,450 $19,525 $22,600 $7,994 $11,325 $14,656 $17,988 $21,319 $24,650 15 16 17 18 19 20 21 22 23 24 25 26 27 Direct Marketing Range Names Direct Marketing 2 Step Instructions 16 17 18 19 20 Make the Direct Marketing 2 worksheet active. Create a scenario named Best Case, using Number of Ads and click Rate. Enter these values for the scenario: 40000, and 6.5%. Create a second scenario named Worst Case, using the same changing cells. Enter these values for the scenario: 10000, and 1%. Create a third scenario named Most Likely, using the same changing cells. Enter these values for the scenario: 10000, and 6.83%. Generate a scenario summary report using Gross Profit and Net Income. Return to the Direct Marketing 2 worksheet. Load the Solver add-in if it is not already loaded. Launch Solver and set the objective to calculate a net profit of $20,000. Use Number of Ads and click Rate (B4:B5) as changing variable cells. Set a constraint to ensure Number of Ads purchased is less than or equal to 40,000. Set a constraint to ensure Click Rate is less than or equal to 7%. (Mac users should enter the value in decimal form. Example.07) Solve the problem. Generate the Answer Report Create a footer on all worksheets with your name on the left side, the sheet name code in the center, and the file name code on the right side. 21 22 23 24 25 14 Complete the two-variable data table and format the results with Accounting Number Format with two decimal places. Apply a custom number format to make the formula reference appear as Net Profit. 15 Insert = Calibri ~A? 11 abe ht LI General HPA o LO 9X Delete Fm IN Analyze Data Paste BIU += + 40 $ %) , .00 y v Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Analysis F1 X for A B D E F G H 1 J K L M N P Direct Marketing 2.0% 2.5% 3.0% $5,000.00 2.0% 2.5% 3.0% 3.5% 3.5% 2 3 Inputs 4 Number of Ads 10000 5 Click Rate 6.83% 5 7 Parameters 8 Design Fee $ 2,000.00 Cost Per Ad $ 2.25 0 Total Clicks 682.9268293 1 Profit Per Click $ 12.50 2 Gross Profit $ 8,536.59 3 Net Profit $ 5,000.00 4 5 6 7 4.0% 4.5% 5.0% 5.5% 6.0% 6.5% Gross Profit Net Profit $ 2,500.00 $ 50.00 $ 3,125.00 $ 562.50 $ 3,750.00 $1,075.00 $ 4,375.00 $1,587.50 $ 5,000.00 $2,100.00 $ 5,625.00 $2,612.50 $ 6,250.00 $3,125.00 $ 6,875.00 $3,637.50 $ 7,500.00 $4,150.00 $ 8,125.00 $4,662.50 4.0% 4.5% 5.0% 5.5% 6.0% 6.5% 10000 15000 20000 25000 30000 35000 40000 $ 50.00 $1,075.00 $ 2,100.00 $ 3,125.00 $ 4,150.00 $ 5,175.00 $ 6,200.00 $ 562.50 $1,843.75 $ 3,125.00 $ 4,406.25 $ 5,687.50 $ 6,968.75 $ 8,250.00 $1,075.00 $2,612.50 $ 4,150.00 $ 5,687.50 $ 7,225.00 $ 8,762.50 $ 10,300.00 $1,587.50 $3,381.25 $ 5,175.00 $ 6,968.75 $ 8,762.50 $ 10,556.25 $ 12,350.00 $2,100.00 $4,150.00 $ 6,200.00 $ 8,250.00 $ 10,300.00 $ 12,350.00 $14,400.00 $2,612.50 $4,918.75 $ 7,225.00 $ 9,531.25 $11,837.50 $ 14,143.75 $16,450.00 $3,125.00 $5,687.50 $ 8,250.00 $10,812.50 $13,375.00 $ 15,937.50 $ 18,500.00 $3,637.50 $6,456.25 $ 9,275.00 $12,093.75 $ 14,912.50 $ 17,731.25 $ 20,550.00 $4,150.00 $7,225.00 $ 10,300.00 $13,375.00 $16,450.00 $ 19,525.00 $ 22,600.00 $4,662.50 $ 7,993.75 $11,325.00 $14,656.25 $ 17,987.50 $ 21,318.75 $ 24,650.00 8 9 20 1 -2 -3 -4 5 -6 27 Direct Marketing Range Names Direct Marketing 2 + 1 M Cupboard Styles Lens Analysis 13 for =B13 A A B D E F G G H J L M N o P Q R 1 Direct Marketing 2.00% 2.50% 3.00% Net Profit 10000 2.00% $50 2.50% $563 3.00% $1,075 3.50% 2 2 3 Inputs 4 Number o 10000 5 Click Rate 5.00% 6 7 Parameters 8 Design Fe $ 2,000.00 9 Cost Per A $ 2.25 10 Total Click 500 11 Profit Per $ 12.50 12 Gross Prot $ 6,250.00 13 Net Profit $ 3,125.00 14 4.00% 4.50% 5.00% 5.50% 6.00% 6.50% Gross Profit Net Profit $7,666 $4,286 $9,582 $5,857 $11,498 $7,429 $13,415 $9,000 $15,331 $10,571 $17,247 $12,143 $19,164 $13,714 $21,080 $15,286 $22,997 $16,857 $24,913 $18,429 3.50% 4.00% 4.50% 5.00% 5.50% 6.00% 6.50% $1,588 $2,100 $2,613 $3,125 $3,638 $4,150 $4,663 15000 20000 25000 30000 35000 40000 $1,075 $2,100 $3,125 $4,150 $5,175 $6,200 $1,844 $3,125 $4,406 $5,688 $6,969 $8,250 $2,613 $4,150 $5,688 $7,225 $8,763 $10,300 $3,381 $5,175 $6,969 $8,763 $10,556 $12,350 $4,150 $6,200 $8,250 $10,300 $12,350 $14,400 $4,919 $7,225 $9,531 $11,838 $14,144 $16,450 $5,688 $8,250 $10,813 $13,375 $15,938 $18,500 $6,456 $9,275 $12,094 $14,913 $17,731 $20,550 $7,225 $10,300 $13,375 $16,450 $19,525 $22,600 $7,994 $11,325 $14,656 $17,988 $21,319 $24,650 15 16 17 18 19 20 21 22 23 24 25 26 27 Direct Marketing Range Names Direct Marketing 2Step 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