10101137Computerized Accounting Applications
Course Information
Description
Provides practical experience developing and applying flexible solutions to accounting problems using Excel. Spreadsheet tools that may be utilized include analysis formulas, cross-referencing and linking, lookup, statistical, date/time, database and financial functions; tables, pivot tables, dashboards, transferring Excel information into other programs, logical statements (IF); what-if tools, charting and macros. In addition, each student will benchmark ten-key data entry speed and improve this skill on an individualized basis if needed.
Total Credits
2
Course Competencies
  1. Demonstrate ten-key (number pad) data entry speed
    Assessment Strategies
    Skill Demonstration
    Criteria
    Meet benchmark speeds for one and three minute intervals
    Meet 100 key strokes per minute in one minute with two errors or less

  2. Complete Excel spreadsheet startup and review activities
    Assessment Strategies
    Worksheet Presentation and Results
    Criteria
    Organize information and format consistently
    Complete manual formulas which calculate accurate results
    Use relative and absolute cell addresses
    Totals are accurate
    Horizontal and vertical analysis formulas are correct
    Apply conditional formatting rules
    Create a column chart from the data on the worksheet
    Display formulas on a separate worksheet
    Run your personal macro on each worksheet

  3. Create formulas to analyze financial statement results and look up new information
    Assessment Strategies
    Income Statement and Balance Sheet Worksheets
    Criteria
    Cross-reference information from data sheet into financial statements
    Create accurate horizontal and vertical analysis formulas
    Create correct ratio formulas at the bottom of each statement worksheet
    Link information to another workbook
    Use the VLOOKUP function properly to calculate new tax expense amounts
    Link new information back to the original workbook for updated results
    Display formulas on a separate worksheet
    Run your personal macro on each worksheet

  4. Expand worksheets to include new data and analysis
    Assessment Strategies
    Comparative Income Statement and Health Insurance Budget Worksheets
    Criteria
    Insert columns to accommodate new data
    Copy links into new columns
    Create headings and adjust formulas for changing analysis
    Use the ROUND function for exact results
    Use VLOOKUP formulas to look up information and display additional results
    Create manual formulas to display costs and totals
    Use SORT and SUBTOTAL functions to reorganize and display results
    Create a line chart from table information
    Use a circle (DRAWING TOOL) to highlight information
    Display formulas on a separate worksheet
    Run your personal macro on each worksheet

  5. Use multiple worksheets to organize and summarize information
    Assessment Strategies
    Accounts Receivable Subsidiary Ledger and Depreciation Schedule Workbook
    Criteria
    Customize worksheet templates with reusable formulas and copy to new worksheets
    Create HYPERLINKS to move between worksheets and workbooks
    Create a summary worksheet to combine information from detail worksheets
    Create a pie chart from balances on summary worksheet
    Complete other applications including a bank reconciliation, aging schedule of accounts receivable and notes receivable cash proceeds calculations
    Display formulas on a separate worksheet
    Run your personal macro on each worksheet

  6. Record macros in library and separate worksheets
    Assessment Strategies
    Macro Library, Main Street Office Database and Depreciation Schedule Workbooks
    Criteria
    Document and record new MACROS in your macro library
    Test macros and adjust in visual basic editor
    Assign macros to control buttons
    Record and run macros to sort, subtotal, and return to original
    Record macros to insert depreciation worksheets and assign to buttons

  7. Create and use Date/Time, Financial and Logical functions in formulas to solve business problems
    Assessment Strategies
    Depreciation Schedules, Commission Calculations, Rental Unit Budget
    Criteria
    Use STRAIGHT-LINE and DOUBLE-DECLINING functions in depreciation templates
    Use YEARFRAC function for partial year calculations
    Create IF statements to test options and perform calculations
    Copy Excel results into WORD communication document with link
    Develop IF statements for multiple applications
    Run your personal macro on each worksheet

  8. Use Databases, Tables, Pivot Tables, and Dashboards to maintain data and summarize results
    Assessment Strategies
    Database files
    Criteria
    Use DATA FILTER to query out information from database
    Use STATISTICAL and DATABASE Functions to extract information from the database
    Insert Tables for adding new records and creating new formulas
    Develop PIVOT TABLES to summarize information from the database or table
    Create a DASHBOARD to display graphic results from pivot tables

  9. Use What-If analysis tools to present multiple options
    Assessment Strategies
    Contract Analysis, Net Profit Comparison, Production Model Costing
    Criteria
    Use GOAL SEEK to change variables in key formulas
    Create a DATA TABLE to show multiple results reflecting changes in variables
    Create SCENARIOS to store results for multiple changes in variables
    Run your personal macro on each worksheet

  10. Prepare Portfolio Workbook and School District Budget in Final Project Applications
    Assessment Strategies
    Portfolio Workbook and School District Budget Project
    Criteria
    Locate previous files, make changes, and relocate into Portfolio Workbook
    Develop a School District Budget in a multiple-worksheet workbook
    Budget meets specifications as identified by instructor