Step

Instructions

Points Possible

1

Open the file named Hum365_Excel_Assign3_VisualData.xlsx. Grader has automatically added your last name to the beginning of the filename, so it should be easy to locate.

Ensure you have saved these files correctly within your File Management structure.

When you open the Excel workbook, you will see two worksheet tabs at the bottom!

0

2

Change the Workbook Theme to Ion Boardroom, the Colors to Slipstream, and the Fonts to Century Gothic

Sheet1 contains information about your business expenses.  It is setup for the first two quarters of the year; however, you need to fill in the expense data.

Change Company Name to your Company Name

2

3

For the Payroll section, complete the following:

Cell range B7:G7, type in how much wages you spend each month on your staff’s pay.  Your choice.

In cell B8, insert a formula to calculate the Employer Contribution for CPP.  CPP is the

Canadian Pension Plan and employers are responsible to pay some of its contribution.  This is calculated by multiplying your monthly wages by the CPP Rate.  Copy this formula across to the other months. Remember to use proper absolute referencing.

In cell B9, insert a formula to calculate the Employer Contribution for EI.  EI is Employment Insurance, which helps those who lose a job or cannot find a job, and as a business owner, you are required to pay a certain percentage.  This is calculated by multiplying your monthly wages by the EI Rate. Copy this formula across to the other months.  Remember to use proper absolute referencing.

In cell B10, insert a formula to calculate the Employer Contribution for Benefits.  Benefits are not offered by all companies, but they do help employees subsidize medical and dental payments. This is calculated by multiplying your monthly wages by the Benefits Rate. Copy this formula across to the other months. Remember to use proper absolute referencing.

In cell B11, insert a function to total each month for the Payroll section.

4

4

For the Office Costs section, complete the following:

For all the Office Costs expenses, type in how much you spend on each expense for each month. Your choice.

In cell B23, insert a function to total each month for the Office Costs section.

1

Step

Instructions

Points Possible

5

For the Advertising Costs section, complete the following:

For all the Advertising Costs expenses, type in how much you spend on each expense for each month. Your choice.

In cell B32, insert a function to total each month for the Advertising Costs section.

1

6

For the Other Expenses section, complete the following:

For all the Other Expenses Costs expenses, type in how much you spend on each expense for each month. Your choice.

In cell B38, insert a function to total each month for the Other Expenses section.

1

7

For the Service Costs section, complete the following:

For all the Service Costs expenses, type in how much you spend on each expense for each month. Your choice.

In cell B43, insert a function to total each month for the Service Costs section.

1

8

In cell B45, insert a function to add up each sections’ totals using the total cells for each section. 

1

9

In cell H7, insert a function to total each expense for each section.

5

10

Starting in cell I7, insert a formula to calculate the Percent of Total for each expense within the different sections.  This is calculated by dividing each item by the total of the entire section.  For example, to calculate the Wages Percent of Total, you would divide the Wages total by the Total Payroll Amount.  Remember to use absolute referencing where required.

5

11

Based on only the monthly data for Office Costs section, create a 3-D Clustered Column chart.  Move this chart properly to a new sheet and rename it Office Costs Expenses.  Move the new chart sheet to the end of your workbook (to the right of Sheet2).  Change the Tab Color to Orange, Accent 5 (Column 9, Row 1 - Under Theme Colors).

2

12

Format the chart as follows:

Chart Title

Rename to OFFICE COSTS EXPENSES

Apply Bold

Legend

Remove it

Chart Style

Apply Style 2

Axis Labels

Add a Primary Horizontal axis title called Office Expenses

Add a Primary Vertical axis title called Dollar Amounts

4

13

Based on only the monthly data for the Advertising Costs section, create a 2-D Line chart. 

Size the chart to Height 5” and Width 12” and move it to cell A47.  Switch the Row/Columns.

2

Step

Instructions

Points Possible

14

Format the chart as follows:

Chart Title

Rename to Advertising Costs

Legend

Remove it

Chart Style

Apply Style 6

Trendline

Add a Linear Trendline based on Series 4

4

15

Based on the Percent of Total data for the Payroll section, create a 3-D Pie Chart.  Move this chart to a new sheet and rename it Payroll for First Two Quarters

Move the chart sheet the end of your workbook and change the tab color to Black, Text 1 (Column 2, Row 1 - Under Theme Colors)

2

16

Format the chart as follows:

Chart Title

Rename to Payroll for First Two Quarters 2021

Apply Bold

Legend

Remove it

Chart Style and Layout

Apply Style 4

Apply Layout 6

4

17

Return to your Actual Expense Sheet

In cell J7, insert a Line Sparkline to show the trends of each Payroll expense for the first two quarters of the year.  Duplicate this for Other Expenses (J35) and Service Costs (J41)

3

18

Format these Line Sparklines as follows

Add High Point and Markers

Apply the Red, Sparkline Style Accent 6 (no dark or light) (column 6, row 3) Change the High Point Marker Color to Light Blue (under standard colors)

4

19

In cell J14, insert a Column Sparkline to show the trends of each Office Costs for the first two quarters of the year.

1

20

Format this Column Sparkline as follows

Add First Point

Apply the Dark Green, Sparkline Style Colorful #4 (column 4, row 6)

3

21

In cell J26, insert a Win/Loss Sparkline to show the trends of each Advertising Costs for the first two quarters of the year.

1

22

Format this Win/Loss Sparkline as follows

Add Last Point

Apply the Blue, Sparkline Style Accent 6 Darker 50% (column 6, row 1)

Change the Sparkline Color to Dark Blue (under standard colors)

3

Step

Instructions

Points Possible

23

Sheet2 contains a list of your clients who donated items and values to your charity drive you are organizing.  You want to make this list more appealing.

Change Company Name to your Company Name

Simultaneously freeze the first four rows and the first column 

Format your client list using the Green Table Style Light 12.  Rename the table to ClientList and remove any duplicate records that match First and Last Name

4

24

Insert a row above Row 50. Add your ClientID as 15.  and enter your FirstName and Last Name. For the rest of the information, you can make up the information.

1

25

For Table Style Options, add the First Column and Total Row

2

26

Make a copy of this worksheet. Rename it Donation List Sorted and move it to the right of Sheet2 tab.  Change the tab color to Light Blue (under Standard Colors).  

Change the Table Style to Green, Table Style Medium 5 (Column 5, Row 1 under Medium). 

Rename the Table Name to SortedList

4

27

On the Donation List Sorted worksheet, apply a multiple sort to your list as follows: 

ClientID – smallest to largest

Last Name – A to Z, then by 

Category – Z to A, and then by 

Value – largest to smallest

4

28

Apply the Purple Gradient Data Bar conditional formatting to the Value column

Apply the conditional formatting Yellow Fill with Dark Yellow Text to all the text that equals Gift Certificate in the Category column.

4

29

In the Total Row, total the Value column and count the First Name column

1

30

Return to Sheet2, apply the following filters:

Display only those clients from ON

Display only the values greater than 100

3

31

In the Total Row, average the gift values

1

32

Rename the Sheet1 tab to Quarterly Expenses and change the tab colour to Dark Blue (under Standard Colors).  

Rename the Sheet2 tab to Donation List Filtered and change the tab colour to Blue (under Standard Colors).

4

33

For all worksheets (not the Charts), add the following headers and footers

Header:

Center Section: Excel Assignment #3

Footer:

Left Section: Student Name (replace this with your name)

Right Section: insert the Sheet Name code

3

Step

Instructions

Points Possible

34

For all worksheets (not the Charts), change all margins (except the Header/Footer) to 0.5”

Center horizontally and vertically on the page

2

35

Change the page orientation for the Quarterly Expenses worksheet to Landscape Scale it to 1 page

2

36

For the Donation List Filtered worksheet, set the Print Area to A4:L106, change the orientation to Landscape, and scale to 1 page

4

37

For the Donation List Sorted worksheet, change the page orientation to Landscape.  Move the first vertical page break to the right of Category

Move the horizontal page break to above Row 53

Return to Normal View

For printing purposes, repeat the first four rows

5

38

On the Quarterly Expenses worksheet, format your money numbers as Accounting and your Percent numbers as Percents with 1 decimal place.

On both Donation List worksheets, format your value column as Currency with no decimals.

Autofit your worksheets (not the Charts) to ensure all values are displayed.

2

39

Save the file and submit the file Hum365_Excel_Assign3_VisualData.xslx to MyLab IT as directed.  You should have a total of FIVE worksheet tabs.

Review your grade (it will be graded automatically).  Once you are satisfied with your grade, you can close the file and the Excel application.  

If you are not satisfied, you can fix anything in your workbook and resubmit to obtain a higher grade.  You can resubmit up to 3 times.

To help you compare your document, you can assignment example

Hum365_Excel_Assign3_VisualData_Example.pdf, which is a file that was downloaded with this assignment.

0

Total Points

100