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
in your formula.
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.
19. Save your Workbook.
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
Error Alert
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
Title: Comments
Message: Enter additional
comments here. Comments are
restricted to 100 characters.
Type: Warning
Title: Invalid Comments
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
24. Save your file.
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.

Unformatted Attachment Preview

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 in your formula. 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. Page 1 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. 19. Save your Workbook. 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. Page 2 of 4 22. Create Data Validation for the following cells: Cell Validation B4 Date Date Purchased >=5/25/2020 B6 List Source: Range (A13:A15) B8 Decimal Data between $1.99 and $500 B9 Text Length Data between 0 and 100 Input Message Title: Date Purchased Message: Enter the date purchased. Title: Classification Message: Select Classification from the drop-down arrow Error Alert Type: Stop Title: Invalid Date Purchased Message: You must enter a date >=5/25/2020 Type: Stop Title: Invalid Classification Message: Select a valid Classification from the drop-down list Title: Purchase Price Type: Stop Message: Enter purchase price Title: Invalid Purchase Price Message: Enter a valid purchase price between $1.99 and $500 Title: Comments Type: Warning Message: Enter additional Title: Invalid Comments comments here. Comments are Message: You have exceeded 100 characters. restricted to 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 24. Save your file. 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. Page 3 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. Page 4 of 4 Name: Description: ...
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.
Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4