Course Description

This course is taught in online mode and consists of 5 units.

The duration of the course is 110 hours That distributes between content and collaboration tools. Upon completion, the student will receive an accrediting diploma.

Training is done through our Virtual Campus , with this modality you will have all the didactic content on the course platform and it will be accessible, from the start day of the course, 24 hours a day, every day of the week. Access to the Campus will be available for 3 months.

The student will also have participation forums , as well as a continuous tutoring .

Content

• Basic worksheet modeling
• Range names
• Lookup functions
• The INDEX function
• The MATCH function
• Text functions and Flash Fill
• Dates and date functions
• Evaluating investments by using net present value criteria
• IRR, XIRR, and MIRR functions
• More Excel financial functions
• Circular references
• IF, IFERROR, IFS, CHOOSE, and SWITCH functions
• Time and time functions
• The Paste Special command
• Three-dimensional formulas and hyperlinks
• The auditing tool and the Inquire add-in
• Sensitivity analysis with data tables
• The Goal Seek command
• Using the Scenario Manager for sensitivity analysis
• The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK functions
• The SUMIF, AVERAGEIF, SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS functions
• The OFFSET function
• The INDIRECT function
• Conditional formatting
• Sorting in Excel
• Excel tables and table slicers
• Spin buttons, scrollbars, option buttons, check boxes, combo boxes, and group list boxes
• The analytics revolution
• An introduction to optimization with Excel Solver
• Using Solver to determine the optimal product mix
• Using Solver to schedule your workforce
• Using Solver to solve transportation or distribution problems
• Using Solver for capital budgeting
• Using Solver for financial planning
• Using Solver to rate sports teams
• Warehouse location and the GRG Multistart and Evolutionary Solver engines
• Penalties and the Evolutionary Solver
• The traveling salesperson problem
• Importing data from a text file or document
• Get & Transform (or s/b Get & Transform?)
• Geography and Stock data types
• Validating data
• Summarizing data by using histograms and Pareto charts
• Summarizing data by using descriptive statistics
• Using pivot tables and slicers to describe data
• The Data Model
• Power Pivot
• Filled and 3D Power Maps
• Sparklines
• Summarizing data with database statistical functions
• Filtering data and removing duplicates
• Consolidating data
• Creating subtotals
• Charting tricks
• Estimating straight-line relationships
• Modeling exponential growth
• The power curve
• Using correlations to summarize relationships
• Introduction to multiple regression
• Incorporating qualitative factors into multiple regression
• Modeling nonlinearities and interactions
• Analysis of variance: One-way ANOVA
• Randomized blocks and two-way ANOVA
• Using moving averages to understand time series
• Winters method and the Forecast Sheet
• Ratio-to-moving-average forecast method
• Forecasting in the presence of special events
• Introduction to Probability
• An introduction to random variables
• The binomial, hypergeometric, and negative binomial random variables
• The Poisson and exponential random variables
• The normal random variable and Z-scores
• Weibull and beta distributions: Modeling machine life and project duration
• Making probability statements from forecasts
• Using the lognormal random variable to model stock prices
• Importing Historical stock data into Excel
• Introduction to Monte Carlo simulation
• Calculating an optimal bid
• Simulating stock prices and asset-allocation modeling
• Fun and games: Simulating gambling and sporting event probabilities
• Using resampling to analyze data
• Pricing stock options
• Determining customer value
• The economic order quantity inventory model
• Inventory modeling with uncertain demand
• Queuing theory: The mathematics of waiting in line
• Estimating a demand curve
• Pricing products by using tie-ins
• Pricing products by using subjectively determined demand
• Nonlinear pricing
• Array formulas and functions
• Recording macros
• Advanced Sensitivity Analysis