Question #$1$:

Open the Test$3\_$Q$1\_$FirstNameLastName datafile and go to the Summary worksheet. In cell A$5,$ create a formula using the SUM function and $3-$D references to total cell $G15$ on the Philadelphia worksheet and cell $G15$ on the Springfield worksheet.

Elizabeth would like you to calculate the total service agreement sales for both offices.

Format cell A$5$ using the Accounting number format with two decimal places and the $ symbol.

Elizabeth would like you to separate the customer names from their invoice numbers and format the names with the last name first.

Enter the text Lowell, Ken in cell E$5,$ then enter the text Casey, Jon in cell E$6.$ Then, select the range E$5$:E$22$ and use Flash Fill to fill in the range E$7$:E$22.$

$P$&$H$ is considering expanding the business to add a third office. Elizabeth asks you to help with the analysis of loan options to fund the expansion.

Switch to the Expansion worksheet, then create a formula using the PMT function in cell B$8$ to determine the monthly payment using the loan information shown in the range $B5$ : $B7.$ In your formula, divide the rate $($cell $B6)$ by $12,$ use the term in months $($cell B$7)$ as the nper, and use a negative value for the pv $($cell B$5).$ Copy the formula from cell $B8$ into the range C$8$:D$8.$

In cell B$9,$ enter a formula without using a function to calculate the total payments by multiplying the monthly payments $($cell B$8)$ by the term in months $($cell B$7)$ for Loan Option $1.$ Copy the formula from cell B$9$ into the range C$9$:D$9.$

In cell B$10,$ enter a formula without using a function that calculates the total interest by subtracting the loan amount $($cell B$5)$ from the total payments $($cell B$9)$ for Loan Option $1.$ Copy the formula from cell B$10$ into the range C$10$:D$10.$

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Upload your workbook to the Midterm Exam drop box.