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. 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 the creation of formulas moving forward. I. Open e$06$c$1$DirectMarketing and save it as e$06$c$1$DirectMarketing$\_$LastFirst. $2.$ Create appropriate range names for Design Fee $($cell B$8),$ Cost per Ad $($cell B$9),$ Total Clicks $($cell B$10),$ Profit Per Click $($B$11),$ and Gross Profit $($cell B$12).3.$ Edit the existing name range Design$\_$Fee to reflect the current year. Example: Design$\_$Fee$2021.4.$ Use the newly created range names to create a formula to calculate Gross Profit $($cell B$12)$ and Net Profit $($cell B$13).$ 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 A$1,$ and then resize the columns as needed for proper display. 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. $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$\backslash$&A worksheet. 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\mathrm{.}5\%$ to complete the task. $10.$ Complete the one$-$variable data table and format the results with Accounting Number Format with no decimal places. I I. 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$\backslash$&A worksheet. Save the workbook. Two$-$Variable Data Table 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. $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 J$3.$ Complete the series of substitution values from $10000$ to $40000$ at $5000$ increments. $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$\backslash$&A worksheet. Save the workbook. Scenario Manager 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 scenario: $40000,$ and $6\mathrm{.}5\%.20.$ Create a second scenario named Worst Case, using the same changing cells. Enter these values for the