Please provide step-by-step instructions for how to do: Excel Pivot Table/What-If Analysis

timer Asked: Dec 6th, 2015

Question description

Please provide detailed, step-by-step instructions for how I can complete this worksheet: Excel Pivot Table, What-If Analysis. I am looking up videos online and they are not helping.

Create Pivot Table1, Pivot Table 2, Pivot Chart, Excel Table 1 based on the information listed on the 4K Video Rental worksheet. Run simulations based on the data included on the Car Loan worksheet. Follow the instructions provided on the next pages. 

1. Pivot Table 1:

  •   Use the 4K Video Rental information and create a Pivot Table to display a table of Replacement Costs with the Release Years as row labels an Categories as column labels.

  •   Filter the information so that only information for the Ratings PG-13, R, NC-17 are displayed.

  •   Format the monetary numbers shown as Currency with no decimals.

  •   Rename the PivotTable's worksheet: Pivot Table 1.

    2. Pivot Table 2:

  •   Use the 4K Video Rental information and create a Pivot Table that displays the Amounts organized by the Payment Month and Store ID as row labels (in that order.)

  •   Show the amount values as % of Grand Total.

  •   Collapse the Store ID rows and create a 3D Pie Pivot Chart.

  •   Apply Chart Style 5 and Chart Layout 2.

  •   Enter as Title of the Chart: % of Revenue per Month.

  •   Rename the PivotTable's worksheet: Pivot Table 2.

  •   Move the Pivot Chart to its own worksheet. Rename this new worksheet as Pivot Chart.

    3. ExcelTable1:

    •   First, Create a copy of the 4K Video Rental worksheet (right-click on the 4k Video Rental tab -> Click on ‘Move or Copy’ -> check ‘Create a Copy’ -> Click on ‘Move to End’)

    •   Rename the worksheet just created as Selected Video Rentals

    •   Convert the Selected Video Rentals dataset into an Excel Table.

    •   Sort the information in ascending order by Category.

    •   Filter the Replacement Cost values to display only those greater than or equal to 15.

    •   Filter the Store IDs to display only information for store 1

  •   Filter the Payment Dates to display only those with a date of 6/29/2015 or greater.

  •   Create a new column: days_rented

  •   [Tip: For the following formulas, use the formula notation specific to Excel Tables which uses square brackets around the names of the columns]

  •   Use the following formula for all cells in the Days_Rented:

 days_rented = return_Date – rental_Date

  •   Create a new column: diff_limit

  •   Use the following formula for all cells in the diff_limit column:

 diff_limit = rental_duration - days_rented

  •   Create a Total row.

  •   On the Total row created, calculate the average for rental_duration and days_rented.

  •   Apply Table Style Medium 5. 4. What-IfAnalysis

    •   Use the Projections worksheet to solve this What-If Analysis question.

    •   A company has calculated an Initial Earnings Estimate of $80 million for next year. However, that Initial Estimate can be affected by factors such as the rate of growth of the economy, an intense competitive market and changes in the tax rates.

    •   Each of the factors mentioned will affected the estimated by an estimated percentage. As an example, a combination of a high growth of the economy, strong competition in the marketplace and a stable tax rate will update the initial estimate by7% -2%+0%=+5%.

    •   Create a formula – include appropriate functions - to produce an Updated Initial Estimate based on the three tables on the top section of the spreadsheet.

    •   Enter your formula on the light blue cell of the worksheet.

    •   The company wishes to run several simulations to updated its initial estimate.

    •   Create the following scenarios. The data listed indicates the changing data for each scenario:

    •   Scenario 1: Growth: High, Competition: Weak, Taxes: Stable

    •   Scenario 2: Growth: Medium, Competition: Stable, Taxes: Decrease

    •   Scenario 3: Growth: Low, Competition: Weak, Taxes: Decrease

  •   Scenario 4: Growth: Medium, Competition: Strong, Taxes: Increase

  •   Scenario 5: Growth: High, Competition: Stable, Taxes: Increase 


Studypool has helped 1,244,100 students
flag Report DMCA
Similar Questions
Hot Questions
Related Tags

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors