Assignment 3 Fall 2014
CBSY2205- 2014 - 30
Assignment 3 – Music Academy Results
Administration
You will place an electronic copy of your report and spreadsheet on Class Web Site by beginning of class on the due date. Refer to Moodle for that date.
Late assignments accepted only with prior approval. Discount will be applied rigorously!!.
Assignment Description:
You are working as an assistant for me at a Music Academy. We have collected data about each contract we have issued over the last three years and now I want you to analyze this data for me so that we can plan out next year’s contract offerings.
I want you to take the workbook called “Lessons” from the web site and answer the following questions: (for each question you are required to take a copy of all the data cells on the spreadsheet you created for that question and place it on a new sheet in the workbook, changing the name of the worksheet to refer to the question being answered.)
1. Based on the cost of a month for each contract and including the monthly rental, create a flexible way of isolating the total income for 2010 for each of the following “Lesson Types”. Piano, Percussion and Violin. Take a copy of the worksheet for each one and paste that copy onto a new worksheet, renaming the worksheet tab to reflect the content. Three new worksheets in all. 10 marks.
2. Find out which Instructor gathered the most revenue for us and take a copy to place on a new worksheet with named tab. 5 marks.
3. Create a pivot table on its own worksheet, with the Instructor Names as the Row labels and the Lesson Type as the column labels. Set up a filter on Student Name. With no filter applied, create a Pie Chart for the Piano Lesson Type and place it on the sheet with the Pivot table underneath the table. Make sure there are sufficient labels and legend to make the resulting chart very clear. 20 marks.
4. Use the filter on the pivot table to determine which student spends the most money on lessons with us in the year 2010.
Assignment 3 – Music Academy Results
Administration
You will place an electronic copy of your report and spreadsheet on Class Web Site by beginning of class on the due date. Refer to Moodle for that date.
Late assignments accepted only with prior approval. Discount will be applied rigorously!!.
Assignment Description:
You are working as an assistant for me at a Music Academy. We have collected data about each contract we have issued over the last three years and now I want you to analyze this data for me so that we can plan out next year’s contract offerings.
I want you to take the workbook called “Lessons” from the web site and answer the following questions: (for each question you are required to take a copy of all the data cells on the spreadsheet you created for that question and place it on a new sheet in the workbook, changing the name of the worksheet to refer to the question being answered.)
1. Based on the cost of a month for each contract and including the monthly rental, create a flexible way of isolating the total income for 2010 for each of the following “Lesson Types”. Piano, Percussion and Violin. Take a copy of the worksheet for each one and paste that copy onto a new worksheet, renaming the worksheet tab to reflect the content. Three new worksheets in all. 10 marks.
2. Find out which Instructor gathered the most revenue for us and take a copy to place on a new worksheet with named tab. 5 marks.
3. Create a pivot table on its own worksheet, with the Instructor Names as the Row labels and the Lesson Type as the column labels. Set up a filter on Student Name. With no filter applied, create a Pie Chart for the Piano Lesson Type and place it on the sheet with the Pivot table underneath the table. Make sure there are sufficient labels and legend to make the resulting chart very clear. 20 marks.
4. Use the filter on the pivot table to determine which student spends the most money on lessons with us in the year 2010.
References: to the appendix can be made to pull through numbers to support your findings