CGS2518 Final Exam Review

Chapter 6

1. RATE: function to calculate the interest percentage per period of a financial transaction.
2. PV: function to calculate the value at the beginning of a financial transaction.
3. FV: function to calculate the value at the end of a financial transaction.
4. PMT: function to calculate the number of compounding periods in or out of a financial transaction.
5. NPER: function to calculate the number of compounding periods in a financial transaction.
6. TYPE: use a 1 for this argument to indicate that interest will be paid at the beginning of each compounding period.
7. SIMPLE INTEREST: interest is calculated based on original principal regardless of the previous interest earned.
8. COMPOUND INTEREST: interest calculated based on principal and previous interest earned.
9. SLN: function to calculate straight line depreciation based on the initial capital investment, number of years to be depreciated, and salvage value.
10. PPMT: function to calculate the amount of a specific periodic payment that is principal in a given payment.
11. IPMT: function to calculate the amount of a periodic payment that is interest in a given period.
12. CUMPRINC: function to calculate the cumulative principal paid between two periods. CUMIPMT: Cumulative interest payments
13. NPV: function to determine the value of a variable set of cash flows discounted to its present value.
14. IRR: function to determine the rate of return, where the net present value of the cash flows is 0.
15. =FV(.05/4,5*4,200,-6000)
16. =RATE(30*12,-3000,375000,0)
17. =NPER(6%/12,-370,205000,0)/12
18. =PMT(5%/12,15*12,1000000,-5000)
19. =PV(3%/4,4*3,0,15000)
20. =SLN(250000,10000,10)

Chapter 7
1. Steps to create structured list of data (from text file that contains values stored on separate lines)
=CONCATENATE combines info from many strings into one string
Each cell in the function contain the original data. The , in quotes are used to insert commas in between each arguments.
Use trim function to remove spaces in the text string,
Find & Sort info
2. Remove unnecessary spaces from data imported from another source because:
Spaces can cause errors in Excel formulas. Remove unnecessary spaces from a text string:
=TRIM(CONCATENATE(text))
3. Find function: returns starting position of one text value within another text value. Case sensitive. Finds one text string within another text string. Using start_num as starting point. Search function: function that returns starting position of one text value within another text value in the same manner as the FIND function but is NOT case sensitive.
4. Options for parsing data when using the Convert Text to Columns Wizard:
Identify the character that separates the data
Set field widths to identify the breaks between data that appears in columns.
5. Advantages/ Disadvantages of Subtotal tool:
Only works with one category and one subtotal calculation at a time.
Category can be changed by opening subtotal dialog box.
Can collapse/expand level of detail in report
Not available if working with Excel Table
6. Difference between unstructured list and an Excel list:
Excel table has: validation, sorting, filtering.
Use Excel list to manage large amounts of data when our database skills are not sufficient to use a database to manage the same data.
Unstructured list: need to determine format you need so it can be converted into structured list to be used on excel.
7. Options when using AutoFilter feature in Excel table:
Sort A-Z/Z-A
Create Custom Filter
Sort by Color
Clear Filter From
Text/Numbers/Date Filters
Display all records
Display top 10 records
8. Advantage of storing data in a database/importing that data into excel:
In a database, the data is protected from accidental changes, yet it is available for export into a spreadsheet for analysis.
9. Steps to import data stored in an Access database into Excel:
Click “From Access” (in Get External Data group on Data tab on