Fill This Form To Receive Instant Help

#### Excel Assignment #3 – Visual Data – Version 1 Project Description: It is time to calculate and visually analyze you company’s expenses for the first quarter of the year

###### MS Excel

Excel Assignment #3 – Visual Data – Version 1

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)

Steps to Perform:

 Step Instructions Points Possible 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 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

 Step Instructions Points Possible 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