Format the Worksheet
Your assistant compiled the initial data and saved it in an Excel workbook. However, the column labels are hard to read because the full text does not display. You will use alignment and format options to make it easier to read the labels.
Open eApp_Cap1_Publisher and save it as eApp_Cap1_Publisher_LastFirst.
Select the range A6:K6 on the Data worksheet.
Wrap the text and apply Center alignment to the selected range.
Change the row height to 30.
Insert Formulas and Basic Functions
The Data worksheet contains the quantity of books sold, the number of books returned, and the unit price per book. You want to calculate the percentage of books that were returned from bookstores to your warehouse. Then you will also calculate the net sales, the amount of royalties to pay the authors, and the total author earnings. You want to insert functions to calculate the average, highest, and lowest net sales amounts. Use appropriate relative, absolute, and mixed references correctly in your formulas.
Click cell F7 on the Data worksheet and 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.
Click cell H7 and 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.
Click cell I7 and 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.
Click cell K7 and 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.
Click cell J2 and insert a function to calculate the average net sales.
Click cell J3 and insert a function to calculate the highest net sales.
Click cell J4 and insert a function to calculate the lowest net sales.
Move Data and Insert Functions
The legend that explains the abbreviations for each series would look better in a different location. You will insert a new column in the worksheet and insert a lookup function to display the full series names. Finally, you will replace the bonus with a function that calculates a bonus only if the return rate is less than 10%. Use relative, absolute, and mixed references correctly in your functions.
Select the range L1:N2, copy the selected data, and transpose the data when pasting it to cell A2. Delete the data in the range L1:N2.
Click cell C6 and insert a column. Type Series Name in cell C6.
Click cell C7 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.
Click cell K7 and insert 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.
Format Data
Most of the values were already formatted with Accounting Number Format, and when you inserted functions in the Net Sales area, Excel formatted the values for you because the source values were already formatted. However, you want to format the values in the Percent Returned and Bonus columns. In addition, you want to format the Series legend to match the other ranges at the top of the worksheet. You will merge and center the label and apply a border around the range.
Select the range G7:G22 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.
Apply Thick Outside Borders to the range A1:C4.
Select Page Setup Options
Currently, the worksheet data would not fit on one printed page. You will change the orientation, scaling, and margins so that the data would fit on one page if you decide to print the worksheet.
Select Landscape orientation.
Adjust the scaling so that the data fits on one page.
Set 0.1" left and right margins.
Insert a Table, Sort and Filter Data, and Apply Conditional Formatting
To preserve the integrity of the original data, you will work with a portion of the dataset in the Sales worksheet. First, you will convert the data to a table and apply a specific table style. Next, you will sort the data in a specific order and display the total net sales by series and within each series with the highest to lowest net sales. Then you will add a total row to display the total net sales. Finally, you want to apply a conditional format to focus on the book titles where 10% or more of the books were returned and then apply a filter to focus on the books with the lowest net sales.
Click the Sales sheet tab and convert the data to a table.
Apply Table Style Light 9.
Sort the data by Series Name in alphabetical order and then within Series Name, sort by Net Sales from largest to smallest.
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 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 the values in the Net Sales column and apply a filter to display only net sales that are less than $100,000.
Create a Column Chart
The Net Sales worksheet contains net sales organized by software and series. You will create a clustered column chart to compare the software sales across the series.
Click the Net Sales sheet tab.
Select the range A3:D7 and create a clustered column chart.
Move the chart so that the top-left corner covers cell A9. Change the chart width to 4.66" and the chart height to 2.9".
Link the chart title to cell A1.
Format the value axis to display whole numbers only.
Format the chart title, value axis, category axis, and legend with Black, Text 1 font color.
Create a Pie Chart
The Series Sales worksheet contains net sales organized by software and series. You will create a pie chart to determine the percentage of sales for each book within the Office Reference series.
Click the Series Sales sheet 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.
Change the chart title to Office Reference Series. Apply bold and change the font size to 18 for the chart title.
Apply the Style 12 chart style and change the colors to Color 4.
Display data labels in the Inside End position. Display Percentage data labels; remove the Value data labels. Apply bold, change the font size to 18, and then apply White, Background 1 font color to the data labels.
Apply these fill colors: Excel data point Green, Access data point Purple, PowerPoint data point Orange, Accent 2.
Finish the Project
You want to insert a footer on each sheet.
Group the Data, Sales, Net Sales, and Series Sales sheet tabs.
Create a footer with your name on the left side, the sheet tab code in the center, and the file name code on the right side of each sheet.
Click the Office Reference chart sheet and create a footer with your name on the left side, the sheet tab code in the center, and the file name code on the right side.
Save and close the file. Based on your instructor's directions, submit eApp_Cap1_Publisher_LastFirst.
Attachment:- assignment.rar