Microsoft Excel 2019 Data Analysis and Business Modeling

Descripción del curso

Este curso se imparte en modalidad online y consta de 5 unidades.

La duración del curso es de 110 horas que se distribuye entre el contenido y las herramientas de colaboración. A su finalización, el alumno recibirá un diploma acreditativo.

La formación se realiza a través de nuestro Campus Virtual, con esta modalidad dispondrás de todo el contenido didáctico en la plataforma del curso y estará accesible, desde el día de inicio de curso, las 24 horas todos los días de la semana. El acceso al Campus estará disponible por 3 meses.

El alumno también dispondrá de foros de participación, así como una tutorización continua.

Contenido

• 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
• Queueing 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