Application of Advanced Excel & Quick Books in Financial Management & Microsoft Dynamics

Advanced Excel Financial Modeling, Quick Books & Microsoft Dynamics Navision Training.

Advanced Excel Financial Modeling

This will be a comprehensive, Advanced and practical training course for finance staff, departmental heads and other staff whose roles necessitate periodic reports or otherwise and whose functionality imperatively revolves around use of Excel Spreadsheets. The course aims to impart skills that will among other things lead to objective reporting and improved use of excel spreadsheets.

Microsoft Excel has countless finance value adding features and the expert trainers will aim to select the most powerful and relevant tools and cover these while ensuring the practical sessions enable learners to emerge from the training more empowered.

Finance Advanced in Microsoft Dynamics Navision.

This course provides learners with the tools to set up the financial management application area. The focus is on the advanced financial functions within the organization such as multicurrency, cost accounting, cash flow forecast, and the VAT rate change tool. Further, this course focuses on internal reporting and analysis functionality in addition to the means for sharing data outside of the Microsoft Dynamics NAV environment, either through XBRL—a language for tagging financial data—or by exporting data to Microsoft Office Excel 2010.

QuickBooks.

The Training will endeavor to impart knowledge in the following areas;

• QuickBooks Products and System Requirements Clean and Conversion File Setup
• Customizing Preferences
• Sales Cycle – Invoicing Customers, Customer Payments, Bank Deposits
• Procurement Cycle – Purchase Orders, Entering Bills, Paying Bills, Writing Checks
• Tips and Tricks on Unique Customer and Vendor Transactions
• Inventory Management Payroll Services
• Payroll Setup and Processing Sales Tax
• Data File Management Data Security Measures Year-End Procedures

Target Audience.

This event is for all from Public Sector; Private Sector and NGO Sector staff who desire to enhance their skills in finance software’s. This course is useful for both finance and non-finance professionals. The course is highly recommended but not limited to the following professionals;

• Finance &Non-finance managers;
• Grants Managers & officers
• Branch Departmental heads;
• All staff that prepare performance data or those who supervise others in preparing the reports;
• Tax consultants;
• Revenue Managers;
• Financial services sector experts (e.g. Banks, insurance companies etc.);
• Board members& CEOs;
• Project Managers;
• Academics/ researchers
• ICT Officers

Advanced Excel Key Training modules

Data Manipulation in Excel

• How Excel handles different data types
• Data consistency, starting with the end in view
• Building Datasheets that can easily scale
• Sorting

Cascaded sorting

• Sorting across rows (left to right sorting, not the usual up to down sorting) Sorting and Conditional
• Formatting to identify trends

Filtering

Data cleaning

Removing duplicates

• Text-to-column
• Grouping
• Data Validation

Data formatting

• Using Tables (and when to convert to tables)
• Formatting for printing
• Formatting for email
• Data Review and formatting for 3rd party use

Charts

• Chart types
• Line chart and when to use it
• Column chart and when to use it
• Bar chart and when to use it
• Pie chart and its dangers
• Combining charts; when and how.
• Dynamic Charts, using filter.
• Best practices when making charts Sparklines
• Power Map and Power View (Excel 2013)

Pivot Table, Pivot Chart and PowerPivot

• Pivot Table
• Default Pivot Table
• Tabular Pivot Table
• Pivot Table Filtering
• Making a very dynamic regular table from Pivot Table
• Calculations and Formula use with Pivot Table
• Advanced Pivot Table tricks
• Pivot Chart
• Pivot Chart and its limitations
• Dynamic Pivot Charts
• PowerPivot (for Excel 2010 and 2013) only

Lookup functions

• V-lookup
• H-lookup
• Looking up the last data or pattern in a particular row or column
• Overcoming the limitations of Vlookup and Hlookup using index and match function

Power Functions

• IF, IF ERROR, AND, OR, ISBLANK, and others in the same family
• TEXT manipulative functions to make a completely automated Dashboard
• COUNTIFS, SUMIFS, AVERAGEIFS and others, to make dynamic summary tables
• MATCH and INDEX to do the impossible

Executive Dashboards and Reporting

• Best Practices
• Executive Dashboards
• Executive Dashboards
• Dynamic Reports
• Determining the KPIs and tracking them
• Strategic Insights & Analysis
• Data Visualization
• Having the audience/recipient in mind
• eE-mails and Excel reports

Microsoft Dynamics Navision Training modules

• Set up Intrastat for use in Microsoft Dynamics NAV 2013.
• Explain how to run and submit Intrastat reports.
• Explain and demonstrate the preparation of the VAT rate change process.
• Explain and set up the VAT rate change tool.
• Explain and demonstrate how to perform a VAT rate change.
• Explain setup requirements for currencies and exchange rates when using multiple currencies.
• Explain setup requirements using multiple currencies with customers, vendors, and bank accounts.
• Describe steps and additional functionality for processing purchase and sales documents that use foreign currency.
• Describe steps and additional functionality for recording and posting multicurrency transactions in the cash receipts journal.
• Describe how to process multicurrency payments using the payments journal.
• Describe how to run the Adjust Exchange Rates batch job for customers, vendors, and bank accounts.
• Explain set up, viewing amounts, daily processing, and periodic processes when using an additional reporting currency.
• Describe how to run the Adjust Exchange Rates batch job for G/L accounts.
• Describe how to view the Exchange Rate Adjustment Register.
• Explain the Budgets feature in Microsoft Dynamics NAV 2013.
• Explain the G/L Budgets page elements.
• Show how to set up budgets manually.
• Explain how to delete budget entries.
• Explain how to create budgets by using the Copy Budget
• Explain how to create budgets by using the Export Budget and Import Budget
• Explain the workflow in Cost Accounting.
• Explain and set up Chart of Cost Types, Chart of Cost Centers, Chart of Cost Objects, and Cost Accounting Setup.
• Explain the relationship between the cost accounting and general ledger application areas.
• Set up cost journals.
• Explain how to create cost entries either through a transfer from G/L entries or through posting the cost journal.
• Explain and set up cost budgets by using different copy functions.
• Explain the transfer from budget to actual.
• Explain and set up cost allocations.
• Explain static and dynamic allocations.
• Explain how to allocate costs and cost budgets.
• Explain the cost registers and cost budget registers.
• Explain the deletion of cost entries and cost budget entries.
• Explain different cost accounting reports.
• Provide tips and tricks.
• Explain the functions of the cash flow forecast
• Explain the setup of the Chart of Cash Flow Account, Cash Flow Setup and Cash Flow Payment Terms.
• Explain how to create cash flow forecasts by using Cash Flow Forecast Cards and Cash Flow Manual Revenues and Expenses.
• Explain how the Cash Flow Worksheet is used.
• Review the registration of the cash flow through Cash Flow Forecast Entries.
• Explain the different Cash Flow Forecast Reporting possibilities.
• Describe filtering and analysis pages related to the chart of accounts.
• Explain how to set up an account schedule and explain other processes that relate to account schedules.
• Demonstrate cost account schedules.
• Demonstrate cash flow account schedules.
• Describe types of dimensions.
• Explain how to set up analysis views.
• Explain the process of exporting analysis views to Microsoft Office Excel® 2007, and describe the elements of the exported file.
• Show how to generate analysis by dimensions and other dimension analysis reports.
• Show how to set up a Finance Performance chart.
• Explain the terminology associated with XBRL.
• Explain how to import and update taxonomies.
• Explain how to attach, update, and apply linkbases to taxonomies.
• Describe pages that are related to XBRL lines, and explain how to enter XBRL line definitions
• Demonstrate how to export an instance document.

QuickBooks

Lesson One: Getting Started

ü To gain an overview of the course and the topics to be  covered

ü To know how QuickBooks works and how you can get around in  QuickBooks

• To learn common business terms used by QuickBooks
• To Discuss different versions of QuickBooks
• To see how to exit QuickBooks

Lesson Two: Setting Up and Managing QuickBooks Data File

ü To discuss decisions that must be made before using QuickBooks ü To create a new QuickBooks company using the Easy Step Interview

• To set QuickBooks preferences

ü To discuss Single vs. Multi user mode and how to add  users

ü To discuss different QuickBooks file types and Backup  Options

Lesson Three: Working with List and Items

• To edit the company chart of accounts

ü To add a new customer to the Customers & Jobs  list

• To add a new vendor to the Vendor list

ü To learn about custom fields, and to practice adding custom  fields

• To see how to manage lists and items in QuickBooks

Lesson Four: Working with Bank Accounts

ü To learn how to work with registers for QuickBooks bank  accounts

• To demonstrate how to open a register
• To learn the features common to all registers

ü To learn when and how to make entries directly in the register ü To demonstrate how to reconcile a QuickBooks bank account

Lesson Five: Entering Sales Information

• To learn about the different formats available for sales forms

ü To save sales and purchase forms in Portable Document Format  (PDF)

• To practice creating a new invoice
• To learn the purpose and use of the QuickBooks Item list

ü To see how QuickBooks records the information you enter on sales  forms

• To memorize an invoice transaction for reuse
• To add a new item to the Item list

ü To add a new price level to the Price Level  list

• To associate a price level with a customer
• To generate reminder statements

Lesson Six: Receiving Payments & Making Deposits

• To learn how to record customer payments in QuickBooks

ü To learn how to handle customer discounts, partial payments, overpayments, or down   payments

• To see how to record a deposit in QuickBooks, and learn how QuickBooks treats the deposit behind the scenes

ü To learn how to enter cash back from a deposit in  QuickBooks

Lesson Seven: Entering and Paying Bills

ü To discuss the different ways you can handle bills in  QuickBooks

• To learn how to enter a bill in QuickBooks

ü To use the Pay Bills window to pay a bill in  QuickBooks

Lesson Eight: Using Other Accounts in QuickBooks

• To introduce the other account types available in QuickBooks

ü To learn how to track credit card transactions in  QuickBooks

• To reconcile a credit card account
• To see how to make a credit card payment
• To discuss the different types of asset and liability accounts you can create and see how to track assets and liabilities in QuickBooks.
• To introduce the subject of equity and QuickBooks equity accounts

Lesson Nine: Analyzing Financial Data

• To discuss some of the tools QuickBooks gives you for analyzing financial data: QuickReports, preset reports, and graphs
• To create a QuickReport

• To learn about the types of preset reports QuickBooks offers
• To practice creating reports and viewing them onscreen
• To customize a report by changing how it looks and the data it covers (filtering)
• To save reports as Portable Document Format (PDF) files
• To learn how to export a report to Microsoft Excel
• To practice filtering reports in Microsoft Excel
• To learn about the types of graphs QuickBooks offers

Lesson Ten: Setting Up Inventory

• To get an overview of inventory in QuickBooks
• To practice filling out a purchase order for inventory items

ü To track the receipt of the inventory items in QuickBooks ü To adjust inventory manually, to enter a stock loss or increase

• To set up and use units of measure

Lesson Eleven: Tracking and Paying Sales Tax

• To get an overview of sales tax in QuickBooks (the steps involved in tracking, collecting, and paying

ü To see how to set up QuickBooks to track sales  tax

ü To see how to apply sales tax to a  sale

ü To learn how to determine a business’s sales tax  liability

ü To write a QuickBooks check to the appropriate tax agency for sales tax  liability

Lesson Twelve: Doing Payroll with QuickBooks

• To gain an overview of payroll in QuickBooks
• To learn more about payroll setup
• To set up employee payroll information
• To set up payroll schedules
• To practice writing and printing a payroll check
• To learn how QuickBooks tracks your tax liabilities
• To practice paying payroll taxes

Way forward After the Training

Participants will develop a work plan through the help of facilitators that stipulates application of skills acquire in improving their organizations. ASPM will monitor implementation progress after the training.

Training Evaluation:

Participants will undertake a simple assessment before the training to gauge knowledge and skills acquired and another assessment will be done after the training in-order to demonstrate knowledge gained through the training