Virtual Lab 1
In this lab, we will explore the use of spreadsheet software to perform some simple data analysis. While spreadsheets can be used to perform simple, straightforward (though tedious) calculations, spreadsheets such as Microsoft’s Excel can also perform more sophisticated data analysis and what if analysis.
The following is a set of videos that provide instruction on the basics of writing Microsoft Excel spreadsheets, along with instructions as to how to use some of the advanced features:
https://www.youtube.com/watch?v=ONMaFt0j-7A: Spreadsheet Analysis Methods
https://www.youtube.com/watch?v=rJbf-2XXsuY: Microsoft Excel Tutorial: A Basic Introduction
https://www.youtube.com/watch?v=jTthq0yjNQA: Excel’s Business Tools – What-if Analysis
https://www.youtube.com/watch?v=9NUjHBNWe9M: Microsoft Excel Pivot Table
The following link, Excel Easy) is a short reference to some of the topics covered in the above videos.
The lab below consists of four parts. You will submit a single workbook (a .xls file) for this lab, but each of the parts should be in a separate worksheet (see https://www.youtube.com/watch?v=rwbho0CgEAE : The Beginner’s Guide to Excel – Excel Basics 2017 Tutorial – Youtube). In addition to your Excel Workbook, you must also submit a PDF document with your responses to the questions asked in this lab (the questions to answer in the PDF document will appear in red.). Label each response with the lab part number and the question number within the part.
Part 1: A basic spreadsheet with formulas (totals, means, medians and standard deviations)
You are given the following information in the data file Part1Lab1Data.xlsx, which consists of sales data for a company with eight salespeople. Copy and paste this data to a new workbook that you will submit for this assignment. Rename the worksheet hold the data you just copied to Part 1.
For each salesperson, the company has their sales for the last year broken down by month. The company wants you to summarize the data as follows:
for each sales person, define columns which show the
total sales for the sales person
the mean (average) sales for the salesperson
the median sales for the salesperson
the standard deviation of the mothly sales for the sales person
for each monthly sales, define rows which show the
total sales for the month
the mean sales for the month
the median sales for the month
the standard deviation of the sales for the month
Once you have the columns and rows described above defined, sort the table by highest total sales first. Then, answer the following questions (It may be useful to define additional columns and rows to answer these questions or to resort the table; if you modified the table to answer the question, indicate what modfiication you made.):
Which saleperson had the highest sales of any salesperson?
For which month were the mean and median sales the closest?
Which salesperson had the highest variability of sales as measured by the standard deviation? Which salesperson had the lowest variability of sales?
During which month did Sally have the smallest difference between her monthly sales and the mean over all her sales? (This is easily done by adding a row that calculates the difference between Sally’s monthly sales and her mean sales.)
Part 2: Projecting Growth
For this part of the lab, use the data in the file Part2Lab1Data.xlsx. Copy and paste the data in this file to a new worksheet in your workbook and do all of the work for this part in the new worksheet. Call the new worksheet Part 2.
In the new information system under development for Domestic Car Sales, seven tables will be implemented in the new relational database. These tables are: New Vehicle, Trade-in Vehicle, Sales Invoice, Customer, Salesperson, Installed Option, and Option. The expected average record size for these tables (in bytes) and the initial record count per table are given in the file Part2Lab1Data.xlsx. The company wants to project the growth of the tables over the next three years in order to ensure that appropriate hardware is obtained to handle the increase data load. The company assumes that each table has a 35% storage overhead above the storage required for just the data records, themselves. The company assumes the the company (and the size of the tables) will grow at a rate of 10% per year. Add the columns to show the total storage require by each table, including overview along with the growth for the three year period. When writing the formula to include overhead and the formula to calculate growth, use the cells label Overhead and Growth Rage in your formulas.
Answer the following questions (again, if you make additions or modify the table to answer the questions, indicate the modification.)
What is the total storage requirements (in bytes) over all tables in years 1, 2, and 3.
What is the change in total storage requirements (in bytes) going from year 1 to 2 and from year 2 to 3.
Part 3: What-if analysis
For part three of this lab, we will use the table you built from part 2, but in a new worksheet called Part3. Copy and paste your table from the Part 2 worksheet (be sure to copy the Overhead and Growth Rate cells along with the rest of the table).
If you did not do so in part 2, ensure that your formulas reference the Overhead and Growth Rate cells and do not simply use the constants .35 and .1, otherwise you will not be able to perform the what if analysis.
We will be using the Data table method using both the row and column variables. We will focus on a what if analysis of the third total size of the Trade-in Vehicle table. As in the video tutorial for what if analysis, you need to do the following steps:
Create a new cell that refers to the third year total storage for the Trade-in Vehicle table.
Create a column of overhead percentages directly underneath your new cell contain the Trade-in Vehicle table size, starting from .05 up to .5 in .05 increments.
Create a row of growth rate directly to the right of your new cell, starting from .10 to .15 in .01 increments.
Select the rectangle containing all of these cells.
Go to the Data Table option in the What-if section and place the cell with the Overhead in the column input field and the cell with the Growth Rate in the row input field. Click OK.
You now have a table of number showing the size of the Trade-in Vehicle tables for all combinations of Overheads and Growth Rates.
In this step, you’ll set up some conditional formatting for the values in the Trade-in Vehicle table size values in your What-If Data Table using the original year 3 Trade-In table size (the value should be in cell F3). Using the Conditional Formatting tool, show the values within a .05 range of the original year 3 Trade-In table size in green (use the between condition), values below this range in red, and value above this range in yellow.
Answer the following questions:
How many values are in the green range?
Are there more value below or above the green range?
Change the record size for the Trade-in Vehicle table from 48 to 53. Did the number of green value increase or decrease? What is the new number? Change the record size to 43 and answer the same two questions.
Restore the record size for the Trade-in Vehical table to 48 and change the number of records from 7500 to 7000. Did the number of green value increase or decrease? What is the new number? Chage the number of records to 8000 and answer the same two questions.
Part 4: Pivot tables
For this part of the lab, you will use the data in the Part4Lab1Data.xlsx file. Copy and paste this data into a new worksheet and call the worksheet Part 4. This file contains sales information for a company that sales paper, ink, printers and shredders nationally. The company has six regional areas: NorthEast, SouthEast, MidWest, NorthWest, SouthWest, and the WestCoast. Sales are booked quarterly by quanitity of an item and price per item by each salesperson in each area. The data is presented as a table with a row for each sale with the following information in each column:
the salesperson
area
the product that is sold
the customer the product was sold to
the quarter in which the sale was made
the quantity sold
the price per unit
and the total for the sale
A pivot table will allow you to reorganize this data to answer a lot of questions.
Do the following
Select a column in the data and create a new sheet with a pivot table by going to the Insert tab and clicking Pivot Table.
Check that the range is correct and allow a new worksheet to be created and click OK.
Change the name of the new worksheet to Part4Lab1PivotTable.
Answer each of the questions below using the pivot table row, column, value, and origin areas.
Find the salesperson with the highest total sales. What is the name of the salesperson and what is the total sales for the salesperson?
Which area had the lowest total sales. What is the name of the area and what is the total sales for the area?
Suppose that you consider only sales of printers and shredders (combined sales) (use the origin field to filter the product type). What is the name of the area with the lowest total sales and what are the sales?
What are the sales for each of the salespeople in the SouthEast area per quarter. How many salespeople are there?
Consider the same scenario as above, but consider only sales of paper and ink (combined). What are the sales for each of the salespeople in the SouthEast area per quarter now?
Which customer purchase the most product in terms of total sales? How about in terms of quantity of products?
Did any customer make purchases every quarter? If so, what is the customer(s) name and were the customer(s) purchases per quarter and in total?
Which customers, if any, have sales in multiple areas? What are the name(s) of these customer(s)?
How many shredders were sold in each quarter in each area?
Which salesperson sold the most printers in total and in which quarters did he sell them? How many total printers did he sell?