Assignment #2 Excel (10% of Final Grade)

This assignment is to be completed individually and a final excel file should be submitted to the Assignment #2 Excel Assignment Folder by the due date of Sunday, March 13th.

Assignment Type: Excel File Due Date: Sunday, March 13th Grading: Total 20 Marks, Worth 10% of Final Grade Submission: An electronic version of your excel file must be submitted to the Assignment #2 Folder in eCentennial by the time specified by your professor. Any late submissions will receive a –15% penalty per day. Instructions: Imagine you work for a travel company called [Your Name] Travel. For example, my travel company is Michelle Belchetz Travel. You have many offices around the world and you want to create some visuals to help understand how these offices have been performing Year to Date (YTD). Utilizing the data in the excel file titled: Excel Assignment Data File, create the charts that have been outlined in the Step-by-Step Instructions below. *Note: When I say “calculate” in the instruction below, I mean you must use the excel functions/formulas to calculate, not a calculator and just manually enter numbers! Step-By-Step Instructions Step 1: Open the Excel File Named Excel Assignment Data File and download it to your computer. Save the file to your computer by including your name in the File Name: Excel Assignment Data File [First Name Last Name] Imagine you work for a travel company called [Your Name] Travel. For example, my travel company is Michelle Belchetz Travel. You have many offices around the world and you want to create some visuals to help understand how these offices have been performing Year to Date (YTD). Step 2: In the excel file, Create a Copy of Sheet 1 and rename the new worksheet with your First Name. Sheet 1 should always remain as the first worksheet to the far right of all the other worksheets. Do not delete Sheet 1 and do not change anything on that original worksheet.

Step 3: On the new worksheet (the one with your name) select 5 cities that you want to keep in your list and delete the rest of the rows with the other cities. Delete the same cities for both the Revenue data and the Website Hits data. Your worksheet with your name should now have only 5 cities and the corresponding data for Revenue and Website Hits and it should look something like this (Note: Actual city names and data have been revised in my example for illustration purposes).

Step 4: Calculate (using the appropriate excel function) the Total Revenue for each City in Column H, Row 5 to 9 Calculate (using the appropriate excel function) the Total Website Hits for each City in Column H, Row 15 to 19 Add a Total label in Cell A10 (below the list of cities) and a Total label in Cell A 20 (below the list of cities) and; Calculate the Total Revenue for each month in Row 10, Column B to G Calculate Total Website Hits for each month in Row 20, Column B to G (Note: Actual city names and data have been revised in my example for illustration purposes).

Step 5: To the right of Column A, Insert a Column that will be used to calculate Average Per City. Label the new Column Average Per City in cells B4 and B14. Calculate the Average Revenue Per City and Average Website Hits Per City

Step 6: Sort your data as follows:

For the Revenue data, sort in order of the City with Highest Total Revenue to Lowest Total Revenue (do not sort the Total Row, leave that where it is at the bottom of the table)

For the Website Hits Data, sort in order of the City with Lowest Average Website Hits to Highest Average Website Hits (do not sort the Total Row, leave that where it is at the bottom of the table) Step 7: Now, improve on the look of your Revenue and Website Hits tables. Consider what you can you do to the data alignment, borders, column & row headings, number formatting, table names/titles, etc. to make them look more attractive and professional. Step 8: Make a copy of the worksheet that has been labelled with your name. The new worksheet should be labelled as Formatted. You should now have 3 worksheets in your workbook that look like this:

Step 9: On this same worksheet “FORMATTED” Using the data in the Monthly Revenue Table, add a Clustered Column Chart Grouped by City along the X Axis

Step 10: Format your chart with the following details

a. Add a chart title to describe that the chart is displaying Revenue Year to Date for your Travel company. The Title should be [Your Name] Travel Revenue YTD

b. Label your axis. Horizontal (X) Axis should be Office Location. Vertical (Y) Axis should be Revenue.

c. Move the legend to the right of your chart d. Change the Colour of the Bar for January to Black

Step 11: On the same worksheet “FORMATTED”, make a copy of the Clustered Column Chart that you just completed. On this copied chart, add the Average Revenue Per City data (the data from Column B), and represent, just the Average Per City data series, as a Line. Increase the width of this line to 4pt Font

Step 12: On the same worksheet “FORMATTED” Using the Website Hits Data, create a Pie Chart representing Total Website Hits per city. Each “slice of the pie” should be the Total Website Hits per city. Ensure each “slice” is appropriately labelled so it is clear what the total website hits is for each city. For this one, you choose how best you want to represent the data, what title is most appropriate, how/where the legend should be represented, what labels are required, etc. Below is an example of the Unformatted pie chart using my own data. You need to create the pie chart and then format it as described above.

Step 13: Once you have completed and saved all steps within the assignment, you can submit your final Excel File to the Assignment #2 Folder. Your submission must be your excel file with all 3 worksheets that you have created. I should be able to see all formulas when selecting cells where you were instructed to calculate totals or averages and all charts should be derived directly from the data in your tables. Final submission should have all of these worksheets: Sheet 1 is the original data that I supplied and should not be changed The worksheet with “YOUR NAME” should have the final, sorted, formatted table The worksheet labelled “FORMATTED” will have the 2 column charts and 1 pie chart