Best writers. Best papers. Let professionals take care of your academic papers

Order a similar paper and get 15% discount on your first order with us
Use the following coupon "FIRST15"
ORDER NOW

Excel Volume 1

/in /by

Excel Volume 1

EX16XLVOL1GRADERCAPHW_-_Software_Training_Books_17_Instructions.docx
Grader – Instructions Excel 2016 Project

EX16_XL_VOL1_GRADER_CAP_HW – Software Training Books 1.7
Project Description:
You are a vice president for a publisher of software training books. Your division publishes three series that focus on Microsoft Office and Windows. You want to analyze the sales data and calculate author royalties. You will format the worksheet, insert formulas and functions to perform calculations, sort and filter data to review specific book sales, and prepare a chart that compares sales by series.

Steps to Perform:
Step

Instructions

Points Possible

1

Start Excel. Download and open the file named exploring_ecap_grader_h1.xlsx.

0

2

On the Data worksheet, select the range A6:K6, wrap the text, and apply Center alignment. Change the row height to 30 for row 6.

3

3

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.

4

4

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.

4

5

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.

4

6

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.

4

7

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.

6

8

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.

4

9

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.

8

10

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.

5

11

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. Note, the border type may be Thick Box Border, depending on the version of Office used.

6

12

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.

4

13

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

4

14

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

4

15

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

4

16

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%.

3

17

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

4

18

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

4

19

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.

3

20

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

2

21

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

3

22

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.

5

23

Change the chart title to Office Reference Series.

2

24

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.

4

25

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.

6

26

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.

0

Total Points

100

Created On: 07/05/2019 1 EX16_XL_VOL1_GRADER_CAP_HW – Software Training Books 1.7

sparrow_exploring_ecap_grader_h1.xlsx
Data
Series Legend Input Area Net Sales CE OR TB
Royalty Rate: 7.3% Average Certification Series Office Reference True Beginner
Return Rate: 10.0% Highest
Bonus Amount: $ 500.00 Lowest
Author Series Code Software Quantity Sold No. Books Returned Percent Returned Unit Price Net Sales Author Royalties Bonus Author Earnings
Lopez OR Word 2016 8,584 500 $ 49.95 500
Krupin TB Word 2016 1,847 271 $ 25.00 500
Cote CE Word 2016 2,684 400 $ 39.95 500
Yeung OR Excel 2016 11,841 1,042 $ 49.95 500
Tremblay TB Excel 2016 9,475 957 $ 30.00 500
Torres CE Excel 2016 8,443 327 $ 39.95 500
Martin OR Access 2016 8,064 834 $ 49.95 500
Alfero TB Access 2016 3,397 331 $ 30.00 500
Daniels CE Access 2016 3,978 415 $ 34.49 500
Ortiz OR PowerPoint 2016 1,279 120 $ 49.95 500
Wong TB PowerPoint 2016 1,050 184 $ 25.00 500
Kumar CE PowerPoint 2016 2,507 187 $ 34.49 500
Bartalotti TB Outlook 2016 1,884 175 $ 25.00 500
Wallace OR Windows 10 14,750 1,839 $ 49.95 500
Toulou TB Windows 10 8,342 803 $ 25.00 500
Coleman CE Windows 10 6,124 741 $ 34.49 500
Sales
Author Series Name Software Percent Returned Unit Price Net Sales
Lopez Office Reference Word 2016 5.8% $ 49.95 $ 403,795.80
Krupin True Beginner Word 2016 14.7% $ 25.00 $ 39,400.00
Cote Certification Series Word 2016 14.9% $ 39.95 $ 91,245.80
Yeung Office Reference Excel 2016 8.8% $ 49.95 $ 539,410.05
Tremblay True Beginner Excel 2016 10.1% $ 30.00 $ 255,540.00
Torres Certification Series Excel 2016 3.9% $ 39.95 $ 324,234.20
Martin Office Reference Access 2016 10.3% $ 49.95 $ 361,138.50
Alfero True Beginner Access 2016 9.7% $ 30.00 $ 91,980.00
Daniels Certification Series Access 2016 10.4% $ 34.49 $ 122,887.87
Ortiz Office Reference PowerPoint 2016 9.4% $ 49.95 $ 57,892.05
Wong True Beginner PowerPoint 2016 17.5% $ 25.00 $ 21,650.00
Kumar Certification Series PowerPoint 2016 7.5% $ 34.49 $ 80,016.80
Bartalotti True Beginner Outlook 2016 9.3% $ 25.00 $ 42,725.00
Wallace Office Reference Windows 10 12.5% $ 49.95 $ 644,904.45
Toulou True Beginner Windows 10 9.6% $ 25.00 $ 188,475.00
Coleman Certification Series Windows 10 12.1% $ 34.49 $ 185,659.67
Net Sales
Net Sales
Software Certification Series Office Reference True Beginner
Access 2016 $ 122,887.87 $ 361,138.50 $ 91,980.00
Excel 2016 $ 324,234.20 $ 539,410.05 $ 255,540.00
PowerPoint 2016 $ 80,016.80 $ 57,892.05 $ 21,650.00
Word 2016 $ 91,245.80 $ 403,795.80 $ 39,400.00
Series Sales

The post Excel Volume 1 appeared first on graduatepaperhelp.

 

“Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!”

Excel Volume 1 was first posted on August 5, 2019 at 11:14 pm.
©2019 "Nursing Assignment". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement. Please contact me at support@Academicheroes.com.org

 

“Is this question part of your assignment? We will write the assignment for you. click order now and get up to 40% Discount”

Excel Volume 1 was first posted on August 6, 2019 at 3:55 am.
©2019 "Nursing Assignment". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement. Please contact me at support@Academicheroes.com.org

 
Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"