+254 704 052992

please do this

For the next several tasks, use the Burger Hut Data.

Make a copy of the Burger Hut Data.

  1. We should always make a copy of the data sheet so that you manipulate a copy of the data rather than possibly destroying the original data.
  2. Create a copy of the Burger Hut Data on a new worksheet named “BurgerHutData Copy” without the quotes.
  3. Copy the whole worksheet including the blank first row and column.
  4. Notice there are not any spaces within the text BurgerHutData and there is a space between the text BurgerHutData and Copy.

1Create a PivotTable to calculate the total sales for each product.

  1. Create a PivotTable on the SumOfProducts worksheet.
  2. Show the totals of all the kinds of products sold at Burger Hut.

7Create a PivotTable to calculate summary statistics on Classic Burger sales.

  1. Create a PivotTable on the ClassicBurgerStats worksheet.
  2. Show the SUM, AVERAGE, MIN, and MAX Classic Burgers sold.

4.5Create a PivotTable to examine if coupons affect Classic Burger sales.

  1. Create a PivotTable on the ClassicBurgerByCoupon worksheet to answer the question, “Did the coupons affect the sales of Classic Burgers?”
  2. Show the AVERAGE, MIN, and MAX Classic Burgers sold.
  3. Add the Coupon dimension to the PivotTable.
  4. Make a PivotChart for the PivotTable data.

5.5Create a PivotTable to examine which product sales are seasonal.

  1. Create a PivotTable on the ProductSalesByDate worksheet to answer the question, “Which of the products have sales that are seasonal?”
  2. Show total product sales by date for each product.
  3. Expand year to quarter and quarter to month to show sales by year, quarter, and month.
  4. Remove the display of the Year Subtotals from the PivotTable.
  5. Create a Line PivotChart from the PivotTable report to see the change in values over time.
  6. Click on the collapse entire field button and then the expand button to see how the PivotChart changes. +/- buttons in the bottom right corner of the chart.
  7. Sort the table by years from the newest to the oldest.
  8. Add conditional formatting (data bars) to the Jalapeno column.

8Create a PivotTable that adds a field to calculate the ratio of Avocado to Classic burger sales.

  1. Create a PivotTable on the CalculatedField1 worksheet.
  2. Show the sum of all products in the PivotTable.
  3. Create a column PivotChart of the PivotTable to show a comparison of the product sales.
  4. Insert a calculated field named “PercentAvocadoToClassic” (without the quotes) with the formula =Avocado/Classic.
  5. Change the number format of the calculated field to % with one decimal place.

6Create a PivotTable to calculate the total of shakes, total of fries, and total of burgers (Avocado, BBQCheddar, Jalapeno, and Classic).

  1. Create a PivotTable on the CalculatedField2 worksheet.
  2. Show the total of shakes, total of fries, and total of burgers (Classic, Avocado, BBQCheddar, and Jalapeno ).
  3. Hint: use a calculated field, named “Burgers” (without the quotes) to create the total of Burgers.

7Create a PivotTable to examine sales between dates.

  1. Create a PivotTable on the PerDiffFrom worksheet showing the total of Burgers sold.
  2. Place months as the row field and years as the column field.
  3. Change that metric to show the % difference from the previous date.
  4. Make a PivotChart for the PivotTable that shows the change in sales over time.

9

2

Study the ClassicBurgerByCoupon PivotTable. True or false: coupons affect sales of classic burgers.

3

Study the ProductSalesByDate PivotTable. Sales for which product appear to be seasonal?

4

Bad DriversThe data on the BadDriversData worksheet displays the percentage of drivers involved in fatal collisions by state. Use the BadDriversData worksheet as the data for the PivotTable questions.

Create a PivotTable on the Speeders worksheet.

  1. Show the 5 worst states for fatal collisions involving speeding.
  2. Sort the PivotTable values from largest to smallest.

12Create a PivotTable to show the 10 states that have the highest percent of fatal collisions involving distracted driving.

  1. Place the PivotTable on the DistractedDriving worksheet.
  2. Use a calculated field name “DistractedDriving” (without the quotes) to determine the worst states (Hint: calculate by subtracting from 100).
  3. Show the 10 states with the highest percentage for fatal collisions involving distracted driving.
  4. Sort the PivotTable values from largest to smallest.

18Create a PivotTable to show the 10 states in which the insurance companies are making the best ROI.

  1. Place the PivotTable on the BestROI worksheet.
  2. Use a calculated field named “BestInsROI”(without the quotes) Hint: ROI is 1 minus the ratio of insurances losses to premiums. The ratio is calculated as Insurance companies losses divided by Car Insurance Premiums. Note that the Car Insurance Premiums ($) field contains the average premium for all customers in each state.
  3. Display the values as a percentage with one decimal place.
  4. Show the 10 states where insurance companies are making the best ROI.
  5. Sort the PivotTable from largest to smallest.

20

admin