You are the digital marketing director for High West Fashions, a regional clothing company that specializes...

 

  

Transcribed Image Text:

You are the digital marketing director for High West Fashions, a regional clothing company that specializes in custom t-shirts. Your company has decided to launch an online advertising cam- paign that enables customers to purchase heavily discounted products. You have the task of determining the optimal amount of advertising to purchase in order to maximize profit and most effectively utilize resources. 10. Complete the one-variable data table and format the results with Accounting Number Format with no decimal places. II. Apply Custom number formats to make the formula references display as descriptive column headings. Bold and center the headings and substitution values. 12. Answer question 2 on the Q&A worksheet. Save the workbook. Range Names Before using Excel's Analysis tools to help complete your fore- casts, you will create range names for key input cells to simplify Two-Variable Data Table the creation of formulas moving forward. 1. Open e06c1DirectMarketing and save it as e06c1DirectMarketing LastFirst. 2. Create appropriate range names for Design Fee (cell B8), Cost per Ad (cell B9), Total Clicks (cell B10). Profit Per Click (B11), and Gross Profit (cell B12). You are considering increasing the number of ads purchased to raise net profit. You are also interested in how net profit will change if the response rate changes as well as the total number of ads purchased. You will create a two-variable data table to complete the task. 3. Edit the existing name range Design_Fee to reflect the current year. Example: Design_Fee2021. 4. Use the newly created range names to create a formula to calculate Gross Profit (cell B12) and Net Profit 13. Copy the response rate substitution values from the one-variable data table and paste the values starting in cell 14. 14. Type 10000 in cell J3. Complete the series of substitution values from 10000 to 40000 at 5000 increments. (cell B13). Gross profit is the product of total clicks and profit per click. Net profit is Gross profit - ad design fee - total clicks * cost per ad design. 5. Create a new worksheet labeled Range Names, paste the newly created range name information in cell Al. and then resize the columns as needed for proper display. 15. Enter the reference to net profit formula in the correct location for a two-variable data table. 16. Complete the two-variable data table and format the results with Accounting Number Format with no decimal places. 17. Apply a Custom number format to make the formula reference display as a descriptive column heading. Bold and center the heading and substitution values. 18. Answer questions 3 and 4 on the Q&A worksheet. Save the workbook. Goal Seek Currently, you estimate that if 10000 ads are purchased with a 5% response rate, you will earn $3,125. Your next task is to deter- mine the optimal click response rate to earn a $5.000 net profit. Scenario Manager 6. Use Goal Seek to determine the optimal click rate in order to earn a $5.000 net profit. 7. Enter the values in the Q&A worksheet. Up to this point. you have created forecasts based on static amounts: however, it is important to plan for both positive and negative outcomes. To help you analyze best, worst, and most likely outcomes, you will use Scenario Manager. 19. Create a scenario named Best Case, using Number of Ads and Click Rate. Enter these values for the One-Variable Data Table Your initial forecast includes 10000 ads with a 5% response rate. You want to evaluate the change in gross profit and net profit as the click response rate changes. You will create a one-variable data table using substitution values from 2% to 6.5% to complete the task. scenario: 40000. and 6.5%. 20. Create a second scenario named Worst Case, using the same changing cells. Enter these values for the to raise net proIL. You are also interested in hoW net proit will change if the response rate changes as well as the total number of ads purchased. You will create a two-variable data table to complete the task. Start in cell E4. Complete the series of substitution values ranging from 2% to 6.5% at increments of 50% vertically down column E. Format the range E4:E13 with percentage number format and two decimal points. 9. Enter references to the Gross Profit and Net Profit scenario: 10000, and 1%. 21. Create a third scenario named Most Likely, using the sume changing cells. Enter these values for the scenario: 10000, and 6.83%. 22. Generate a Scenario Summary report using Gross Profit and Net Profit. in the correct location for a one-variable data table. Direct Marketing Inputs Click Rate Number of Ads 10000 Click Rate 5.00% Parameters Design Fee $ 2,000.00 Cost Per Ad $ 2.25 Total Clicks 500 Profit Per Click 12.50 Gross Profit Net Profit You are the digital marketing director for High West Fashions, a regional clothing company that specializes in custom t-shirts. Your company has decided to launch an online advertising cam- paign that enables customers to purchase heavily discounted products. You have the task of determining the optimal amount of advertising to purchase in order to maximize profit and most effectively utilize resources. 10. Complete the one-variable data table and format the results with Accounting Number Format with no decimal places. II. Apply Custom number formats to make the formula references display as descriptive column headings. Bold and center the headings and substitution values. 12. Answer question 2 on the Q&A worksheet. Save the workbook. Range Names Before using Excel's Analysis tools to help complete your fore- casts, you will create range names for key input cells to simplify Two-Variable Data Table the creation of formulas moving forward. 1. Open e06c1DirectMarketing and save it as e06c1DirectMarketing LastFirst. 2. Create appropriate range names for Design Fee (cell B8), Cost per Ad (cell B9), Total Clicks (cell B10). Profit Per Click (B11), and Gross Profit (cell B12). You are considering increasing the number of ads purchased to raise net profit. You are also interested in how net profit will change if the response rate changes as well as the total number of ads purchased. You will create a two-variable data table to complete the task. 3. Edit the existing name range Design_Fee to reflect the current year. Example: Design_Fee2021. 4. Use the newly created range names to create a formula to calculate Gross Profit (cell B12) and Net Profit 13. Copy the response rate substitution values from the one-variable data table and paste the values starting in cell 14. 14. Type 10000 in cell J3. Complete the series of substitution values from 10000 to 40000 at 5000 increments. (cell B13). Gross profit is the product of total clicks and profit per click. Net profit is Gross profit - ad design fee - total clicks * cost per ad design. 5. Create a new worksheet labeled Range Names, paste the newly created range name information in cell Al. and then resize the columns as needed for proper display. 15. Enter the reference to net profit formula in the correct location for a two-variable data table. 16. Complete the two-variable data table and format the results with Accounting Number Format with no decimal places. 17. Apply a Custom number format to make the formula reference display as a descriptive column heading. Bold and center the heading and substitution values. 18. Answer questions 3 and 4 on the Q&A worksheet. Save the workbook. Goal Seek Currently, you estimate that if 10000 ads are purchased with a 5% response rate, you will earn $3,125. Your next task is to deter- mine the optimal click response rate to earn a $5.000 net profit. Scenario Manager 6. Use Goal Seek to determine the optimal click rate in order to earn a $5.000 net profit. 7. Enter the values in the Q&A worksheet. Up to this point. you have created forecasts based on static amounts: however, it is important to plan for both positive and negative outcomes. To help you analyze best, worst, and most likely outcomes, you will use Scenario Manager. 19. Create a scenario named Best Case, using Number of Ads and Click Rate. Enter these values for the One-Variable Data Table Your initial forecast includes 10000 ads with a 5% response rate. You want to evaluate the change in gross profit and net profit as the click response rate changes. You will create a one-variable data table using substitution values from 2% to 6.5% to complete the task. scenario: 40000. and 6.5%. 20. Create a second scenario named Worst Case, using the same changing cells. Enter these values for the to raise net proIL. You are also interested in hoW net proit will change if the response rate changes as well as the total number of ads purchased. You will create a two-variable data table to complete the task. Start in cell E4. Complete the series of substitution values ranging from 2% to 6.5% at increments of 50% vertically down column E. Format the range E4:E13 with percentage number format and two decimal points. 9. Enter references to the Gross Profit and Net Profit scenario: 10000, and 1%. 21. Create a third scenario named Most Likely, using the sume changing cells. Enter these values for the scenario: 10000, and 6.83%. 22. Generate a Scenario Summary report using Gross Profit and Net Profit. in the correct location for a one-variable data table. Direct Marketing Inputs Click Rate Number of Ads 10000 Click Rate 5.00% Parameters Design Fee $ 2,000.00 Cost Per Ad $ 2.25 Total Clicks 500 Profit Per Click 12.50 Gross Profit Net Profit

Answer rating: 100% (QA)

1 Open the given sheet as per requirement 2 Perform below steps a Select Cell B8 and manual type the range name DesignFee as below b Select B9 and typ... View the full answer