Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave
Visual Data - Version 1: Excel Assignment for Analyzing Business Expenses and Inventory

Project Description:

It is time to calculate and visually analyze you company’s expenses for the first quarter of the year.  In addition, you decided to setup the company’s inventory list to be more visually appealing.  Using Excel with the information you have already learned and with the new tools you just learned in Topic 5 and Topic 6, complete the following assignment. Remember: all information to help you complete this assignment is located on Blackboard Learning Materials (Module 3, Parts 1, 2, & 3)

  January February March Total Percent of Total Trends
Payroll
Wages            
Employer Contribution for CPP            
Employer Contribution for EI            
Employer Contribution for Benefits            
Total Payroll Amount            
Payroll Percentages
CPP 10.24%
EI 2.21%
Benefits 1.47%
Office Costs
Rental on Business Property            
Business Property Tax            
Utilities (Heat, Electrical, Water)            
Delivery, Express, Freight            
Office Expenses (postage, stationary, shipping)            
Minor Equipment Costs            
Internet access            
Supplies            
Security            
Total Office Costs            
Quantity Ordered Item # Item Name Cost Size Category Food Cost
155 7956 Crème Fraiche 3.59 8.5 oz. Accompaniments  
156 7899 Mini Chicken Pot Pie 4.75 8.5 oz. Appetizers  
256 7898 Asian Snack Mix 4.75 13 oz. Accompaniments  
362 7897 Lobster Mac & Cheese 3.95 9.75 oz. Appetizers  
266 7896 French Cheese Trio 4.95 8.5 oz. Cheese and Crackers  
139 7798 Mini Crab Pies 5.75 13 oz. Appetizers  
278 7654 Cheese Straws 4.35 10 oz. Cheese and Crackers  
362 7652 Arrabbiata Dipping Oil 2.95 6.5 oz. Accompaniments  
155 7695 White Sturgeon Caviar 20.95 32 oz. Accompaniments  
153 7645 Almond Macaroons 3.59 11 oz. Desserts  
276 7635 Cheese Biscuits 4.65 13 oz. Accompaniments  
159 7569 Sun-Dried Tomatoes 7.45 13 oz. Accompaniments  
253 7568 Pineapple Vanilla Preserves 7.45 18 oz. Accompaniments  
112 7575 Chocolate Covered Strawberries 7.55 15 oz. Desserts  
339 7563 Olive Assortment 3.75 7.5 oz. Accompaniments  
85 7562 Deviled Eggs 3.95 11 oz. Accompaniments  
295 7567 Butter Caramels 4.95 13 oz. Desserts  
156 7564 Roasted Pistachios 3.65 6.5 oz. Accompaniments  
156 7564 Roasted Pistachios 3.65 6.5 oz. Accompaniments  
99 7548 Caramel Apples 5.89 18 oz. Desserts  
134 7545 Italian Cheese Trio 3.45 9.75 oz. Cheese and Crackers  
95 7528 Crab Cake 6.45 20 oz. Appetizers  
278 7523 Swiss and Cheddar Duo 2.55 8.5 oz. Cheese and Crackers  
185 7498 Caramel Corn 3.59 11 oz. Desserts  
285 7485 Vanilla Marshmallows 5.45 14 oz. Desserts  
145 7489 Marzipan Doodles 6.45 15 oz. Cheese and Crackers  
265 7481 Shrimp Cake 3.45 9.75 oz. Appetizers  
139 7482 Mini Lobster Pie 8 24 oz. Appetizers  
280 7478 Butter Cheese Duo 4.65 15 oz. Cheese and Crackers  
62 7469 Dried Fruit Tray 5.09 6.5 oz. Appetizers  
259 7468 Cherry Tomatoes in Oil 2.75 8.5 oz. Accompaniments  
152 7464 Olive Oil Grissini 3.52 13 oz. Cheese and Crackers  
135 7465 Chocolate Cupcake Assortment 4.59 11 oz. Desserts  
85 7463 Apple Walnut Cheese 3.45 9.75 oz. Cheese and Crackers  
162 7453 Carrot & Grapefruit Crisps 5.95 9.75 oz. Accompaniments  
162 7453 Carrot & Grapefruit Crisps 5.95 9.75 oz. Accompaniments  
199 7459 Onion Flatbread 3.59 13 oz. Cheese and Crackers  
133 7433 S'mores on a Stick 5.25 25 oz. Desserts  
175 7452 Manchego and Almonds 2.25 9.75 oz. Cheese and Crackers  
166 7436 Crostinis 2.45 9.75 oz. Cheese and Crackers  
232 7425 Sardine Plate 3.58 9.75 oz. Appetizers  
159 7389 Rosemary Breadsticks 2.95 11 oz. Cheese and Crackers  
146 7379 Fruit Shrub Assortment 5.35 6.5 oz. Fruits and Nuts  
175 7368 Spring Rolls 3.25 11 oz. Appetizers  
265 7365 Gouda Round 3.89 6.5 oz. Cheese and Crackers  
138 7356 Pecan Bread Crisps 4.89 11 oz. Accompaniments  
185 7325 White Vermont Cheddar 2.75 8.5 oz. Cheese and Crackers  
59 7296 Wisconsin Cheddar 2.65 9.75 oz. Cheese and Crackers  
119 7285 Cheddar Straws 3.58 10 oz. Cheese and Crackers  
119 7285 Cheddar Straws 4.58 11 oz. Cheese and Crackers  
98 7265 Herbed Goat Cheese 3.45 9.75 oz. Cheese and Crackers  
188 7239 American Cheese Trio 4.65 13 oz. Cheese and Crackers  

Step

Instructions

Points Possible

1

Ensure you are ONLY using the Desktop version of Excel 365 or Excel 2019.  
(Do not use any other application or version)
Open the file named Hum365_F21_Excel_Assign3_V1_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.
There is a PDF version of these instructions that is part of the files you downloaded, which may make it easier to follow along.
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 some of your business expenses.  It is setup for the first quarter of the year; however, you need to fill in the expense data.

Change Company Name to your Company Name

3

3

For the Payroll section, complete the following:

Cell range B7:D7, 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.

8

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.

2

5

In cell B25, insert a function to add up the Payroll and Office Costs.

2

6

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

2

7

Starting in cell F7, 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.  Remember to use absolute referencing where required.

Format these numbers as Percentage with 1 decimal place.

6

8

Rename the tab Sheet1 to Expenses.  Change the tab color to Green, Accent 3 (col 7, row 1; theme colors)

2

9

Based on only the first quarter 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 Red, Accent 6 (col 10, row 1; theme colors)

2

10

Format the chart as follows:
Chart Title
Rename to First Quarter Office Expenses
Apply Bold
Legend
Remove it
Chart Style
Apply Style 3
Axis Labels
Add a Primary Horizontal axis title called Office Expenses
Add a Primary Vertical axis title called Dollar Amounts

4

11

Return to the Expenses worksheet.  Created a 2-D Line chart based on the first quarter of data for the Payroll section.  Size the chart to Height 5” and Width 10” and move it to cell A27.  

Edit the chart legend to change Series to the Months

3

12

Format the chart as follows:
Chart Title
Rename to First Quarter Payroll Expense
Apply Bold
Chart Style
Apply Style 6
Legend
Move it to the right side of the chart
Trendline
Add a Linear Trendline based on January

4

13

Return to the Expenses worksheet.  Create a 3-D Pie chart based on the Percent of Total data for the Payroll section.  Move this chart to a new sheet and rename it Payroll for First Quarter
Move the chart sheet the end of your workbook and change the tab color to Orange, Accent 5 (column 9, row 1; theme colors)

2

14

Format the chart as follows:
Chart Title
Rename to Payroll for First Quarter
Apply Bold
Chart Style and Layout
Apply Style 4
Apply Layout 6 

4

15

Return to the Expense worksheet

In cell G7, insert a Line Sparkline to show the trends of each Payroll expense for the first quarter of the year.

3

16

Format the 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

17

In cell G14, insert a Column Sparkline to show the trends of each Office Costs for the first quarter of the year.

3

18

Format this Column Sparkline as follows
Add First Point
Apply the Indigo, Sparkline Style Colorful #4 (column 5, row 6)

3

19

You are planning an office party for your staff and have put together a quick inventory food list.  You want to make this list more appealing by using a table.

Change Company Name to your Company Name.  In G5, insert a function or formula to calculate the Food Cost for each item on the worksheet.

3

20

Simultaneously freeze the first four rows and the two columns

Rename the tab Sheet2 to Office_Party_FoodList.  Change the tab color to Green, Accent 4 (column 8, row 1; theme colors)

5

21

Format your inventory list using the Red Table Style Light 14.  Rename the table to Food_List and remove any duplicate records that match Item# 

Table Style Options, add the First Column and Total Row

2

22

Apply the Light Blue Gradient Data Bar conditional formatting to the Food Cost column

Apply a custom conditional formatting of Dark Blue fill with White font color text with Bold to all the text that equals  Accompaniments in the Category column.

4

23

In the Total Row, Average the Cost column and count the Item Name column

2

24

Make a copy of this worksheet. Rename it Food List Sorted and move it to the right of Office_Party_FoodList tab.  Rename the Table Name to Sorted_List and change the tab color to Blue, Accent 1 (column 5, row 1; theme colors)

3

25

On the Food List Sorted worksheet, apply a multiple sort to your list as follows:
Item Name – A to Z, then by
Category – Z to A, then by
Food Cost – Largest to Smallest

3

26

Return to Office_Party_FoodList, apply the following filters:
Display only the foods for Accompaniments 
Display only the values greater than 1000

3

27

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

Header:
Center Section: Visualize Data

Footer:
Left Section: Student Name (replace this with your name)
Right Section: insert the Sheet Name code

3

28

For the Expenses worksheet:
Change all margins (except the Header/Footer) to 0.5”
Change the page orientations Landscape
Scale it to 1 page
Center horizontally and vertically on the page

2

29

For theOffice_Party_FoodList worksheet:
Change the page orientation to Landscape
set the Print Area to A1:G54
Scale it to 1 page
Center horizontally and vertically on the page

4

30

For the Food List Sorted worksheet, move the first vertical page break to the right of Food Cost column
Insert a page break at cell A29
For printing purposes, repeat the first four rows

4

31

On the Expenses worksheet, format your money numbers as Accounting with 2 decimal places

On both Food List worksheets, format your Cost and Food Cost columns as Currency with 2 decimal places.

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

2

32

Change the workbook properties by adding the following:

Title: Visualize Data Version 1
Subject: Excel
Author: Student Name (Change this to your name)
Keywords: Visualize Data Assignment

3

33

When you have completed this assignment, you should have a total of five (5) worksheets.  Save the file and submit the file to MyLab IT as directed.  

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

If you are not satisfied, you can review your submission, review the errors, fix anything in your document, and resubmit to obtain a higher grade.  You can resubmit up to 3 times.

0

support
close