$3$ Using absolute cell references as necessary, in cell G$4,$ construct a formula to calculate the percent that the Texas Total is of Total Sales, and then apply Percent Style. Fill the formula down through the range G$5$:G$8.$

$4$ In the range $H4$:$H8,$ insert Line sparklines to represent the trend of each state across the four quarters. Do not include the totals. Add Markers and then in the first row, apply the first Sparkline style.

$5$

$4$

$5$ Select the range $A3$:$E8,$ and then use the Recommended Charts command to suggest an appropriate chart. Click the first Clustered Column chart that uses the state names as the category axis. Align the upper left corner of the chart inside the upper left corner of cell A$11,$ and then size the chart so that its lower right corner is slightly inside cell H$24.$ Apply chart Style $6.$ As the chart title, type Quarterly Net Sales by State

$6$ To show the percent that each state contributes to the total sales, select the nonadjacent ranges that represent the state names and state totals $($in column F$).$ Insert a $3-$D Pie chart, and then move the chart to a New sheet. Name the sheet Net Sales Chart.

$7$ Change the Chart Title to Annual Net Sales by State and then change the chart title Font Size to $32.$ Remove the Legend from the chart, and then add Data Labels that display only the Category Name and Percentage positioned in the Center. Change the data labels Font Size to $14$ and apply Bold and Italic. Change the Font color to White, Background $1.$

$8$ Select the entire pie and display the Format Data Series pane. From the $3-$D Format gallery, modify the $3-$D options by changing the Top bevel and Bottom bevel to the first bevel option in the first row. Set all of the Width and Height boxes to $256$ and then change the Material to the fourth Standard type$-$Metal.

$9$ Insert a Custom Footer with the File name in the left section and then save your workbook.

$10$ On the Portland Inventory worksheet, in cell B$5,$ enter a function that will display the average retail price. In cell B$6,$ enter a function that will display the median retail price. In cell B$7,$ enter a function that will calculate the lowest retail price. In cell B$8,$ enter a function that will calculate the highest retail price. Format the range B$5$:B$8$ with Accounting Number Format.

$11$ On the Portland Inventory worksheet, in cell G$14,$ enter an IF function to determine the items to be ordered. If the Quantity in Stock is less than $50,$ then the cell should display Order. If not, then the cell should display OK$.$ Copy the function down through cell G$19.$