Question

In: Finance

Series Legend Input Area Net Sales CE OR TB Royalty Rate: 7.3% Average Certification Series Office...

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

EX16_XL_VOL1_GRADER_CAP_HW - Software Training Books 1.5

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.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

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. Apply bold and change the font size to 18 for the chart title.

2

24

Apply the Style 12 chart style and change the colors to Colorful Palette 4.

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

Solutions

Expert Solution

First things first, It is not possible to answer for steps 18 - 26, as you have not provided the data.

It would look like this

And here goes the remaining steps.

  1. Open the workbook
  2. Steps
    • Click and drag or use navigation arrows while pressing down shift to select the range A6:K6
    • Press Alt,H,W in sequence or go to Home>Wrap text
    • Now press Alt,H,A,C or goto Home>Align Center, [in the alignment tab the bottom center one with bars]
    • Now press Alt,H,O,H Or go to Home>Format>Row Height, and type in 30 and press enter.
  3. Steps
    • Navigate to F7
    • Enter the formula =E7/D7
    • Format it to Percentage by Home>Format>Percentage or Pressing Alt,H,P
    • Now copy the same and paste it in F8:F22 or Select F7:F22 and press Ctrl+D
  4. Steps
    • Navigate to H7
    • Enter the formula =(D7-E7)*G7
    • Now copy the same and paste it in H8:H22 or Select H7:H22 and press Ctrl+D
  5. Steps
    • Navigate to I7
    • Enter the formula =H7*$G$2 [we need to fix the cell G2 using $ or it would change when you copy the formula]
    • Now copy the same and paste it in I8:I22 or Select I7:I22 and press Ctrl+D
  6. Steps
    • Navigate to K7
    • Enter the formula =I7+J7
    • Now copy the same and paste it in K8:K22 or Select K7:K22 and press Ctrl+D
  7. Steps
    • Navigate to J2
    • Enter the formula =AVERAGE(H7:H22)
    • Press Enter and you will be on J3
    • Enter the formula =MAX(H7:H22)
    • Press Enter and you will be on J4
    • Enter the formula =MIN(H7:H22)
  8. Steps
    • Click and drag or use navigation arrows while pressing down shift to select the range L1:N2
    • Press Copy in Home or Ctrl+C
    • Now navigate to A2
    • Press Alt,E,S,V,E and Enter or use Paste Special in Home and Check the box beside Transpose and Press Enter
    • Go back to L1:N2 and press Delete
  9. Steps
    • Navigate to C6
    • Press Ctrl+Shift+=
    • and click entire column and press enter
    • Type in Series Name in the new C6 and press enter
    • You will be on C7
    • Enter the formula =VLOOKUP(B7,$A$2:$B$4,2,FALSE)
    • Now copy the same and paste it in C8:C22 or Select C7:C22 and press Ctrl+D
    • Now press Alt,H,O,W or click Home>Format>Row Width and type 18 and press enter
  10. Steps
    • Navigate to K7
    • Enter the formula =IF(G7<$H$3,$H$4,0)
    • Now copy the same and paste it in K8:K22 or Select K7:K22 and press Ctrl+D
  11. Steps
    • Click and drag or use navigation arrows while pressing down shift to select the range G7:G22
    • Press Alt,H,P and Alt,H,0 (zero)
    • or go to format in Home and select percent and press the right one among the 000 buttons
    • Click and drag or use navigation arrows while pressing down shift to select the range K7:K22
    • Press Alt,H,K or Go to Home.Format and select Accounting
    • Click and drag or use navigation arrows while pressing down shift to select the range A1:C1
    • Press Alt,H,M,C and press enter or go to Home> Merge and Center [Below Wrap Text]
    • Click and drag or use navigation arrows while pressing down shift to select the range A1:C4
    • Press Alt,H,B,T or Home>Borders>Thick Outside Borders
  12. Steps
    • Goto Pagelayout tab and click on orientation and select landscape
    • or press Alt,P,O and select Landscape
    • Press Alt,P,M,A and set the borders
    • Now press Alt,P,X and type in 70% or any number where you can get the dotted line on the outside border of Column L
  13. Steps
    • Click and drag or use navigation arrows while pressing down shift to select the range A6:L22
    • Press Alt,H,T or Go to Home>Format as Table and select Light Style Table 9
  14. Steps
    • Now Press Alt,A,S,S or go to Data>Sort
    • In the sortby select Series Name and Order select A to Z
    • Click Add level
    • In the sortby select Net Sales and Order Largest to Smallest
  15. Steps
    • Navigate to I23 (i 23)
    • And Click Alt+=
    • and press enter
    • now press Alt,H,O,W and type 14 and press enter
  16. Steps
    • Click and drag or use navigation arrows while pressing down shift to select the range G7:G22
    • press Alt,H,L,H,G or Go to Home>Conditional Formatting>Highlight cell rules>Greater Than
    • and type in 9.9% and press enter
  17. Steps
    • Click on the dropdown button beside Net Sales
    • and then Number Filters and Less Than
    • type in 100000 and press enter
  18. as I said no data is provided for 18-26 steps

Good luck


Related Solutions

Series Legend Input Area Net Sales CE OR TB Royalty Rate: 7.3% Average Certification Series Office...
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...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT