CGS 2518 ARMSTRONG EXAM
QUESTIONS WITH COMPLETE
ANSWERS
=PMT(5.0%/12,15*12,100000,-5000)
=PMT(rate, nper, pv, [fv],[type]) - Answer-Consider a $100000 mortgage at 5% annual
interest compounded monthly, to be paid back over the next 15 years. The loan will
have a $5000 balloon payment due at the end of the loan. Write an excel formula to
determine the payment that must be made each month on this loan.
=PV(3.0%/4, 3*4, 0, 15000)
=PV(rate, nper, pmt, [fv], [type]) - Answer-Assume that you have been left an
inheritance and want to save part of it toward the purchase of a car upon graduation,
which is three years from now. Write an Excel formula to determine the amount of
money you need to invest now to have $15000 at the end of the three-year period.
Assume that you will place this money in a CD that pays 3% interest compounded
quarterly and that you will be making no additional deposits into this account.
=-SLN(250000,10000,10) - Answer-Write an Excel formula to determine the amount of
money that can be depreciated each year, using straight line depreciation, for a new
packaging machine purchased by your company. The machine originally cost $250000
and has a useful life of 10 years and an estimated salvage value of $10000.
Data>Insert>Insert from Text>"Okay" on popup wizard - Answer-List and describe the
steps you would take to create a structured list of data from a text file that contains
values stored on separate lines.
It may cause errors in Excel; you remove the unnecessary space by using the trim
function. (=Trim([text]) - Answer-Why should you remove unnecessary spaces from data
imported from another source? How do you remove unnecessary spaces from a text
string?
The FIND (=FIND( find_text, within_text, [start_num] )function returns the starting
position of one text value within another text value. It is also case-sensitive. The
SEARCH function does the same thing as find, however it is not case-sensitive. -
Answer-How do the FIND and SEARCH functions work, and how are they different?
First way is to identify the character that separates the data. The second way is to set
field widths to identify the breaks between data that appears in columns - Answer-What
options are available for parsing data when you use the Convert Text to Columns
Wizard?
, The advantages are that it creates summary reports that quickly organize data into
categories with subtotal calculations and lets you collage and expand the level of detail
in the report. A disadvantage of the subtotal command is that it works only with one
category and one subtotal calculation at a time. - Answer-What are the advantages and
disadvantages of using the Subtotal tool to analyze data?
The difference between an unstructured list and an Excel list is that because all the data
in an excel list are in a structured format, one can use the filter feature to sort through
the data using different methods. - Answer-What is the difference between an
unstructured list and an Excel list?
Sort A to Z, Sort Z to A, Sort by Color (if cells are manually or conditionally changed to
have background color, it filters these colors), Clear Filter From (removes any filters and
restores original data), Text Filters (filters alphanumeric text by specific characters),
Number Filters, Date Filters (filters by date and time values),(Custom) - Answer-List and
describe the six available options when using the AutoFilter feature in an Excel Table
It ensures that your data is secured and protected but can be analyzed using a
spreadsheet easily, thus reducing redundancy. - Answer-What is the primary advantage
of storing data in a database and importing that data in Excel?
Open a workbook where you want to import the database data. Select the first blank
cell, and then click the From Access button in the Get External Data group on the Data
tab on the ribbon. In the dialog box, find the file, select it, and click to open it. - Answer-
Explain the steps you must take to import data stored in an Access database into Excel
The query wizard lets you choose your data source and select the database table and
fields you want to import into a workbook. It prompts you to define any criteria for the
data by selecting row to meet those criteria. You would use the query wizard when you
are trying to select specific data prior to importing it into Excel. - Answer-What is the
Query Wizard and when would you use it?
Excel uses serial numbers to represent dates and times. For dates, Excel represents it
as the number of days since January 0, 1900. Therefore January 1, 1900 would be
represented by the serial number 1. Times are represented by decimals. Noon on
January 1, 1900 would be represented by the serial number 1.5. - Answer-How does
Excel store date and time values?
YEARFRAC(start_date,end_date,basis) .the basis argument of 1 uses actual values as
in the correct number of days per month, using 0 as the basis causes the function to
calculate the months to have only 30 days and years using 360 days. - Answer-What
are the arguments for the YEARFRAC function? What are the possible values for
calculating months are years using this function?
You should use a PivotTable report to analyze data when you need a way to summarize
and analyze data from different perspective such as by month, quarter and year. It
QUESTIONS WITH COMPLETE
ANSWERS
=PMT(5.0%/12,15*12,100000,-5000)
=PMT(rate, nper, pv, [fv],[type]) - Answer-Consider a $100000 mortgage at 5% annual
interest compounded monthly, to be paid back over the next 15 years. The loan will
have a $5000 balloon payment due at the end of the loan. Write an excel formula to
determine the payment that must be made each month on this loan.
=PV(3.0%/4, 3*4, 0, 15000)
=PV(rate, nper, pmt, [fv], [type]) - Answer-Assume that you have been left an
inheritance and want to save part of it toward the purchase of a car upon graduation,
which is three years from now. Write an Excel formula to determine the amount of
money you need to invest now to have $15000 at the end of the three-year period.
Assume that you will place this money in a CD that pays 3% interest compounded
quarterly and that you will be making no additional deposits into this account.
=-SLN(250000,10000,10) - Answer-Write an Excel formula to determine the amount of
money that can be depreciated each year, using straight line depreciation, for a new
packaging machine purchased by your company. The machine originally cost $250000
and has a useful life of 10 years and an estimated salvage value of $10000.
Data>Insert>Insert from Text>"Okay" on popup wizard - Answer-List and describe the
steps you would take to create a structured list of data from a text file that contains
values stored on separate lines.
It may cause errors in Excel; you remove the unnecessary space by using the trim
function. (=Trim([text]) - Answer-Why should you remove unnecessary spaces from data
imported from another source? How do you remove unnecessary spaces from a text
string?
The FIND (=FIND( find_text, within_text, [start_num] )function returns the starting
position of one text value within another text value. It is also case-sensitive. The
SEARCH function does the same thing as find, however it is not case-sensitive. -
Answer-How do the FIND and SEARCH functions work, and how are they different?
First way is to identify the character that separates the data. The second way is to set
field widths to identify the breaks between data that appears in columns - Answer-What
options are available for parsing data when you use the Convert Text to Columns
Wizard?
, The advantages are that it creates summary reports that quickly organize data into
categories with subtotal calculations and lets you collage and expand the level of detail
in the report. A disadvantage of the subtotal command is that it works only with one
category and one subtotal calculation at a time. - Answer-What are the advantages and
disadvantages of using the Subtotal tool to analyze data?
The difference between an unstructured list and an Excel list is that because all the data
in an excel list are in a structured format, one can use the filter feature to sort through
the data using different methods. - Answer-What is the difference between an
unstructured list and an Excel list?
Sort A to Z, Sort Z to A, Sort by Color (if cells are manually or conditionally changed to
have background color, it filters these colors), Clear Filter From (removes any filters and
restores original data), Text Filters (filters alphanumeric text by specific characters),
Number Filters, Date Filters (filters by date and time values),(Custom) - Answer-List and
describe the six available options when using the AutoFilter feature in an Excel Table
It ensures that your data is secured and protected but can be analyzed using a
spreadsheet easily, thus reducing redundancy. - Answer-What is the primary advantage
of storing data in a database and importing that data in Excel?
Open a workbook where you want to import the database data. Select the first blank
cell, and then click the From Access button in the Get External Data group on the Data
tab on the ribbon. In the dialog box, find the file, select it, and click to open it. - Answer-
Explain the steps you must take to import data stored in an Access database into Excel
The query wizard lets you choose your data source and select the database table and
fields you want to import into a workbook. It prompts you to define any criteria for the
data by selecting row to meet those criteria. You would use the query wizard when you
are trying to select specific data prior to importing it into Excel. - Answer-What is the
Query Wizard and when would you use it?
Excel uses serial numbers to represent dates and times. For dates, Excel represents it
as the number of days since January 0, 1900. Therefore January 1, 1900 would be
represented by the serial number 1. Times are represented by decimals. Noon on
January 1, 1900 would be represented by the serial number 1.5. - Answer-How does
Excel store date and time values?
YEARFRAC(start_date,end_date,basis) .the basis argument of 1 uses actual values as
in the correct number of days per month, using 0 as the basis causes the function to
calculate the months to have only 30 days and years using 360 days. - Answer-What
are the arguments for the YEARFRAC function? What are the possible values for
calculating months are years using this function?
You should use a PivotTable report to analyze data when you need a way to summarize
and analyze data from different perspective such as by month, quarter and year. It
