If and Lookup Functions Charts Template

							                                                                                                         Page 1 of 6



Computerized Accounting Applications
Course Outcome Summary

Course Information
Organization                  Madison Area Technical College
Developers                    Mark Finger
Development Date              1/8/2001
Revised By                    Mark Finger
Revised Date                  9/30/2010
Course Number                 10-101-137
Instructional Level           Associate Degree
Instructional Area            Accounting
Division                      Business and Applied Arts
Department                    Accounting/Business Management/Finance
Potential Hours of            72
Instruction
Total Credits                 3

Description
Provides practical experience developing and applying flexible solutions to accounting problems using Excel.
Spreadsheet tools that will be utilized include financial, lookup, and database functions; logical statements (IF);
goal seek; solver; pivot tables; and macros. In addition, the student will gain exposure tin transferring financial
information between software programs and will become familiar with QuickBooks Pro accounting software.

Target Population
Associate Degree Accounting students who are in the second year of their program training to become
bookkeepers and accountants; students will need to utilize fundamental accounting knowledge and beginning
spreadsheet skills in this course

Types of Instruction
Instruction Type                                                     Contact Hours                  Credits
Classroom Presentation & Lab                                                 72                         3


Textbooks
Textbook information can be found by contacting the Madison College Bookstore or the instructor.


Learner Supplies
Flash Drive. Manufacturer: ---. Quantity: 2. Source: MATC Bookstore.
3-Ring binder. Manufacturer: ---. Quantity: 1. Source: MATC Bookstore.


Prerequisites
Accounting II Principles (101-113)
Beginning Excel (103-133)


Exit Learning Outcomes
Core Abilities
A.    Demonstrate critical thinking




file://C:\Documents and Settings\JMay\My Documents\WIDS\WizardReport.htm                                    3/1/2011
                                                                                               Page 2 of 6



B.   Use mathematics effectively
C.   Use science and technology


Competencies
1.        Prepare spreadsheet templates
          Linked Core Abilities
          Demonstrate critical thinking
          Use science and technology
          Competence will be demonstrated:
          a. by satisfactorily completing and submitting all assigned spreadsheet problems and special
          business projects when due
          Criteria - Performance will be satisfactory when:
          a. spreadsheet problems demonstrate the utilization of basic tools and commands
          b. spreadsheet problems show data organized in rows and columns
          c. spreadsheet problems include consistently formatted worksheet cells
          d. spreadsheet problems include mathematical formulas to perform calculations
          e. spreadsheet problems demonstrate the utilization of macros
          f. spreadsheet problems and special business projects include printouts in business quality
          Learning Objectives
          a. Utilize worksheet window areas to locate commands
          b. Utilize toolbar icons to run commands
          c. Use the mouse and the keyboard to move around the worksheet
          d. Enter text and value data on each spreadsheet template
          e. Select typeface styles and appropriate format settings
          f. Select appropriate function to generate desired cell output
          g. Create correct formulas to perform calculations
          h. Create and run centering and informational macros
          i. Save template for future use and analysis
          j. Set print settings for business quality presentation
          k. Print out final template product
2.        Create charts from financial information
          Linked Core Abilities
          Use mathematics effectively
          Use science and technology
          Competence will be demonstrated:
          a. by satisfactorily completing and submitting all assigned spreadsheet problems and special
          business projects when due
          Criteria - Performance will be satisfactory when:
          a. spreadsheet problems include data cells to be charted
          b. spreadsheet problems demonstrate the use of charting commands and techniques to present
          information graphically
          c. spreadsheet problems include charts edited to meet different presentation requirements
          d. spreadsheet problems include chart printouts in business quality
          Learning Objectives
          a. Determine what data should be presented in chart display
          b. Select adjacent and nonadjacent data cells for inclusion in chart
          c. Access chart command menu
          d. Utilize chart wizard commands to select type of chart and other chart settings




file://C:\Documents and Settings\JMay\My Documents\WIDS\WizardReport.htm                         3/1/2011
                                                                                              Page 3 of 6



         e. Edit created chart on the screen and through menu commands
         f. Position chart in existing spreadsheet or on own sheet
         g. Set print settings to print chart with data or chart by itself
         h. Explain relationships between charted data points
3.       Use multiple worksheet files to organize information
         Linked Core Abilities
         Demonstrate critical thinking
         Use science and technology
         Competence will be demonstrated:
         a. by satisfactorily completing and submitting all assigned spreadsheet problems and special
         business projects containing multiple worksheet applications when due
         Criteria - Performance will be satisfactory when:
         a. spreadsheet problems show the development of related multiple sheet templates within the same
         file
         b. spreadsheet problems correctly link cell information between sheets and files using cross-
         referencing techniques
         c. spreadsheet problems summarize information from worksheet detail
         d. spreadsheet problems include printouts from a multiple sheet file in business quality
         Learning Objectives
         a. Identify related template data that can be organized on separate worksheets
         b. Establish detail to summary data heiarchy
         c. Enter appropriate sheet labels onto tabs
         d. Organize informational relationships across worksheets
         e. Enter data onto detail sheets
         f. Connect detailed data to summary worksheets using link cross-referencing
         g. Set print settings for business quality presentation
         h. Print out worksheet and workbook products
4.       Create function formulas to solve business problems
         Linked Core Abilities
         Use mathematics effectively
         Use science and technology
         Competence will be demonstrated:
         a. by satisfactorily completing and submitting all assigned spreadsheet problems and special
         business projects containing function formulas when due
         Criteria - Performance will be satisfactory when:
         a. spreadsheet problems utilize lookup functions to find information
         b. spreadsheet problems include IF statements to provide responses for multiple situations
         c. spreadsheet problems include selected financial functions to calculate desired results
         d. spreadsheet problems contain arguments to complete financial function formulas
         e. spreadsheet problems include formulas which perform "what-if" analysis
         f. spreadsheet problems include cell formula printouts to display formulas
         Learning Objectives
         a. Identify appropriate functions needed for template calculations
         b. Determine whether or not data is needed from a table
         c. Select vlookup or hlookup functions to connect to table
         d. Enter lookup formula arguments through dialog box to extract information
         e. Determine whether or not data comparisons result in 2 or more possible results
         f. Select and enter IF statement arguments through function dialog box
         g. Determine whether or not a program function exists to perform a financial calculation




file://C:\Documents and Settings\JMay\My Documents\WIDS\WizardReport.htm                        3/1/2011
                                                                                                 Page 4 of 6



          h. Select and enter financial function arguments through dialog box
          i. Test function formulas using variable data sources ("what-if" analysis)
          j. Set cell format to display cell formulas
          k. Print out worksheet templates as-displayed, as cell-formula versions and as products containing
          test data
5.       Use database functions to manipulate spreadsheet data
          Linked Core Abilities
          Demonstrate critical thinking
          Use science and technology
          Competence will be demonstrated:
          a. by satisfactorily completing and submitting all assigned spreadsheet problems and special
          business projects containing data list applications when due
          Criteria - Performance will be satisfactory when:
          a. spreadsheet problems display the location of information in data list
          b. spreadsheet problems correctly sort list data
          c. spreadsheet problems correctly filter list data
          d. spreadsheet problems display pivot tables from list data
          e. spreadsheet problems include printouts of data lists in business quality
          Learning Objectives
          a. Recognize data list structure exists within worksheet template
          b. Name data list for reference in using list functions
          c. Determine the desired order of presentation of the records
          d. Sort data list records using primary and secondary sorts
          e. Determine the need to extract a short list of data list records
          f. Filter data list records using field identifiers and criteria
          g. Analyze the need to summarize data list records in a pivot table report
          h. Utilize the pivot table wizard dialog boxes to construct summarized reports
          i. Set print setting area to print out data sorts, filters, and pivot tables
6.       Import spreadsheet information into a word processing document
          Linked Core Abilities
          Demonstrate critical thinking
          Use science and technology
          Competence will be demonstrated:
          a. by satisfactorily completing and submitting all assigned spreadsheet problems and special
          business projects importing spreadsheet information into word processing applications when due
          Criteria - Performance will be satisfactory when:
          a. spreadsheet problems export Excel spreadsheet data
          b. spreadsheet problems correctly import spreadsheet information into a WORD document
          Learning Objectives
          a. Create worksheet template with business information to be communicated to managers,
          employees, others
          b. Develop communication (i.e. memo, letter) in word processing software
          c. Identify worksheet cell information to be copied into word processing document
          d. Copy worksheet information to the clipboard
          e. Import worksheet information into word processing document so that link between applications is
          established
          f. Print out word processing document displaying worksheet information
          g. Change worksheet data to confirm that link to word document exists
7.       Apply internet search techniques to locate published financial information




file://C:\Documents and Settings\JMay\My Documents\WIDS\WizardReport.htm                            3/1/2011
                                                                                                Page 5 of 6



         Linked Core Abilities
         Demonstrate critical thinking
         Use science and technology
         Competence will be demonstrated:
         a. by satisfactorily completing and submitting all assigned spreadsheet problems containing website
         financial information when due
         Criteria - Performance will be satisfactory when:
         a. spreadsheet problems show the correct input of website addresses to locate financial information
         b. spreadsheet problems contain imported financial statement information found on company
         websites
         c. spreadsheet problems include financial statement analysis formulas
         Learning Objectives
         a. Access the internet using available browser
         b. Develop a list of valuable business site web addresses
         c. Input web addresses to locate business site information
         d. Locate financial statement information inside company homepage sites
         e. Copy out sections of financial information from website
         f. Paste financial information into Excel
         g. Create ratio analysis formulas in Excel
         h. Analyze company financial information through analysis formulas
8.       Use QuickBooks accounting software to perform small business accounting
         Linked Core Abilities
         Use mathematics effectively
         Use science and technology
         Competence will be demonstrated:
         a. by satisfactorily completing and submitting all QuickBooks training assignments when due
         b. by satisfactorily completing and submitting all QuickBooks simulation project printouts and
         analysis sheets when due
         Criteria - Performance will be satisfactory when:
         1 QuickBooks training assignments confirm practice in managing revenue
         2 QuickBooks training assignments confirm practice in accounting for managing expenses
         3 QuickBooks training assignments confirm practice in bank reconciliation
         4 QuickBooks training assignments confirm practice in creating and processing reports
         5 QuickBooks training assignments confirm practice in accounting for inventory activities
         6 QuickBooks training assignments confirm practice in working with sales tax processing
         7 QuickBooks training assignments demonstrate accounting for adjustments and year-end closing
         procedures
         8 QuickBooks simulation project includes the accounting of small business activity for two months
         Learning Objectives
         a. Identify the computerized accounting processes performed in small businesses
         b. Acquaint self with QuickBooks software commands and processes
         c. Review accounts and entries used in processing sales related transactions
         d. Use QuickBooks Sales and Customer process to handle sales related transactions as presented
         in the training manual
         e. Review accounts and entries used in processing purchase related transactions
         f. Use QuickBooks Purchases and Vendors process to handle purchasing related transactions as
         presented in the training manual
         g. Review accounts and entries used in processing payroll activities
         h. Use QuickBooks Bank and Checks process to prepare handwritten checks as presented in the




file://C:\Documents and Settings\JMay\My Documents\WIDS\WizardReport.htm                           3/1/2011
                                                                                               Page 6 of 6



          training manual
          i. Use QuickBooks Bank Reconciliation process to reconcile the checking account as presented in
          the training manual
          j. Use QuickBooks Taxes & Auditing process to handle adjustment and general journal entries as
          presented in the training manual
          k. Set up QuickBooks sales tax codes and process an reconcile sale tax activities
          l. Apply QuickBooks software knowledge by maintaining the accounting records for a small business
          for two months




file://C:\Documents and Settings\JMay\My Documents\WIDS\WizardReport.htm                          3/1/2011