Functions, Data Analysis, Presentation and Finance w/ Excel

Explore basic Excel techniques, including entering data, dragging the plus to extend data, creating formulas with equals, and applying functions like average and standard deviation, while practicing to build confidence.

Learn to set up quarterly data in Excel, merge headers, name ranges, and paste values while using formulas to generate random numbers and compute averages, maxima, and standard deviation.

Learn practical Excel essentials for functions, data analysis, and presentation, including formatting, random data generation, named ranges, data validation, vlookup, index, and chart creation.

Learn to fill across worksheets by creating a new sheet, selecting sheets, and using cross worksheets to copy data; rename sheets for quarters and build a chart.

Explore Excel basics for data analysis and formatting across quarters, including merging cells, conditional formatting, data bars, and essential functions like count and sum if.

In chapter two, learn to create and customize charts in Excel by inserting charts, changing types, and inputting data, while enabling the developer tab and form controls.

Apply Excel to input, format, and analyze data using if statements, vlookup, data validation, and charts; build commissions and class logic with structured tables and large functions.

Learn to build an interactive excel setup: generate stochastic weather data for the New York area, switch years with option buttons, and drive a line chart via vlookup.

Learn to extract the first name from a full name by locating the space and using left and mid, and compute bonuses with vlookup on years, sales, and marital status.

Learn the basics of VBA in Excel by writing if statements, referencing cells, and using message boxes to control flow and testing with the VBA editor.

Develop and run a random path simulator in Excel using a scrollbar to adjust mu, delta-t, and sigma; analyze returns with mean, standard deviation, skew, and kurtosis.

Learn to build VBA functions in Excel with a module, using if statements and message boxes, read grades from cells, and create a function area circle using pi.

Build an Excel data model in section three to simulate income, state, GPA, admissions (early or regular), and financial aid using random numbers and a lookup table.

Explore basics of text functions in Excel, including left, right, and find to extract data, learn length considerations, generate data with ran between 1 and 100, and customize charts.

Explore interactive charts and data analysis in Excel, using random data, booleans, formulas, and chart features like legends, data selection, and descriptive statistics.

Compute tenure from hire dates by subtracting from today and dividing by 365.25, then apply 10% or 2% bonuses with if statements and use averages for a $5000 two-condition bonus.

Explore section three with Excel: generate fake pay data using rand between in euros, apply bonuses via if statements, and compute totals, vlookup, pay rank, and correlations.

Explore the basics of vlookup in Excel, including table selection, first-column lookup, column index, and exact or approximate matches with examples like names and extensions.

Learn how to generate fake Excel data with randomization and lookups, model house features (bedrooms, bathrooms, area, style, square footage), and compute a weighted price score with simulations.

The lecture demonstrates using VBA in Excel to populate data and headers. It covers enabling the developer tab, inserting modules, defining variables, and filling cells with random city values.

Explore how to model demand with random numbers, define cutoffs, apply VLOOKUP, build probability distributions, and compare two probabilistic scenarios in charts.

Explore how to use a data table and what-if analysis to evaluate production decisions under probabilistic demand, calculating profit, disposal costs, and risk using average and standard deviation.

Explore Excel's financial functions under the formulas tab, including bonds, stocks, depreciation, discount rates, present value, future value, and bond price, with one missing input enough to solve.

Build a dynamic stock chart in Excel by importing a year of daily data, cleaning columns, and using VLOOKUP and INDEX+MATCH to show closes and volume for selected dates.

Calculate loan payments and future value with PMT and FV for a $10,000, 10-year loan with monthly payments and 120 periods at 3.75% APR, revealing total payments and interest.

Explore how to compute monthly loan payments with the PMT function, build single and two-variable data tables, and optimize loan scenarios using goal seek and solver.

Analyze how bond prices respond to interest rate changes by calculating McCawley and modified duration, estimating price changes, and pricing bonds with present value and yield to maturity in Excel.

Demonstrates building an amortization table for a 30-year loan, computing monthly payments, interest, principal, and balance across 360 periods, using PMT, IPMT, and PPMT.

Learn to build an amortization table in Excel, compute down payment and loan, apply the PMT function for monthly payments, and analyze interest versus principal using what-if analysis.

Explore how to use a data table in Excel to analyze loan payments across different interest rates by adjusting the rate, present value, and periods with what-if analysis.

learn to format excel data, apply borders and colors, use conditional formatting and color scales, rank data with array formulas, and calculate totals, averages, standard deviation, and medians.

Explore how to model loan payments in Excel using formulas, PMT, and data tables (one- and two-variable), with interest rate scenarios, randomization, and charted results.

Explore net present value and internal rate of return calculations using cash flows, discount rates, and initial investment, with practical examples and excel workflows.

Explore randomness with a simple stochastic process in Excel, simulating 180-day paths from 100. Use a data table to analyze gains, losses, and average return across paths.

Learn to generate random numbers in a spreadsheet using rand, scale to different ranges, convert to integers, and use random between for bounds; test normal distribution with frequency and bins.

Apply a linear congruential generator using x' = (A*x + B) mod M to produce random numbers, then scale to 0-1 in Excel.

Use a random number generator to generate x values, square them to get y, and average results to approximate the area under the curve; this is Monte Carlo type 1.

Apply Monte-Carlo type 2 to determine how often y is below x squared by sampling random x and y, counting true cases, and forming a closer approximation.

Generate normally distributed values from uniform random numbers using the Box-Muller method and its faster polar variant, producing two normals per pair of uniforms for efficient simulations.

Model a 60/40 stock and bond portfolio using random numbers and a negative correlation to simulate yearly returns. Use Excel data tables to explore 20-year outcomes and allocations.

Analyze stock returns from Apple, GE, Coca-Cola, JPM, and Ford using log returns, compute averages and standard deviations, and build a portfolio variance-covariance matrix to estimate return and risk.

Master using if statements and count if, sum if, and average if in Excel to compute sales by person, leverage criteria ranges, and build efficient, array-based calculations.

Use countif and averageif in excel to analyze county housing data, apply advanced filter across worksheets, and compute counts and average prices by county, agent, baths, and pools.

Explore database formulas in excel, using a database range, a field, and criteria to calculate average, count, max, and sum. Combine conditions across areas like central and north county.

Master advanced filter in Excel to extract data with complex criteria using list and criteria ranges, and copy results to a new location.

Calculate final grades by weighting tests, the final, and homework, using grade_data. Apply database functions and an advanced filter to analyze averages and criteria like name and state.

Learn to create trendlines in Excel using the trend function for multiple data series, including X and Y, with regression lines on charts and scatterplots.

Evaluate trendlines using forecast functions for linear and exponential smoothing, extend data to forecast the next point, and interpret slope, intercept, and R square for line estimation.

Enable the Excel analysis toolpak and use data analysis features like descriptive statistics, correlation, covariance, and regression, then build histograms with bins and review residuals.