Please provide step-by-step instructions for how to do: Excel Pivot Table/What-If Analysis
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
This question has not been answered.
Create a free account to get help with this and any other question!
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