# Test #3: Application Question

Test_3_Application_Instructions Page 1
Test #3: Application Question Instructions
Complete the following steps on your own. You may not communicate with anyone in any way (except
Workshops 1-4) to help you complete the following steps. You can use the checkboxes next to each step
1. ☐Open Excel 2010 or 2013 and open the sample template, Loan Amortization. You will calculate
information for a business loan. The loan amount is 425,000. The annual interest rate is 6%. The
loan period is 15 years. The number of payments per year is 12. The start data is 12/1/2012. There
are zero optional extra payments.
2. ☐Use the Snipping or Grab tool to select A1:J41. This will give you the loan amortization
information through payment no. 24. If you need to use a screen shot, crop it so that only A1:J41 are
shown. Note that you will not save this file.
3. ☐Open a new blank Excel workbook (using Excel 2010). Paste the object in Sheet1.
4. ☐Name the .xlsx file – YourSectionNumber_YourLastName_AppTest_3 (It will look similar to
111090_Morris_AppTest_3.xlsx)
5. ☐In Sheet1, add your company name to A1. Move the loan amortization object below A1.
6. ☐In Sheet1, calculate the total payments for 2013, the total amount of principal paid in 2013, and
the total amount of interest paid in 2013. You can use a calculator. Include this information below
the loan amortization object. Format the information per the rules.
7. ☐In Sheet1, calculate the total payments for 2014, the total amount of principal paid in 2014, and
the total amount of interest paid in 2014. You can use a calculator. Include this information below
the object. Format the information per the rules.
8. ☐In Sheet1, change the page layout to landscape. Adjust the size of the object so that this sheet
prints on one page.
9. ☐Change the name of Sheet1 to Loan Information.
10. ☐In the next sheet, name the sheet tab, Expense Analysis
11. ☐In the next sheet, type the data in black font in the screen shot below. Note that information in red
font means you will need to do something more than just typing the data.
Test_3_Application_Instructions Page 2
12. ☐In B5, format the cell to percentage with two decimal places.
13. ☐In B9 and B10, type the numbers that you calculated in Step 6
14. ☐In D9 and D10, type the numbers that you calculated in Step 7.
15. ☐Insert the other functions or formulas; see the above screen shot.
16. ☐Add your company logo (that you used or created during Word).
17. ☐Add a pie chart that includes the data for the FY 2013 Actual for Salaries and Wages for FY. You
will have two “slices.” Move the chart object to the bottom. Format it per the rules.
18. ☐Add a pie chart that includes the data for the FY 2013 Actual – Social Security, Medicare, FUTA, and
SUTA. You will have four “slices.” Move the chart object to the bottom. Format it per the rules.
19. ☐Format the sheet per the business rules. Keep the page layout of portrait, but adjust the column
widths and the size of the pie charts so this sheet prints on one page.
20. ☐You need to add a summary chart in its own sheet that will show the four subtotals and show both
FY 2013 Actual and FY 2014 Budget data. This means you will have “Subtotal: Loan” with one series
as Actual next to Budget – then “Subtotal: Salaries” with one series as Actual next to Budget, etc.
Select the correct data and an appropriate chart.
21. ☐Move it to a chart sheet. Name the chart sheet, Subtotal Chart. Format it per the rules.
22. ☐Move the sheets so the first one is Expense Analysis, the second one is Loan Information, and the
third one is Subtotal Chart. Delete any other unused sheets.
23. ☐Add a custom footer to all three sheets. In the left area, add the sheet tab name using the sheet tab
field button. In the right area, type – Created by YourName.
Test_3_Application_Instructions Page 3
24. ☐Refer to the rubric and/or the sample in in Bb to verify you completed all the steps. Verify your
that each sheet prints on one page and the proper footer appears. Save the file and close it.
a. Click on Application Test: Spreadsheet
b. Under the Submissions area, click the “Upload” button.
c. Locate the file.
d. Select the file and click “Open.”
e. Verify you see your file name attached in the Submission area.