Course Background:
With the development of informatization and the continuous maturity of artificial intelligence, data collection has been networked, automated, and financial robots have begun to replace the most basic accounting and accounting. So, as a financial person, how should we adapt to this change?How to organize data from the information system into a standout analysis report?How to go from accounting accounting for digital porters to management accounting proficient in data analysisTo achieve transformation, how to use data to empower from a single accounting treatment to support business analysis?Realize the integration of industry and finance, and transfer to financial BP.
All of the above questions revolve around how to process and analyze data, revitalize data, and apply it to actual work scenarios to support business operations, so that management can make the right decisions. In fact, to solve this kind of problem, we need a powerful tool to empower, and the current excel is already the standard software for every financial person's computer, as the most flexible and easy-to-grasp data processing tool, this tool should be used to the extreme, to create their own data-based decision-making ability, but many people are using stupid methods as data porters to work overtime to process data, resulting in the inability to realize the transformation from accounting to analysis, and can not turn data into their own strength, so as to realize the value of financial people。
The course will take you to change the concept of data processing, master the data logic, and exercise the data thinking of financial people, so that you can process data methods, understand tools, and have skills in your future work, so as to improve the Excel data application ability of corporate financial people and master the latest Excel BI tools, dig the story behind the operational data, and create the ultimate skill of financial Excel: one-click refresh of automatic reports, fast and good production of financial business analysis reports, so that you will not be replaced by financial robots, away from overtime!
Course Benefits:
Say goodbye to overtime and sort out the messy data at once.
Master the ideas and perspectives of analyzing data.
Learn to make dynamic and interactive dashboards.
Use powerful business intelligence capabilities on a familiar Excel interface.
Boost productivity and effectiveness at lightning speed.
Complete the transformation from accounting data to analyzing data.
Course Duration:2 days, 6 hours a day.
Course Target:People engaged in finance from all walks of life have a certain foundation in the use of Excel.
Course Method:Theoretical explanation + case teaching + teacher interaction + in-class guidance + hands-on training.
Teaching Software:Participants should bring their own laptops and install Office2016 and above versions in advance (if not, the teacher can provide a broken version of Office2019 installation package).
Course outline
Lecture 1: The Excel tool of financial elites must be known
Application Scenarios:
01-Learn to use shortcut keys and buttons to quickly complete a certain job, save time on desk work, and no longer work overtime (such as switching to display formulas or calculation results, entering the date of the day with one click, filtering out the required data with one click, etc.).
02-Document confidentiality presentation (the designed report is filled in by the subordinate branch personnel, I hope not to change the pre-designed formula, etc., I hope that some ** will not be seen, and I hope to set a password).
03-Reject a large number of calculation formulas to enter one by one (no need to copy and modify, figure out the concepts of different references).
04-Beautiful design of the report (format, color, font, etc. can be quickly realized, so that the leader does not worry about the aesthetics).
Tool Application:
1. Commonly used shortcut skills in excel
1.One-click summation of statistical summarization.
2.Data comparison can be graphed with one click.
3.Drop-down data list for quick entry.
4.Quickly populate and complete report data.
5.Quickly compare lines by line to find differences.
2. Application skills of common data processing tools
1.Use the Columns tool to standardize data types.
2.Split application of summary note data.
3.The classification is displayed in the application of the financial statements.
4.Custom sorting makes analysis reports clear at a glance.
5.Quickly find and extract analytical data with advanced filtering.
6.Use conditional formatting for dynamic alerts on financial statement anomalies.
3. Restricted editing skills in the financial report data area
Fourth, the use of the best style to quickly beautify the financial statements
Lecture 2: Efficient Application of Common Formula Functions in the Financial Workplace
Application Scenarios:
01 - Start from scratch when you encounter a data update.
02 - There are many work reports with repetitive similarities.
——Once the formula is designed, you only need to import the data of the new month to complete the report of each month, and all other data collation, calculation, and update are handed over to the computer to complete!Your value will be reflected in the analysis of this data!
Tool Application:
1. Basic knowledge of formulas and functions
1.Formula and function structure, syntax, and usage considerations.
2.Cell referencing and quick conversion.
3.A practical way to create complex formulas.
4.Simplify formulas with names.
5.Custom number formatting makes the data easy to read.
2. Flexible application of five types of common functions in financial scenarios
Application 1: Calculation of the financial date layer - efficient application of date functions
1) The granularity calculation application of the date dimension.
2) Calculate the expiration date according to the duration of the contract.
3) Calculate the difference between the two day periods.
Application 2: Extract summary information from financial reports - efficient application of text functions
Application 3: Logical judgment of business data - efficient application of logical functions
1) Logical judgment under single conditions.
2) Logical judgment of data intervals.
3) Logical judgment under complex conditions.
Application 4: Statistical summary of business data - efficient application of statistical functions and mathematical trigonometric functions
1) Counting under single and multiple conditions.
2) Summation of single and multiple conditions.
3) Statistical summarization under complex conditions.
Application 5: Finding and applying business data - finding efficient application of functions
1) Data search under single conditions.
2) Data lookup under crossover conditions.
3) One-to-many search of detailed tables and summary tables.
4) Dynamic reference search of financial statement data.
Lecture 3: Data AutomationMake your financial statements refresh with one click
Application Scenarios:
01-How to quickly aggregate and analyze massive amounts of data over the years and automate the aggregation?
02-How to quickly obtain data that needs to be summarized and analyzed from a variety of different data sources?
03-How to quickly combine the annual operational data and discover the secrets behind the data?
——The application of the Power Query component in the Excel BI tool (excellent data cleaning, sorting, integration, etc.), is more powerful than the worksheet function, more concise than the macro and VBA, and can truly achieve "data in, one-click refresh".
Tool Application:
1.Recording macros automates data processing.
2.Comparison and differences between the efficiency of macro and Power Query tools.
3.Use Power Query to proactively obtain analytical data from information systems.
4.Familiarize yourself with the interface and features of the query editor, Power Query.
Automate the processing of your data with Power Query
1) Select and delete rows and columns.
2) Split columns.
3) Extract characters.
4) Grouping by.
5) Replace values with padding.
6) Transpose and reverse rows.
7) Perspective vs. reverse perspective.
8) Calculate on the field.
Leverage Power Query for quick consolidation of data reports
1) Horizontal merging of data: Merge query applications (break through the vlookup function to achieve dynamic query summary).
2) Vertical merging of data: Append query application (dynamic summary of data across worksheets).
3) Merge data files: Merge folders (automatic summary of financial report data in different files).
Lecture 4: Use pivot table + model thinking to quickly complete various data statistical analysis
Application Scenarios:
01-Faced with various temporary data analysis requirements of the management, in order to get the results desired by the management, it is often necessary to export a large amount of data, and then process, process and analyze - Tool: pivot table in excel.
——Use the pivot table to quickly form a report of your own insights, use the latest data modeling and analysis methods to solve complex business problems in the business process, and provide decision-making basis for managers and decision-makers in front of a large amount of data
Tool Application:
1.Basic methods and considerations for creating pivot tables.
2.The structure layout and drag-and-drop ideas of pivot tables.
3.Ideas and methods for automating pivot table data.
Pivot Table Tool Application: Quickly prepare statistical analysis of financial statements
1) Statistical summary of financial data.
2) Multi-dimensional analysis of operational report indicators.
3) Grouping analysis of financial data.
4) Analysis of the composition and proportion of financial data.
5) Creation of custom financial analysis indicators.
6) Year-on-year and month-on-month analysis of operating data.
5.Modeling and Analytical Thinking for Data Analysis.
6.Build a data model with Power Pivot.
7.Manage table relationships in the data model and create analysis instructions.
8.Leverage the data model to create multi-table pivots to analyze business problems.
Lecture 5: Make beautiful and professional charts to present the results of data analysis
Application Scenarios:Through several practical cases, you can modify the various elements of the chart as you like, modify and beautify the chart, and make an Excel dashboad dashboard according to the analysis requirements, so that managers and decision-makers can understand the analysis data at a glance and gain insight into the business value behind the data.
Tool Application:
1. Technical preparation to become a master of charting
1.Cognition: Not all data can be represented graphically.
2.Choose the chart type correctly.
3.The basic way to draw a chart.
4.Format the diagram elements however you want.
5.Appends a data series to the chart.
6.Select a data series that is difficult to select.
7.Change the chart type of the data series.
8.Make good use of primary and secondary axes and auxiliary columns.
9.Excel chart defects and remedies.
2. The production of commonly used financial analysis charts
Figure 1: Comparative analysis chart.
Figure 2: Trend analysis chart.
Figure 3: Structural analysis diagram.
Figure 4: Distribution analysis chart.
Figure 5: Achievement analysis chart.
Figure 6: Factor analysis chart.
Think:What is Excel Dashboard Dashboard.
Applications:The design process of Excel Dashboard.
Practical Cases:Production and presentation of interactive dashboards for business data visualization.