Just do exactly what the instructions say in the link. https://docs.google.com/document/d/18SuZtvgmdGTe673CTJv0a8T4KXykhgZ_Eyy9XDOgNAc/edit?usp=sharinghttps://docs.google.com/document/d/1pobVXYMGqBWh2U…These links are the same as the files.The instructions are straight forward, on both links. Each assignment usually takes me just under 2 hours to complete and I do not know the material very well. I would like you to do both links please and thank you.

instructions_np_ex16_5b__1_.docx

instructions_np_ex16_cs1_4a__1_.docx

Unformatted Attachment Preview

New Perspectives Excel 2016 | Module 5: SAM Project 1b

The Optical Boutique

WORKING WITH EXCEL TABLES, PIVOTTABLES, AND PIVOTCHARTS

GETTING STARTED

•

Open the file NP_EX16_5b_FirstLastName_1.xlsx, available for download

from the SAM website.

•

Save the file as NP_EX16_5b_FirstLastName_2.xlsx by changing the “1” to

a “2”.

o

•

If you do not see the .xlsx file extension in the Save As dialog box, do

not type it. The program will add the file extension for you automatically.

With the file NP_EX16_5b_FirstLastName_2.xlsx still open, ensure that

your first and last name is displayed in cell B6 of the Documentation sheet.

o

If cell B6 does not display your name, delete the file and download a new

copy from the SAM website.

PROJECT STEPS

1.

Colin Morin is the assistant sales manager for The Optical Boutique. One of his

assignments is to format the product sales records from 2018 to 2020 to make

it easier to analyze.

Switch to the Prescription worksheet. Unfreeze the top row of the worksheet.

2.

Sort the data in the PrescriptionSales table first in ascending order by the

Material field and then in the ascending order by the Shape field.

3.

Insert a Total Row in the PrescriptionSales table, and then use the Total Row to

calculate the total of the values in both the 2018 and 2019 fields. (Hint: The

Total Row should automatically total the values in the 2020 field.)

4.

Colin decides to create a PivotTable to allow him to better manipulate and filter

the PrescriptionSales table data.

Create a PivotTable based on the PrescriptionSales table in a new worksheet

using Prescription PT as the worksheet name. Update the PivotTable as

described below so that it matches Final Figure 1:

a.

Add the Material field and the Product Code field (in that order) to the

Rows area. (Hint: The order of the materials should be Plastic, SS, and

Titanium, as shown in Final Figure 1. Sort the PivotTable manually by

dragging or by using the Move command if necessary.)

b.

Add the 2018, 2019, and 2020 fields (in that order) to the Values area.

c.

Update the Sum of 2018 field in the Values area to display the name

2018 Sales with the Accounting number format with 0 decimal places

and $ as the symbol.

New Perspectives Excel 2016 | Module 5: SAM Project 1b

d.

Update the Sum of 2019 field in the Values area to display the name

2019 Sales with the Accounting number format with 0 decimal places

and $ as the symbol.

e.

Update the Sum of 2020 field in the Values area to display the name

2020 Sales with the Accounting number format with 0 decimal places

and $ as the symbol.

5.

Go to the Sunglass worksheet. Format the range A2:G16 as an Excel table with

headers using the Table Style Medium 26 table style. (Hint: Depending on

your version of Office, the table style may be named Light Yellow, Table

Style Medium 26 instead.) Use SunglassSales as the name of the table.

6.

Colin notices that the SunglassSales table is missing a record.

Add the following record as shown in bold in Table 1 below to the end of the

SunglassSales table:

Table 1: New Record for the SunglassSales Table

Product

Code

Type

Material

Shape

2018

2019

2020

SGPP-125

Sunglass

Titanium

Rectangular

$1,100

$990

$1,300

7.

The Optical Boutique offers a large selection of reading glasses. Colin wants to

summarize the reading sales data using subtotals to show how the type of

Material used influences reading sales.

Go to the Reading worksheet and complete the following steps:

a.

Sort the table by the Material field in ascending order.

b.

Convert the table to a normal range.

c.

Insert subtotals into the range A2:G24, with the subtotals appearing at

each change in the Material column value.

d.

The subtotals should use the SUM function and include subtotals for the

2018, 2019, and 2020 fields. (Hint: Make sure to check the summary

below data check box if it is not checked automatically.)

8.

Go to the Computer worksheet and remove the duplicate record associated with

the Product Code of COPS-151 from the ComputerSales table.

9.

Colin wants to summarize The Optical Boutique’s sales data for all products in a

PivotTable. Before doing so, he must first update the data.

Go to the All Products worksheet and freeze the top two rows of the worksheet.

10.

Use the Find command to find the record with a Product Code of RDPP-138. Edit

the record by changing the 2019 field value to $1850. Close the Find dialog

box.

New Perspectives Excel 2016 | Module 5: SAM Project 1b

11.

Filter the table to show only records for products with a Titanium material type

and a Rectangular shape.

12.

Switch to the All Products PT worksheet. Refresh the PivotTable data. (Hint:

After refreshing the PivotTable, the RDPP-138 record in row 34 should now

have a 2019 Sales field value of $1,850.)

13.

Apply the Pivot Style Medium 26 PivotTable style to the PivotTable. (Hint:

Depending on your version of Office, the PivotTable style may be named Light

Yellow, Pivot Style Medium 26 instead.)

14.

Create a Filter for the PivotTable by adding the Shape field to the Filters area.

Filter the table so that only products with an Oval Shape are visible.

15.

Create a slicer that will filter the PivotTable based on the Material field value.

Resize the slicer so that it has a height of 1.5” and a width 2”. Move the slicer

so that its upper-left corner appears within cell F3 and its lower-right corner

appears within cell I10. Finally, use the slicer to filter the PivotTable so that

only products made of Plastic are visible. (Hint: Depending on your version of

Office, the row order may appear different from Final Figure 7.)

16.

Colin also wants to summarize sales data for all products using a PivotChart to

help determine what product lines should be expanded.

Switch to the Product Material PT worksheet. Insert a PivotChart using the

Clustered Column chart type. Format the PivotChart as described below:

a.

Resize and reposition the PivotChart so that the upper-left corner is

located within cell F3 and the lower-right corner is located within cell O19.

b.

Add the chart title Sales by Frames to the PivotChart using the Above

Chart option.

c.

Use the Type axis field button to filter the PivotChart so that only the

sales data for Prescription and Reading glasses of each type of material

appears in the chart.

Your workbook should look like the Final Figures on the following pages. Save your

changes, close the workbook, and then exit Excel. Follow the directions on the SAM

website to submit your completed project.

New Perspectives Excel 2016 | Module 5: SAM Project 1b

Final Figure 1: Prescription PT Worksheet

Final Figure 2: Prescription Worksheet

New Perspectives Excel 2016 | Module 5: SAM Project 1b

Final Figure 3: Sunglass Worksheet

Final Figure 4: Reading Worksheet

New Perspectives Excel 2016 | Module 5: SAM Project 1b

Final Figure 5: Computer Worksheet

Final Figure 6: All Products Worksheet

New Perspectives Excel 2016 | Module 5: SAM Project 1b

Final Figure 7: All Products PT Worksheet

Final Figure 8: Product Material PT Worksheet

New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a

SpringLeaf Designs

FORMATTING, FORMULAS, AND CHARTS

GETTING STARTED

•

Open the file NP_EX16_CS1-4a_FirstLastName_1.xlsx, available for

download from the SAM website.

•

Save the file as NP_EX16_CS1-4a_FirstLastName_2.xlsx by changing the

“1” to a “2”.

o

•

If you do not see the .xlsx file extension in the Save As dialog box, do

not type it. The program will add the file extension for you automatically.

With the file NP_EX16_CS1-4a_FirstLastName_2.xlsx still open, ensure

that your first and last name is displayed in cell B6 of the Documentation sheet.

o

If cell B6 does not display your name, delete the file and download a new

copy from the SAM website.

PROJECT STEPS

1.

Geneva Huddleston works for SpringLeaf Designs. Over the years, the company

has released a variety of products, and Geneva’s manager would like her to

make a spreadsheet to help the company keep track of sales figures.

Change the theme of the workbook to Office.

2.

On the Merchandise worksheet, change the width of columns D through G to

14.00 characters.

3.

Change the height of row 2 to 32 pt.

4.

Merge and center the contents of the range B2:J2.

5.

Format the merged ranges B2:J2 and L2:M2 as described below:

6.

a.

Apply the Title cell style.

b.

Apply bold formatting.

c.

Change the font color to Green, Accent 6, Darker 50% (10th column,

6th row of the Theme Colors palette).

d.

Change the cell fill color to Green, Accent 6, Lighter 40% (10th column,

4th row of the Theme Colors palette).

Format the ranges B3:J3 and L3:M3 as described below:

a.

Center cell contents.

b.

Change the font to Arial Black.

c.

Change the font size to 9 pt.

New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a

d.

Change the font color to Green, Accent 6, Darker 50% (10th column,

6th row of the Theme Colors palette).

e.

Apply the Top and Double Bottom Border cell border.

f.

Apply wrap text formatting.

7.

Select range B4:B14, and then increase the indent of the cell contents once.

8.

Italicize the range B15:B18 and the range B20:B21.

9.

Format the range I4:I18 with the Accounting number format with zero

decimal places and use $ as the symbol. (Hint: Depending on how you

complete this action, the number format may appear as Custom instead of

Accounting.)

10.

Geneva’s manager would like to see some additional information to help make

sales decisions.

Enter a formula in cell J4 using the IF function to it whether or not the item

Beaded Earrings should go on sale. It will go on sale if its current sales rank (in

cell G4) is 8th or lower. (Hint: If the cell value is greater than or equal to 8, it

will go on sale.)

a.

If this condition is true, the item will go on sale. (Hint: The if_true value

should be “YES”.)

b.

If this condition is false, the item doesn’t need to go on sale. (Hint: The

if_false value should be “-”.)

c.

Copy the formula created in cell J4 to the range J5:J14.

11.

Enter a formula in cell E4 to calculate how long each item has been on sale.

This is calculated by subtracting the launch date in cell D4 from the Data

Updated date in cell C20. To show the time in years, divide that result by 365.

Use an absolute reference to the date in cell C20. Copy the formula from cell

E4 to cells E5:E14.

12.

For the range E4:E18, update the number format by decreasing the number of

decimal places displayed to 1.

13.

Enter a formula in cell I15 using the SUM function to total the current sales in

the range I4:I14.

14.

Enter a formula in cell I16 using the AVERAGE function to calculate the

average current product sales based on the range I4:I14.

15.

Enter a formula in cell I17 using the MAX function to calculate the highest

selling product based on the range I4:I14.

16.

Enter a formula in cell I18 using the MIN function to calculate the lowest selling

product based on the range I4:I14.

17.

Add Solid Fill Green Data Bars to range I4:I14.

18.

Enter a formula in cell M6 using the VLOOKUP function to find an exact match

for the sale status of scarves. Use the value in cell L6 as the lookup_value, the

range B3:J14 as the table_array, 9 as the col_index_num argument, and

FALSE as the range_lookup argument.

New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a

19.

In cell C21, enter a formula using the TODAY function that displays the current

date.

20.

Hide column F.

21.

The company is considering expanding, and Geneva’s manager asked her to

review some numbers for growth options.

On the Projections worksheet, use AutoFit to modify the width of column B to

best fit cell contents.

22.

Use the Format Painter to copy the format only from the range B3:F3 to the

range B11:F11. Then use the Format Painter to copy the format only from the

range B9:F9 to the range B15:F15.

23.

Enter a formula in cell C9 using the PMT function to calculate the monthly

payment on a loan using the assumptions listed in the Status Quo scenario. In

the PMT formula, use C6 as the monthly interest rate (rate), C8 as the total

number of payments (nper), and C4 as the loan amount (pv). Enter this

formula in cell C9, and then copy the formula to the range D9:F9.

24.

Select cell F9 and use Goal Seek Analysis to determine the loan amount in the

Aggressive Expansion scenario based on a monthly payment of $12,000. In the

Goal Seek calculations, set the value of cell F9 to -12,000 and select cell F4

(Total Loan Amount) as the changing cell. Keep the outcome of the Goal Seek

Analysis as the value of cell F4.

25.

Enter a formula in cell C15 using the SUM function to calculate total revenues

for the current year (or the range C12:C14). Use the Fill Handle to copy the

formula from cell C15 to the range D15:F15.

26.

There’s a lot of numerical information in this worksheet, so some visuals might

help make the data easier to interpret.

Insert Column Sparklines in the range G12:G14 based on data in the range

C12:F14. Change the sparkline color to Green, Accent 6, Darker 50% (10th

column, 6th row of the Theme Colors palette).

27.

Select the range B12:C14, and insert a 2-D Pie chart segmenting revenues by

product. Reposition the chart so the upper-left corner is in cell H2.

28.

Make the following changes to the chart:

29.

a.

Change the chart style to Style 2.

b.

Change the chart title to Current Year Revenues and change the font

color to Blue (8th column, 1st row of the Standard Colors palette).

c.

Add Center data labels to the chart.

d.

Move the chart legend to the Left position.

Select the Actual and Projected Revenues chart located in the range H16:M30.

Make the following changes to the chart:

a.

Change the major units of the vertical axis to 5000000.

b.

Add Growth Scenarios as the horizontal axis title and Revenue as the

vertical axis title.

c.

Remove the data labels from the graph.

New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a

d.

Move the chart legend to the Bottom position.

30.

Set the range B1:G15 as the print area of the worksheet. Change the

worksheet orientation to Landscape.

31.

Delete the Sales Summary worksheet.

Your workbook should look like the Final Figures on the following pages. (The value

in cell F4 on the Projections worksheet generated by the Goal Seek Analysis has

intentionally been blurred in the Final Figure.) Save your changes, close the

workbook, and then exit Excel. Follow the directions on the SAM website to submit

your completed project.

Final Figure 1: Merchandise Worksheet

New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a

Final Figure 2: Projections Worksheet – A1:G16

Final Figure 3: Projections Worksheet – H1:M31

…

Purchase answer to see full

attachment