Need excel sheet for part2,3, AND 4 and all the answers for the questions .Thank you

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.)

1. What is the total storage requirements (in bytes) over all tables in years 1, 2, and 3.

2 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:

1 How many values are in the green range?

2 Are there more value below or above the green range?

3 Change the record size for the Trade-in Vehicle table from 48 to 5 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.

4 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.

1 Find the salesperson with the highest total sales. What is the name of the salesperson and what is the total sales for the salesperson?

2 Which area had the lowest total sales. What is the name of the area and what is the total sales for the area?

3 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?

4 What are the sales for each of the salespeople in the SouthEast area per quarter. How many salespeople are there?

5 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?

6 Which customer purchase the most product in terms of total sales? How about in terms of quantity of products?

7 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?

8 Which customers, if any, have sales in multiple areas? What are the name(s) of these customer(s)?

9 How many shredders were sold in each quarter in each area?

10 Which salesperson sold the most printers in total and in which quarters did he sell them? How many total printers did he sell?