Introduction to Excel VBA & VBA Real Project for Business Professional
免费公开课:VBA Real Project 周日 2:00 pm

EXCEL VBA FOR BUSIENSS PROFESSIONALS
 
Why to learn Excel VBA?
Business professionals use Excel to analyze data, create reports, prepare charts & presentations, do calculations and understand information. When you are doing all these, you spend quite some time, repeating the same steps. VBA allows you to automate various activities you do in Excel, add “WOW!” factors to your application by dramatically saving time and improving efficiency.
 
What is Excel VBA?
VBA stands for Visual Basic for Applications. It's a programming language that enables you to control just about everything in Excel. Learning VBA will enable you to do a lot more with the software than you can via the normal spreadsheet view.
 
Do I need programming background?
No. This course is customized for business professionals without any IT background. We start from the very basics of programming, so don't worry if you have no skills at all in this area.
 
Course Overview

VBA for Business Professionals is a 24 hours course customized for business professionals without any IT or programming background. This course is focused on VBA for Excel, which is the mostly used VBA application for business professionals. Instructor will teach from a non IT professionals’ point of view, and walk you step by step to master VBA programming skills. Abstract concepts and principles will be explained in an easy understanding way, and all the examples and exercises are associated with financial analysis, reporting and modelling. Super Excel or equivalent skills are required in order to be successful in this course.

Learning Outcomes

Upon completion of this course, students should be able to write simple programs to automate everyday work and significantly improve work efficiency. You will also be able to understand and perform maintenance to intermediate and advanced programs developed by IT professionals.

Course Outline and Objectives  (24HRs)        

Class
Tasks & Projects
Objects
1

Introduction to VBA, VBE, Modules, Variables, Subroutines, With Constructions, comments, record and clean Macro in VBE, Objects/Properties/Methods/Arguments.

Get familiar with VBE and basic knowledge of VBA
2

Properties and methods of ranges, how to refer to a single cell range, multiple cells range, use Offset to dynamically refer to a range, current region, used range, union. Get formula R1C1 format and number format from Immediate window or Debug.Print. Customize range format with VBA syntax.

 

Understand VBA syntax of Ranges and be able to refer to a range with Offset and customize range format.

3

Name, copy and paste special of ranges, insert and delete rows or columns, sort and filter with VBA codes.

Comprehensive exercise: Sensitivity Analysis.

Learn methods of ranges and be able to manipulate ranges from VBE. Apply knowledge learned to the first exercise project.

4

Single line IF, IF THEN ELSE, IF AND/OR, Nested IF, Case Constructions, For loops vs. Do loops, For Each Next Loop.

Learn backbone language of VBA – Control logics and Loops.

5

For Next Loop, Counting backward, Exit Loop prematurely, Do While Loop, Do Until Loop, Do Loop While, Do Loop Until.

Comprehensive exercise: Loan Amortization Model.

Learn backbone language of VBA – Control logics and Loops. Apply knowledge learned to the second exercise project.

6

Workbooks, Worksheets, Charts, Arrays, Revenue consolidation, Sales consolidation.

Analyze example projects to obtain knowledge of other objects of VBA .

7

User Forms creation, Event handling codes, Order taking model, Comprehensive exercise: Car Loan Analysis Model.

Develop skills to create User Forms and Event Handling Subs, analyze the third exercise to review all knowledge learned.

8

Scopes of Variables and Subs, passing Variables across Subs, Function Subs, Syntax error/Runtime error/Logical error, debugging, display alerts, error handlings, and guidelines to program developing.

 

Obtain competency to deal with debugging, Display alert and error handling.

 
INSTRUCTOR BACKGROUND

·         10 years of experience in financial analysis, management reporting, budgeting/forecasting and financial modelling in a world leading Fortune 500 company.

·         Many years of training and teaching experience to individuals and corporates in Excel, Access, Financial analysis and reporting, VBA and SQL.

·         Students include junior/intermediate business professionals, such as financial analyst, general accountant, fund account, data analyst, cost analyst, business analyst, risk analyst, investment analyst, credit analyst, sales analyst, inventory analyst, procurement analyst, and etc.

·         Hold MBA degree from Schulich School of Business, and CMA designation from Certified Management Accountants of Ontario.

 

VBA Real Projects in Business Analysis

Objective: The course includes three VBA related projects, and provides students with coaching in the real working environment. In the current data-intensive business world, knowing how to manipulate data with Excel and Access VBA makes job candidates more competitive. Possessing real VBA project experience can make a candidate even more outstanding during the hiring process. Through mastering the course projects, students can have a good combination of business concepts (specifically in the Finance industry & department) and programming skills enhancement.

Duration: 20 Hours

INSTRUCTOR BACKGROUND
Robin Huang, FRM, MCSA, Master in Economics (U of T), Bachelor in Finance (U of T), served a Toronto based asset management firm with expertise in designing automated processes in regards to portfolio analytics, performance reporting, risk management and equity research; currently working as a Senior Analyst at a major bank.

Course Outline and Objectives  (24HRs)     

Project 1 – Financial Data Process & Reporting I (6 hours)

A VBA tool to automatically extract data from multiple files and process data and generate customized monthly financial reports. The tool can be either customized to run various month-end reports going backward or scheduled to run new month-end reports going forward. You will learn how to process and manipulate data among multiple Excel files.

 Project 2 – Financial Data Process & Reporting II (6 hours)

You will be involved in designing customized financial statement reports which queries data from Access Database. The reports will be exported to PDF format, and finally, scheduled to run automatically daily & monthly. You will learn how to process and manipulate data interactively between Excel and Access.

 Project 3 – Financial Analysis & Reporting (8 hours)

Our client is a financial institution with multiple branches across North America. You need to design a branch ranking report to monitor the operating efficiency, the profitability and financial security of each branch. You need to gather data from each branch and process the data using VBA and finally, build customized reports. The project is ideally required to be automated.

Scope of project

·         Extract data from multiple Excel files

·         Process and manipulate data with VBA (or SQL)

·         Build sorting & filtering features & pivot tables

VBA 项目精华班(VBA Real Project)

三个真实案例,模拟工作环境,20小时突击训练,5场头脑风暴,让您在一个月成为Senior Analyst中的佼佼者!!!

 案例一 (Financial Data Process & Reporting I)

You are a Co-op under the Finance team of Albertech Inc. Your colleague, Robert is a Senior Analyst who has been with the department for ten years. Robert is very knowledgeable about financial reporting. However, he is not willing to invest extra time in learning Excel coding to help improve productivity. Each month, Robert has to complete the Financial Statements report for Albertech’s three branches and send the report to the Senior Manager of the Finance team for further review. To complete the report, Robert has to collect three pieces of Excel files from the Operation Team and enter the data into another Excel template designed by Senior Manager. Robert does not know any Macros and he “complains” that the process is so annoying that each month, he has to spend four hours to manually enter the data and to double check the results. Your manager asks you to take a look at the process and to try to improve the process.
案例特色:本案例让您运用VBA熟练处理多个Excel文档之间的数据流,并实现“一键”将原始数据变成高大上的金融报表。设计好的工具不但能自动的产生过去任何一个月的报表,还能够和定时器挂钩,于将来某一特定时间自动生成所需报表。 

 案例二 (Financial Data Process & Reporting II)

You are a junior financial analyst at the Head Office of Albertech Inc. Your colleague, Mary is a Senior Analyst who has been with Albertech for five years. Mary has always been working so hard and was promoted to Senior Analyst two years ago. Recently, she announces that she will deliver a baby in seven months and will be on maternity in three months. The team celebrates Mary’s big news and you are welcomed to the team to back up Mary’s task on her maternity leave.

Each Month, Mary sends a financial analysis report to the CFO so that CFO can monitor Albertech’s monthly, quarterly and yearly performance and plan the next steps. To do the report, Mary would always go to Albertech’s online reporting site and pull out past financial data month by month and then copy into an Excel template column by column. The CFO requires the report in PDF report. Your manager asks you to take over this task going forward and make improvement on the process wherever possible. The report is required to be delivered within three business days after month-end.

案例特色:本案例让您设计程序自动从不同的数据库下载数据,传递到Excel,并在Excel中将所有数据加以整合修饰最终形成客户所需的报表。本案例运用Lean Six Sigma的原则使报表的生产流程最精准,最快速,最高效!
 

案例三(Financial Analysis & Reporting) You are a business analyst at the Sales department of Albertech Inc. Albertech Inc currently has thirteen regional branches. Each branch has its Income Statements and Balance Sheets. The Director of Sales needs a report to evaluate each branch’s performance. Specifically, he gives three performance factors to evaluate: operating efficiency (measured with Asset Turnover, 30% weight), profitability (Profit Margin, 40% percent weight) and financial security (measured with Leverage Ratio, 30% weight). In 

案例特色:本案例让您熟练运用VBA与SQL让数据在Excel与数据库中自由转换。通过该案例,您会学到当今流行的ETL(Extract-Transform-Load)流程,金融建模,VBA的高级筛选功能,数据透视表的自动刷新等各种利器!这些利器将让您在职场中战无不胜,攻无不克!
 


 

      You are a business analyst at the Sales department of Albertech Inc. Albertech Inc currently has thirteen regional branches. Each branch has its Income Statements and Balance Sheets. The Director of Sales needs a report to evaluate each branch’s performance. Specifically, he gives three performance factors to evaluate: operating efficiency (measured with Asset Turnover, 30% weight), profitability (Profit Margin, 40% percent weight) and financial security (measured with Leverage Ratio, 30% weight). In 


(更多资料和具体参加方法)

本页最后更新: | -- | 网站设计和虚拟主机服务 WECAN.ca CMS