Showing Page:
1/4
Page 1 of 4
COMP 1003 Final Exam S5
Modules 9 and 12
1. Open datafile S20 Final Exam Datafile aet and save as LASTNAME_FirstName_Student#.
Example: SINGH_John_1234567
2. In the Amortization Schedule worksheet, use the PMT function to calculate the quarterly payments in
cell G4.
3. In the range C8:F27 complete the amortization schedule as follows, use absolute references where
necessary:
a. In cell C8 enter a reference to the Loan (PV) in A4
b. In cell D8 use the IPMT function to calculate the interest payment for each quarter.
c. In cell E8 use the PPMT function to calculate the principal payment for each quarter.
d. In cell F8 calculate the Total Payment for period 1
e. In cell C9 calculate the Remaining Principal for period 2
f. Use the fill handle to copy the formulas in C9, D8, E8, F8
g. Copy the formula from C27 to C28 to verify that the loan is completely repaid by displaying the
value of the remaining principal (should be 0).
4. Calculate the cumulative interest and principal payments per year as follows:
a. In the range B34:F34 use the CUMPRINC function to calculate the cumulative principal
payments in each of the first five years for the loan. Use absolute references where necessary
b. In the range B35:F35 use the CUMIPMT function to calculate the cumulative interest payments
in each of the first five years of the loan.
c. In cells G34 and G35, calculate the total principal payments and interest payments in the first
five years of the loan.
d. In the range B36:F36, calculate the remaining principal at the end of each of the first five years
of the loan.
5. Switch to the Income Statement worksheet.
Showing Page:
2/4
Page 2 of 4
6. In the range B7:F7, project the company’s income and expenses for the next five years by interpolating
the Year 2 through Year 4 revenue assuming a growth trend.
7. In the range B8:F8, use the Percent Cost of Sales and Marketing (cell F2) to calculate the Year 1 through
Year 5 Cost of Sales and Marketing. (HINT: just a simple formula)
8. In the range B9:F9, use the Percent Cost of Research and Development (cell F3) to calculate the Year 1
through Year 5 Cost of Research and Development. (HINT: just a simple formula)
9. In the range B10:F10, calculate the gross profit by subtracting the cost of Sales and Marketing and
Research and Development from the annual projected sales.
10. In the range B13:F13, extrapolate the Year 2 though Year 5 Payroll expenses by assuming the payroll
grows by 15 percent each year. In B14:F14 extrapolate the other expenses by assuming they grow by
7% per year from the initial year 1 values.
11. In the range B18:F18, calculate the Total General Expenses for Years 1 through 5.
12. In the range B21:F21, calculate the company’s Initial Earnings for each year, equal to the Gross Profit
minus the Total General Expenses. (The Depreciation in row 22 will be calculated later.)
13. Switch to the Depreciation Sheet.
14. In the range B9:F9, calculate the Yearly straight-line depreciation of the long-term assets using the SLN
function. In the range B10:F10, calculate the cumulative depreciation through the first five years. In the
range B11:F11, calculate the depreciated value of the assets at the end of each of the first five years.
15. In the range B15:F15, use the DB function to calculate the yearly declining balance of the assets. In the
range B16:F16, calculate the cumulative depreciation of the assets. In the range B17:F17, calculate the
depreciated value of the assets at the end of each year.
16. Switch back to the Income Statement worksheet.
17. In the range B22:F22 enter formulas to reference the declining balance depreciation values in the
range B15:F15 of the Depreciation worksheet. (HINT: simple link)
18. Calculate the company’s operating profit in B23:F23 by subtracting the Yearly Depreciation from the
Yearly Initial Earnings.
20. Switch to the Macro Input worksheet.
21. Create a WordArt Title “Raja’s Memorabilia” using the Gradient Fill, Grey style, apply the Text Fill
Colour, Blue. Change the font size 26 pt. Move the WordArt to cells A1:B1.
Showing Page:
3/4
Page 3 of 4
22. Create Data Validation for the following cells:
Cell
Validation
Input Message
B4
Date
Date Purchased
>=5/25/2020
Title: Date Purchased
Message: Enter the date
purchased.
Type: Stop
Title: Invalid Date Purchased
Message: You must enter a date >=5/25/2020
B6
List
Source: Range
(A13:A15)
Title: Classification
Message: Select Classification
from the drop-down arrow
Type: Stop
Title: Invalid Classification
Message: Select a valid Classification from the
drop-down list
B8
Decimal
Data between \$1.99
and \$500
Title: Purchase Price
Message: Enter purchase price
Type: Stop
Title: Invalid Purchase Price
Message: Enter a valid purchase price
between \$1.99 and \$500
B9
Text Length
Data between 0 and
100
restricted to 100 characters.
Type: Warning
Message: You have exceeded 100 characters.
23. Enter the following data:
Cell B4: 28/6/2020
Cell B5: Dancing Woman
Cell B6: Print
Cell B7: 28” x 24”
Cell B8: 499.99
Cell B9: By famous Indian artist Rabindranath Tagore
25. Record a new macro named: “Transfer_To_Sales_List”. This macro should do the following:
Go to Macro List sheet
Right click Row 2 header, insert a blank row
Go to Macro Input Sheet
Copy the Range B4:B9
Go to Macro List Sheet and Paste Special, Values only and Transpose in Cell A2
Go to Macro Input Sheet, clear data in cells B4:B9 then click B4
Stop Recording
26. Add a button to run the macro, edit the button text to “Transfer To Sales List
Size the button to fit over cells B11:B15, font Dubai Medium, Size 14 pt. Bold, Colour Light Blue
27. Unlock the cells in the range B4:B9.
28. Apply Worksheet protection for unlocked cells only without a password.
Showing Page:
4/4
Page 4 of 4
29. On the Macro Input sheet, enter the following data:
Cell B4: Today’s date
Cell B5: Your Name Portrait e.g. Anne’s Portrait
Cell B6: Collectible
Cell B7: 18” x 24”
Cell B8: \$50
Cell B9: My famous Excel Expert portrait!
30. Use your macro button, Transfer To Sales List
31. Save your workbook as a Macro Enabled Workbook.
32. Submit your macro enabled exam solution in the Assignment drop box in eConestoga.