IS 101 Excel Project 2

Click cell K7 in the Data worksheet and replace the current contents with an IF function that compares the percent returned for the first book to the return rate in the Input Area. If the percent returned is less than the return rate, the result is $500. Otherwise, the author receives no bonus. The only value you may type directly in the function is 0 where needed. Copy the function you entered from cell K7 to the range K8:K22.

=IF(G7<$H$3,500,0) and copy through K22

In cell I7 in the Data worksheet, insert a formula that calculates the amount of the first author's royalties. An author's royalties are based on the Royalty Rate located in the Input Area and the respective Net Sales. Copy the formula from cell I7 to the range I8:I22.

In cell I7, insert formula =G$2*H7 and copy through I22

In cell K7 in the Data worksheet, insert a formula that adds the first author's royalty amount to the bonus. Copy the formula from cell K7 to the range K8:K22.

In cell K7 insert formula =I7+J7 and copy through K22

Select the values in the Percent Returned column and apply conditional formatting to apply Light Red Fill with Dark Red Text for values that are greater than 9.9%.

select range D2:D17 and on the conditional formatting icon select the "Highlight Cell Rules" then "greater than". type 9.9% then select light red fill with dark red text

Move the chart so that the top-left corner is positioned inside cell A9. Change the chart width to 4.66 inches and the chart height to 2.9 inches.

drag the chart to A9. on the format tab change the width to 4.66 inches and height to 2.9

In cell J2 in the Data worksheet, insert a function to calculate the average net sales. In cell J3 insert a function to calculate the highest net sales. In cell J4 insert a function to calculate the lowest net sales.

cell J2 =AVERAGE(H7:H22) cell J3 =MAX(H7:H22) cell J4 =MIN(H7:H22)

Click the Sales sheet tab, convert the data to a table, and apply Table Style Light 9.

Click on the sales tab (bottom left corner) insert the option table from the "Tables" group. in "create table" select "=$A$1:$F$17". Apply blue style Light 9

Select the range L1:N2 in the Data worksheet, copy the selected data, and transpose the data when pasting it to cell A2. Delete the data in the range L1:N2.

Copy range L1:N2 (CRTL+C) and right click into A2 using "paste special" then select the option "transpose" and click OK

Select the range G7:G22 in the Data worksheet and apply the Percent Style format with one decimal place. Select the range K7:K22 and apply the Accounting Number Format. Merge and center the label Series Legend in the range A1:C1 in the Data worksheet. Apply Thick Outside Borders to the range A1:C4.

G7:G22 should already be in percantage with 1 decimal. K7:K22 go to number filter and select "Accounting". there should be 2 decimal places. A1:c1 click "merge and center" then apply Thick Outside Borders from "border" on the home (under the "bold" button)

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Data, Sales, Net Sales, Series Sales, Office Reference. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed

Make sure your tabs at the bottom are listed in the order according to the directions.

In cell F7 in the Data worksheet, insert a formula that calculates the percentage of books returned based on the number of books returned and the quantity sold. Copy the formula from cell F7 to the range F8:F22

Select cell F7 and insert the formula =E7/D7 copy the formula through to F22. display "Format Cells" to "percentage" and change the decimal place to 1.

Click the Net Sales sheet tab, select the range A3:D7, and create a clustered column chart.

click net sales tab (bottom) select A3:D7 go to insert tab and select charts. Insert "clustsered column chart"

Select Landscape orientation, adjust the scaling so that the data fits on one page, and set 0.1 left and right margins for the Data worksheet.

go ti the Page Layout tab to select landscape otientation. Click on the page Setup button and select "fit to" then adjust to fit one page. Go to margins tab and set 0.1 left and right margins

Select the values in the Net Sales column and apply a filter to display only net sales that are less than $100,000.

go to number filters (the arrow in the corner of "set sales") and select "less than" and in custom autofilter type 100000

Apply the Style 12 chart style and change the colors to Colorful Palette 4. Apply bold and change the font size to 18 for the chart title.

go to the design tab and select style 12. change color to "colorful palette 4" Apply Bold and font size 18 to the title

In cell H7 in the Data worksheet, insert a formula that calculates the net sales. This monetary amount reflects the number of books not returned and the unit price. Copy the formula from cell H7 to the range H8:H22.

in H7 insert the formula =(D7-E7)*G7 and copy through cell H22

Link the chart title to cell A1. Format the value axis to display whole numbers only.

in the chart title enter ='Net Sales'!$A$1:$D$1 right click the value axis and select "format axis" then select "number" (on the bottom). change category to "number" and decimal place to 0

Click cell C6 in the Data worksheet and insert a column. Type Series Name in cell C6. Click cell C7 in the Data worksheet and insert a lookup function that identifies the series code, compares it to the series legend, and then returns the name of the series. Copy the function you entered from cell C7 to the range C8:C22. Change the width of column C to 18.

right click cell C6 then insert entire column. Type "Series Name". insert function to C7 =VLOOKUP(B7,$A$2:$B$4,2,FALSE) and copy through C22. Select the range then select format and column width to change the width to 18

Select the Series Sales tab, select the ranges A4:A7 and C4:C7 and create a pie chart. Move the pie chart to a chart sheet named Office Reference. Move the Office Reference chart sheet to the right of the Series Sales sheet.

select "series sales" (on bottom) and select A4:A7 "CTRL" C4:C7. Go to insert and select pie chart. Right click and select "move chart" type the same "Office Reference" in the "new sheet". Move the Office Reference chart sheet to the right of the Series Sales sheet.

Add a total row to display the sum of the Net Sales column. Change the column width to 14 for the Net Sales column.

select a cell in the table. select deign then "total row". click the down arrow next to the new number and select "SUM". in "format" change column width to 14

Display data labels in the Inside End position. Display Percentage data labels; remove the Value data labels. With the Series 1 Data Labels selected, apply bold, change the font size to 18, and then apply White, Background 1 font color.

select the chart and select the plus symbol (top-right) then select "more options" from data labels. Select "inside end position" then "percentage data labels" and remove the value data lables. Select the series 1 data label on the chart and apply Bold and font size 18.

Format the chart title, value axis, category axis, and legend with Black, Text 1 font color

select the chart title and go to home tab. Click on "black, text 1 font color". Do the same for value axis, category axis, and legend.

Change the chart title to Office Reference Series.

select the pie chart title and change to "Office Reference Series".

Sort the data by Series Name in alphabetical order and then within Series Name, sort by Net Sales from largest to smallest.

stay on sales worksheet and go to home tab. go to "sort and filter" then custom sort. insert "series name" in alphabetical order. Add level and select net sales in largest to smallest