MCU-CS500: Advanced Data Analysis

MCU-CS500: Advanced Data Analysis Week 1 Assignments Introductions

Assignments - Textbook Chapter exercises:

The Textbook Chapter exercises will consist of the completed file(s) from the Project Exercises included in each chapter (Chapters 1, 2, 3 for this week). These exercises can be found on the white-coloured pages comprising the bulk of each chapter. For example, for Assignment 1, you need to follow the instructions on the white pages of Chapters 1,2,3 and create the following spread sheets:

• Fred Bob Gleamin Personal Budget
• HyperMass Online Storage
• FroYoToGo

The Textbook exercises contains step-by-step instructions on how to complete the Excel spreadsheet(s) which the student will then save upon completion and submit to the appropriate submission link in VLE.

Case Studies – Based on your own business idea:

The case studies will be based on YOUR OWN BUSINESS IDEA (introduced and discussed on the Week 1 Discussion Forum). For this week you have to do case study 1 (Assignment 2). That is, for case study 1 (Assignment 2) you need to create spreadsheets similar to assignment 1 (with different data) for your own business idea.

Please make sure your submissions contain all the following:

Chapter 1 Exercise:

This week's file can be a budget file similar to the Chapter 1 textbook project, or it can be another type of spreadsheet that meets the requirements listed below.  This week's file, however, must have original text, numbers, colour-schemes, graphics, charts, etc., all pertaining to your own individual business idea.  No duplicate information which has been copied from the completed textbook file will be allowed.

Requirements:

• The worksheet title, subtitle, column titles, and row titles are entered.
• The worksheet tab name and colour are changed.
• The worksheet’s margins, header, and orientation are changed as appropriate.
• A 3-D pie chart is created based on this spreadsheet's data.

Chapter 2 Exercise:

For this exercise, you will need to modify/add to the Excel file from Chapter 1, in order to meet the requirements listed below.

Requirements:

• The worksheet tab name and colour are changed.
• The Total rows and columns are created with appropriate data, at least two manually entered FORMULAS (i.e., =A1*B3) and at least three different FUNCTIONS (i.e., SUM, AVERAGE, MIN, MAX, COUNT, etc.).
• Conditional formatting is applied somewhere in the spreadsheet.
• The column widths and row heights are adjusted.
• There are no spelling errors.
• The worksheet’s margins, header, and orientation are changed as appropriate.
• A 3-D pie chart is created based on this spreadsheet's data. The chart is on its own worksheet, resized, formatted, and positioned appropriately.
• The worksheet name and a theme colour are applied to the sheet tabs.

Chapter 3 Exercise:

This chapter's file can be Financial Projection information similar to the Chapter 3 textbook project, or it can be another type of spreadsheet that meets the requirements listed below.

Include all the previous spreadsheets within this file.  As we move forward, you'll be building a MASSIVE workbook with a lot of spreadsheets. LABEL YOUR TABS: i.e., Week 1-Budget, Week 1-Chart, Week 1-Budget_changed, Week 1-Chart_changed, etc.

Requirements:

• The workbook title and subtitle are entered and a workbook theme is applied.
• The column headings are created and rotated.
• The column widths are adjusted and row titles are entered.
• A What-if Assumptions table is created.
• All the formulas are entered correctly, including absolute cell references.
• The IF function is used correctly.
• A clustered column chart is added to a separate sheet and formatted.
• The sheets are renamed, colour is added to the sheet tabs, and the sheet tabs are reordered.
• What-if analysis is performed.
• Goal seeking is performed.
• There are no spelling errors.

MCU-CS500: Advanced Data Analysis Week 2 Assignments Introductions

Textbook Chapter Exercises:

The Textbook Chapter exercises will consist of the completed file(s) from the Project Exercise included in each chapter (Chapters 4, 5 and 6 for this week).  The tutorial exercises can be found on the white-coloured pages comprising the bulk of each chapter. That is, for assignment 3, you need to follow the instructions from Chapter 4 and 5 and do the following:

• FCU At Home Financial Tools
• Williams Franchise Holdings Consolidated Expenses Assignment 5 is based on Chapter 6, you need to do this spreadsheet:
• Coastal Realty Agent Commission Table

The Textbook exercises contain step-by-step instructions on how to complete the Excel spreadsheet(s) which the student will then save upon completion and submit to the appropriate submission link in VLE.

Case Studies:

The case studies will be based on YOUR OWN BUSINESS IDEA (introduced and discussed on the Week 1 Discussion Forum). For this week you have to do case study 2 (Assignment 4) (similar to assignment 3, but with data for your own business idea).

Please make sure your submissions contain all the following:

Chapter 4 Exercises:

This chapter's file can be an Amortization Schedule similar to the Chapter 4 textbook project, or it can be another type of spreadsheet that meets the requirements listed below.
This chapter's files (the textbook exercise and the case study file) will be quite similar in content, but that's OK, just make sure your file follows the colour-scheme, font-style, etc. that you've been using so far.

Requirements:

• The section title, row titles, and system date are entered and formatted
• The sheet tab name is changed
• A number format is applied to applicable fields
• Company name and logo is added
• The Loan Amount formula is entered correctly
• The PMT function is used correctly
• Formulas are entered to determine the total interest and total cost using CELL NAMES in at least one location (not just cell addresses)
• Conditional formatting is applied somewhere in the spreadsheet
• Custom borders are added to the worksheet

Chapter 5 Exercises

This chapter's file can be a Consolidated Expenses Worksheet similar to the Chapter 5 textbook project, or it can be another type of spreadsheet that meets the requirements listed below.

This chapter's files (the textbook exercise and the case study file) will be quite similar in content, but that's OK, just make sure your file follows the colour-scheme, font-style, etc. that you've been using so far.

Requirements

• A consolidated worksheet is created pulling data from at least three other worksheets
• Formulas are entered using the ROUND function
• A new cell style is created for the date
• A 3-D pie chart has been added to the first sheet and formatted (exploded, rotated, and labelled)
• A header and footer have been added, margins changed, and centered horizontally

Chapter 6 Exercises

This chapter's file can be a Commission Table with an Extract Range similar to the Chapter 6 textbook project, or it can be another type of spreadsheet that meets the requirements listed below.

Requirements:

• Create a table
  • Format the range and column headings as indicated
  • Name the table
  • Remove duplicates
  • Enter new records
  • Add columns
  • Center the title
• Insert a calculated field as a Structured Reference
• Use the VLOOKUP function
• Apply conditional formatting AND icon sets
• Extract and query records with criteria ranges
• Use database functions, including DAVERAGE, DCOUNT, COUNTIF, MATCH, and INDEX
• Summarize the data
  • Convert the table into a range
  • Use the Subtotal command
  • Save with a different filename

Final project:

For the final project, create a workbook with ALL the spreadsheets created in the previous case studies AND the spreadsheets to be created in the previous part (for Chapter 6). LABEL YOUR TABS: i.e., Week 1-Budget, Week 1-Chart, Week 1-Budget_changed, Week 1-Chart_changed, Week 2-“Topic”, etc.

Then, submit this workbook as your final project to the appropriate submission link in VLE.

Note: if you think that an additional file (word, notepad or something similar) with some comments on the content of the Excel file will be helpful to the marker, you can also upload it, but be advised that this will not be marked.