Chapter Five $$ Subtotals

Problems $(5$ points each$)$

Please provide the answers to the following questions using the Microsoft$$ Excel Exam. Use the tab C$5$ Subtotals and the tab C$5$ Subtotals for these questions.

$1)$ Using the sheet C$5$ SubTotals, create a subtotal for this dataset. The subtotal should display the Sum of Salary for each Department.

$2)$ Using the sheet C$5$ SubTotals$2,$ create two subtotals for this dataset. The first subtotal will display the Sum of Salary for each Department. The $2$nd subtotal will display the average Salary by Title within Department.

Chapter Five $$ PivotTables

Problems $(6$ points each$)$

Please provide the answers to the following questions using the Microsoft$$ Excel Exam. Use the tab C$5$ PivotTables for these questions.

$3)$ Using the sheet C$5$ PivotTables as a base, create a PivotTable called Analysis $($by naming the sheet Analysis$)$ which shows the Sum of Salary by Title within Department $($as a Stacked Row$).$ Apply any PivotTable Style to the PivotTable.

$4)$ Using the sheet C$5$ PivotTables as a base, create a PivotTable called More Analysis $($by naming the sheet More Analysis$)$ showing both the Sum of Salary and the Average of Salary by Department. Display the Sum of Salary as a Percent of Column Total and the Average of Salary with no calculation. Create a slicer for the field performance and display a Pie Chart as a PivotChart.

Chapter Six $$ What$-$If Analysis

Problems $(6$ points each$)$

Please provide the answers to the following questions using the given Microsoft Spreadsheet called Excel Exam $$ Part $2.$ Use the tab C$6$ What$-$If Analysis for these questions:

$5)$ For both Loan #$1$ and Loan #$2,$ use the corresponding Input Areas to calculate the following values for each loan:

a$.$ The Loan Amount which is the Cost of the Home minus the Down Payment

b$.$ The Monthly Interest Rate

c$.$ The Total Number of Payments to pay off the loan

d$.$ The Monthly Payment for the Loan $($Make sure it displays as a positive number$)$

e$.$ The Total Amount paid to the bank to pay off the loan $($Monthly Payment times Total Number of Payments$)$

f$.$ The amount of Interest paid on the loan once it is fully paid $($Total Amount Paid minus Loan Amount$)$

$6)$ Using Loan #$1,$ use the values from both the Input Area #$1$ and Output Area #$1$ on the loan to create a Data Table to determine the impact on Monthly Payment, Total to Repay the Loan, and the Total Interest Paid on the loan by changing the cost of the home from $$400,000$ to $$500,000$ by increments of $$10,000.$ Create this table starting in Column D and format all column headings to provide descriptions of those columns $($bolding and underline the column headings$)$ as well as format the values inside the table to display a Currency Format with $2$ decimal places. Also, format the changing values of Cost of Home as Currency with $0$ decimal places.

$7)$ Using Loan #$1$ again, create a $2$nd Data Table to determine the impact on Total to Repay the Loan if the cost of the home was between $$400,000$ and $$500,000($looking at the impact at a change increment of $$10,000)$ and the Interest Rate changed to values from $5\%$ to $7\%$ changing at an increment of $\mathrm{.}5\%).$ Format all data inside the table using the Currency Format with $2$ decimal positions. Place this Data Table next to the Data Table $($starting in Column I$)$ created in question #$2$ immediately to its right leaving one blank column between the tables. Place a label on the cell at the intersection of the changing home values and the changing Interest Rate values which says $$Cost$/$APR$$ and bold$/$underline both this label and the values for the changing Interest Rates.

$8)$ Using Loan #$2,$ use Goal Seek to determine the exact Cost of Home which would be required to have an exact Monthly Payment of $$2000.$

$9)$ Using Scenario Manager, create a Scenario Summary Report comparing the three loans entered in the Loan Scenario Section on the spreadsheet using the formulas which have been created in Loan #$3$ and use Loan #$3$ as the basis for your Scenario Summary Report. Make sure the Scenario Summary Report shows the impact of entering all the components of the loan $($Amount of Loan, Down Payment Required, Annual Interest Rate and Total Number of Payments$)$ and their impact on Monthly Payment, Total to Repay Loan and Total Interest Paid.

$10)$ Using Solver, create an Answer Report for Loan #$4$ where Solver is allowed to change Down Payment, Annual Interest Rate and Total Number of Payments to find the minimum value of Total to Repay Loan with the following constraints:

a$.$ Down Payment must be between $$15,000$ and $$60,000$

b$.$ Interest Rate must be between $2\%$ and $5\%$

c$.$ Total Number of Payments must be between $250$ and $300.$

d$.$ Total Number of Payment must be an Integer

Chapter Seven $$ Specialized Functions

Problems $(6$ points each$)$

Please provide the answers to the following questions using the given Microsoft Excel Spreadsheet called Excel Exam $$ Part $2.$ Use the tab C$7$ Specialized Functions for questions $11-16$ and C$7$ Amortization Table for q