开心一笑:和Excel 恋爱太痛苦,雨过天晴都是好日子

有人开玩笑地说,每天工作都离不开Excel,等于是和Excel结了婚。不信?找一个FA/BA问问,问他是否每天和Excel在一起呆的时间,超过和爱人在一起呆的时间。 Excel带给你的喜怒哀乐,爱恨情仇,肯定超过你和你爱人之间的。 不信?再找一个Data Analysis问问,问他有没有过被Excel逼得想砸电脑的时候;有没有过因为Excel干得漂亮,得到全组人称赞仰慕的时候。

有人又要说了,Excel谁不会,我自己买本书或者上YouTube学学就会了。是的,简单的Function,你是可以这么学。可问题是,光会一些Function,不客气地说,等于不会。为什么?因为Excel的真正的妙处是在与如何将各种Function创造性地组合起来,将一大推看似零散的数据,经过整理,建立分析模型,甚至用VBA将其编程自动化。注意了,学的时候一定要用真正的工作实例,真正的数据,尤其是来自于世界顶尖500强公司的,而不是从某一本教科书上或网站上抄来的,也不是你平时在学校里做的Ratio Analysis, SWOT Analysis, Alternative Evaluation什么的。当你看到真实的分析,你会有“原来,Excel要这样用;原来Excel是这么高效强大;原来,这才是真正的分析 ……”的感觉。通过零距离地分析真实案例和项目,你和要申请面试的工作才能零距离,对吧?时间久了Excel成高手了,回忆当初“和excel 恋爱期”的苦涩,你会觉得日子咋好的没法说了嗫,哈哈。想和Excel过上神仙眷侣的生活,只有想把它搞熟摸透,好日子开始了!

 学精Excel&VBA后果是这样的 

后果之一:让你在现有岗位工作效率提高70%!
用Excel办公你是不是常年陷入"穷忙,疲于奔命"的状态?工作低效使得职位升迁没有你份,增加薪水你不沾边,好挫败呀。如果你正是这个状态,只要你肯下功夫学精Advance Excel & Excel VBA ,你就可以做到:
1.15 分钟内做出一个别人用3个小时才能做出一个报表;
2.两小时内分析出一个别人要用一天时间才能找出问题根源;
3.在最短的时间做出一个最优化的计划,而别人只能优化到 70%。强化你的Exce & VBA l技能吧,多好都不为之过,必须的!
后果之二:帮你成功拿下FA、BA 等不同领域的Analysis工作Offer
第一份FA/BA必须掌握硬技能通常是指:系统技能、分析技能和Excel&VBA技能 
* 很多人觉得找个网上的软件自学或者找一门网上的课程,就能学好Excel 和VBA,但是你学到的只是一些基础知识,不跳进水里,怎么能学会游泳呢?不亲临实战,会再多得Excel functions,又怎么能搞掂面试和找到工作呢?
* 我们的Excel 和VBA培训,在任何课本里也找不到,在任何网站里也学不到,我们教你的软件技能是在工作流程里的应用, 我们还教给你怎么用它写报告和上下级沟通,怎么用它交流,更重要的是交给你在工作中怎么把握你的觉得。
*我们的目标目标:
就是 -由Financial Manager 亲自带你,迅速在48小时内,掌握别人5年都不能学会的技能,从零开始,一步一步,亲身体验一个Analyst从找工面世开始,到实际工作的真实情形。 让您在面试时,镇定从容一举拿下Offer;让您在办公室里,游刃有余;让您在老板面前,举足轻重;让你在事业上,步入腾飞,工作职位包含FA、BA等各个领域的Analysis职位。
Excel  & VBA免费公开课    
周日   10:00 am Excel 
周日   2:00 pm Excel VBA 
 
如果你想更好的平衡工作与生活,最好就是呆在一个职位上,拿着7 - 8 万的工资,轻轻松松的一直工作下去。事实上哈,这是全世界90%职场人追求的,那么这个标准就是个国际范围的标准喽,简称"国际范"了,FA /BA 就是这个工作当之无愧呀,她也是个绝对的高大上的工作。

Financial Analysis & Business Analysis 的诱人之处:
01
  
就业广泛,机会众多
02
  
 起薪高,待遇丰厚
03  
 职业发展机会多,易于平衡工作和生活,
  
  
 
 
   
                                                     
实战一
In a job interview, the recruiter wants you to take an Excel test before moving to the next step. You learned some Excel from YouTube and even some training class, however, your test result is not good enough to impress the recruiter. So, he told you he would call you when a job fits you, but that call never comes. Does this sounds familiar? Definitely you don't want this happen again! We will walk you through real Excel tests of a famous recruiter. The next time, you will be best.
案例特色:全面解码招聘者的测试试题,知道他们要考什么,怎么去准备,进而在众多的面试者中胜出,拿到自己心仪的工作。同一个测试,对有准备的人来说是机会,对没有准备的人来说,却意味着又一次白白浪费了机会,您呢?
试题举例

实战二
You are a financial analyst working for a world famous consulting company. CFO wants you to create an Excel model to monitor and control monthly wireless expenses of the company's 3,000 employees. You need to download 12 months data from ERP system, convert data, analyze data by cost centers, 
compare total amount, rate and usage to prior three years, capture significant variances, and present your findings. Are you ready to overcome this challenge?
案例特色:巧妙运用组合公式和函数,对于大量的数据进行转换,分析,计算,汇总,比较。一旦公式和函数设定,以后每个月所做的只是从系统中下载数据,复制,粘贴就可以了。此案例带给您的窍门,只有经验丰富的Senior Analyst才知道,但平常又秘而不宣。这个道理,不说您也懂的。没有这些窍门,知道再多的公式和函数,也只是纸上谈兵。
练习截屏

实战三
You are preparing financial statements from trial balance. The existing model created by previous analysts uses direct cell links. However, since every month the G/L accounts have minor changes, you need to check each link one by one with extreme attentions. You have to work until midnight to get financial statements balanced before deadline. Your goal is to create a new model to complete the same work within 20 minutes and absolutely accurate. Furthermore, you want to create an interactive report with dropdown lists to display MTD and YTD of any month.
案例特色:以全新的思维,全新的方法来准备财务报表,告别繁琐冗长的单元格连接法,让完成这项工作成为享受而不是痛苦。你的报表永远准确,如果有人怀疑,你尽可大胆地脱口而出,"You are wrong"。另外,互动式下拉菜单的创建,可以让你在一个Excel 文件中任意展现任何一个月的当月值和年初至今值,而不用去浪费时间,为每一个月都要去创建一个Excel 文件。此案例的方法不仅仅用来准备财务报表,大量其它的商务报告都可以用这个方法。
练习截屏
实战四
You are required to prepare a dynamic dashboard for quarterly reporting based on two years P&L statements and budget. You need to display total, details and 13 months trend of sales, cost of sales, operating expenses and profits by selecting year, month and category from dropdown lists. You want to automate this dashboard instead of spending hours doing manually. The secret behind the scene will be decrypted in this exercise. You will become the master of dashboard.
案例特色:全面解码商务仪表盘的制作秘密与窍门,使得专家水平的商务仪表盘,在您面前不再神秘。多组下拉菜单,水平滚动条,通通不在话下。以后公司里大大小小的报告和演讲,都离不开你了,你也就成了老板面前,甚至老板的老板面前的大红人了,想不加薪都难啊。
练习截屏
我们只教你互联网上或者其他学校里面学不到的
因为我们的老师都是身经百战,经验极其丰富的商务专业人士。否则,岂不是随便一个人,盗版一套教材,看看Video就可以当老师了吗。
实例展示
Many people know VLOOKUP function. It is a great tool of our daily work - easy to learn and use. However, VLOOKUP is a volatile formula, which recalculate whenever calculation occurs in any cell on the worksheet. This significantly slows execution speed, and sometime even collapse your Excel. Besides, VLOOKUP can only look up from left to right and lacks of flexibility in model building.
Solutions:
Use non-volatile formula - INDEX / MATCH to overcome
the weaknesses of VLOOKUP. The only reason INDEX / MATCH is not so popular is because it's not as straightforward to use as VLOOKUP. After reading the following example, you will be loving to use and even show them off in front of your colleagues.
Example of INDEX / MATCH - To Create an Interactive Sales Report
Following the steps below, you can easily create a dynamic sales report. When selecting a month from the dropdown list, the corresponding sales will display in column Chart.
  1.        Create a dropdown list - Click Data tab / Data Tools group / Data Validation / Choose List in Allow field / Select all 6 months from source data in Source field / Click OK.
 2.        Create a dynamic table - Enter the INDEX / MATCH formula as below to extract sales data of the given month from Source Data.
3.       From the dynamic table, create a column chart. 
4.       When you change the selection from the month dropdown list, the column chart will be updated automatically.
Advanced Excel for Business Professionals
On Your Way to Becoming an Analyst
Module One - Dealing with Data (6 hours)
  • Data Cleaning
  • Data Mining
  • Dada Mapping
  • Data Analysis
Module Two - Business Analysis and Reporting (6 hours)
  • Business Reporting
  • Business Analysis
  • Consolidation and Reporting
Module Three - Visual Analytics and Dashboard (6 hours)
  • Analysis with Visual Aid
  • Powerful Decision Making Chart
Module Four - Business Modelling (6 hours)
  • Business Modeling Essentials
  • Modeling for Decision Making

 

 

  Excel VBA for Business Professionals

 

On Your Way to Becoming an Analyst
  1. 1.      Excel VBA Essentials (3 hours)
  • Introduction to VBA
  • Visual Basic Editor
  • Getting Started
  • Cleaning Codes in VBE
  1. 1.      Working with Range Objects (3 hours)
  • Excel Object Model
  1. 2.      Range Properties and Methods (3 hours)
  • Range Properties
  1. 3. Control Logic and For Each Loops (3 hours)
  • IF Constructions
  1. 4.     For Loops and Do Loops (3 hours)
  • For Loop
  • Do Loops
                VBA Project - Loan Amortization Model
  1. 1.     Other Excel Objects (3 hours)
  • Workbooks                      
  • Worksheets                                                  
  • Excel Charts                                            
  • VBA Arrays
  • VBA Project - Financial Reports Consolidation
  1. 2.     User Forms (3 hours)
  • Creating User Form Templates
  • Working with Form Controls                                           
  • Event Handling Codes
  • VBA Project - Car Load Model
  1. 3.     Other Topics (3 hours)
  • Scope of Variables and Passing Variables
  • Function Subs and Exercises
  • Using Excel Formula in VBA
  • Debugging                               
  • Displaying Alerts and Screen Updating
  • Summary of Good Programming Practices

  
INSTRUCTOR BACKGROUND  David Wang :

·         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. 

Advance Excel Course Outline:

 

Class

 

Tasks & Projects

 

Learning Objectives

1

Pre Analysis - Processing and cleaning data before conducting analysis - clean raw data, custom format numbers, concatenate text and numbers, find expense duplicate transactoins. Functions learned are VALUE, TRIM, SUBSTITUTE, CONCATENATE, VLOOKUP, COUNTIF, IF ISNA, TEXT.

Develop skills to process and clean data before analysis. Review and get familiar with basic Excel functions. Creatively apply basic functions, custom number format and conditional format.

2

Financial Analysis - Analyze data by breaking down data and regourp with PivotTable. Functions learned are complicated nested IF functions, mapping techniques with VLOOKUP, togerther with text string functions. Create line of business, report, order lead-time report, top ten sales report, summarize next year's forecast.
Project One - Sales revenue analysis with large quantities of data.

Be able to create complicated nested formula to map data, breakdown data, compare data and regroup data into summary report. Creatively apply PivotTable to faciliate data analysis. Understand how to extract data from an eiscint PivotTable.

3

Consolidation and Reporting - SUMIF, SUMIFS, COUNTIF, COUNTIFS, MATCH, OFFSET, INDEX, INDIRECT functions and nested formula, comparison of lookup methods, create interactive Sales reports by regions, by products and by periods. Project Two – Expenses Analysis by creatively applying all knowledge and skills previously learned.

Understand lookup for values with variables, be able to create interactive dynamic reports with both data validation and form controls. Be able to consolidate data with consolidation function and with INDIRECT 3D reference techniques. Get familiar with Array formula in reporting.

4

Busienss Dashboards - Working with data that changes size, extract single with multiple criteria, extract multiple with single criteria, extract multiple with multiple criteria, 13 Month Trend Chart, Controlling Charts with Menus, Combo boxes and buttons, smoothing data & forecasting trends, building powerful Decision Making Charts. 
Project Three - Create financial dashboard from P&L statement.

Develop abilities to create dynamic charts with multiple dropdown lists, scroll bars and check mark from various data sources, and build up foundation for financial reporting and presentation.

5

Senario Analysis, Business Modelling, Audit & Security - Data table, Scenario Analysis, Process to create professional financial model, Introduction to VBA, Cleaning recorded macro in VBE, automation applied in modelling. Comprehensive exercise - NPV Analysis Model, Breakeven Analysis Model. Protect workbook and worksheet ensure security.

Perform one input or two inputs sensitivity analysis, and multiple inputs scenario analysis, master the process of creating a professional financial model. Practice to further understand busienss model building and analysis.

6

Master budget, inputs and assumptions, sales & collections budget, COGS and payables budget, OPEX budget, headcount budget, CAPEX budget, cash budget, budgeted P&L/BS/CFS.

Understand and go through the whole process of corporate Master Budget preparation.

7

Introduction to DBMS, relational database, create Tables in Access, assign primary key, build up relationship of tables created, and import/export from/to Excel. Create Queries with Queries Wizard and Queries Design in Access. SQL syntax of retrieving/Sorting/Filtering Data.

Be able to create blank Access, design tables and set up relationships of DMBS. Develop skills to conduct Queries with Query Wizard and Query Design in Access. Learn how to create queries with SQL.

8

 

SQL Concatenating Fields, Mathematical Calculation, Grouping/Having, Sub-query, Joining Tables, Combined Queries. Appending, Updating, Deleting Tables. Create Forms, Reports and Macros in Access. Develop Switchboard to navigate across Access, protect Access with passwords.

 

Continue to learn how to conduct queries with SQL. Master skills to create Forms, Reports and Macros, and design Switchboard  from Forms

 

 
 


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

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