MO-230 exam prep: Microsoft Excel for Business Finance

Explore the Excel interface by opening a blank workbook, navigating the ribbon and tabs, and learning how to open, save, and customize options.

Learn to work with workbooks, spreadsheets, and worksheets, add, delete, and rename sheets, navigate cells with tab, arrows, and enter, and enter text, numbers, dates, and currency in cells.

Save and export Excel workbooks in multiple formats, such as xls, xlsx, xlsm, xlsb, csv, txt, and pdf, choosing OneDrive or local storage and sharing options.

Import data from various sources in Excel by loading delimited CSV files or fixed-width text, previewing with the data import wizard, and setting dates correctly during import.

Learn to consolidate data from CSV, tab-delimited, and fixed files into one Excel workbook using copy, cut, paste, and the clipboard, with keyboard shortcuts and basic save and rename steps.

Learn how to remove duplicate data, delete or hide rows and columns, and resize cells in Excel to clean and organize financial records.

Format cells for consistency by applying font, alignment, borders, and number formats. Use wrap text, merge, and shrink to fit; copy styles with the format painter and cell styles.

Master hands-on data import and cleaning in Excel by working with fixed-width text files, csv imports, date handling, duplicates removal, and formatting—headers, colors, borders, currency—and saving as xlsb and pdf.

Apply data validation to enforce accounting data integrity by restricting inputs with four-digit account numbers, date ranges, and numeric limits, while using error alerts and warning prompts to guide users.

Identify data outside defined standards in Excel by enforcing data validation, circling invalid data, using aggregations and filters, and correcting outliers to ensure accurate accounting data.

Protect workbooks, worksheets, and cells in Excel using passwords for opening or modifying, and by locking sheets to prevent editing or adding new sheets.

Learn to split a single data column into multiple columns using data text to columns, with delimited or fixed width, and delimiters like hyphen or space; explore Power Query.

Learn to use fill features to extend series and patterns in Excel, drag the fill handle, and flash fill to extract data, with tips on when it works or not.

Apply data validation for decimals under 10,000 with warning, circle invalid data, verify data integrity, split hyphen text to columns using flash fill, and auto-fit widths practice activity two.

Learn to use the ampersand to combine text from cells, build account names with a hyphen as a string literal, and copy formulas with relative references when filling down.

Learn to combine values in Excel using text functions like concat and text join, versus the ampersand, and handle locale separators and ranges like D2:G2, and ignore empty cells.

Learn to extract characters from account numbers using Excel text functions like left, right, and mid, then locate with find and measure length with len, and clean with trim.

Master Excel date functions to transform transaction dates, by adding days or months, extracting year, month, day, weekday, and using date value, text, today, now, and date diff.

Practice activity 3 teaches building Excel formulas to combine account and bank, extract first word, month, last day of month, trim spaces, and format dates for financial analysis.

Learn to perform horizontal analysis in Excel by calculating variances and percentage changes across balance sheet and income statement figures, compare quarterly and year-over-year, and assess seasonality.

Learn to calculate budget versus actual variance and percent difference, and compare it against benchmarks using if functions and conditional formatting. Explore paste special options and nesting if statements.

Use the dollar sign to fix a column or row in formulas, creating absolute or mixed references that stay put when copying across or down.

Learn to perform vertical analysis in Excel by comparing a single period's line items to totals such as assets and liabilities, and create aggregations like sum and count.

This practice activity 4 solution guides you through summing and autosum, calculating year-to-year differences, percentage changes, and benchmarking with absolute references for horizontal and vertical financial analysis in Excel.

Analyze financial statements in Excel by using formulas, fix duplicate totals with subtotal, and prepare financial metrics and pricing analysis including gross profit, operating income, and net profit.

Compute profitability, liquidity, and solvency ratios, including gross and net margins, return on assets, return on equity, current and quick ratios. Freeze panes in Excel to keep headers visible.

Identify and report formula errors using formula auditing tools to verify and correct calculations, trace precedents and dependents, show formulas, and handle issues like division by zero.

Use subtotal to avoid duplication when totaling cash flows and net cash activity. Calculate operating cash flow percentage, reinvestment ratio, and cash flow index; use auditing and freezing panes.

Create a dashboard in Excel that pulls current assets, long-term assets, current liabilities, gross profit, and net profit from financial statements, using cross-sheet references, formatting as percentages, and shapes.

Master printing Excel dashboards by using page layout options, including scale to fit, orientation, margins, print areas and titles, and repeating headers across pages.

Learn to create and customize charts in Excel by selecting data ranges, using recommended charts, adding data labels, filtering series, and moving charts to their own sheet.

Format and customize charts in Excel by adding axes, chart titles, data labels, legends, data tables, and trend lines. Verify data series, axis labels, and chart type for accuracy.

Improve accessibility and usability of Excel dashboards by adding hyperlinks for navigation, using charts and conditional formatting, and applying alt text and accessibility checks to support screen readers.

Create a dashboard by linking data across spreadsheets to display 2027-2029 cash and cash equivalents, then add a column chart with alt text and set print area.

Use Excel PMT and PV to calculate loan values, including monthly payments, present value, and total interest for a $50,000 loan at 6 percent over 36 months.

Create a loan amortization schedule in Excel by calculating the monthly payment with PMT, then compute monthly interest with IPMT and principal with PPMT, while tracking periods and remaining balance.

Build an Excel amortization schedule for 300,000 loan over 15 years at 5.25%, using PMT, IPMT, and PPMT to compute payments, interest, and remaining balance with charts and what-if analyses.

Demonstrate the time value of money through loan and savings calculations, including amortization and monthly payments. Apply PV and FV functions to compute current and future balances across 36 months.

Use Excel's scenario manager to create and compare loan scenarios by changing interest rate and term, producing a scenario summary with current values and outcomes.

Apply what-if analysis with data tables to explore how interest rate and loan term affect total interest, using one-variable and two-variable data tables.

Apply Excel loan analysis techniques using the fv function to compute remaining balance, explore scenarios with scenario manager, goal seek, and data table to evaluate loan payments and rates.

Calculate bond prices and yields in Excel using the price and yield functions, given purchase date, maturity date, coupon rate, face value of 100, and payments per year.

Apply two what-if analysis techniques to bond calculations in Excel, using data tables and goal seek. Avoid common traps and fix input links to ensure accurate results.

Apply the dividend discount model to estimate intrinsic stock value by linking dividends, payout ratio, sustainable growth, and risk premium.

Calculate bond prices and yields using price, settlement, maturity dates, rate, and frequency. Analyze stock value with payout ratio, growth, required return, and intrinsic value.

Analyze historical data using formulas and build a forecast sheet in Excel, exploring date-based forecasting, confidence intervals, seasonality, and chart options to project revenue.

Explore advanced forecast sheet options by using interpolation for missing points, handling duplicates with average or sum, and pasting values with transpose into the income statement.

calculate historic growth rates from quarterly data, apply growth rates to forecasts, compare simple versus compounded growth, and use goal seek to set target figures in Excel.

Learn to project interest payments and cash budgets by calculating principal times rate, adjusting for periods, using PMT and IPMT, then build cash budgets from revenue minus costs to 2031.

Forecast 2030-2032 cash flows in Excel and adjust forecast options, including removing confidence intervals and using paste special with transpose. Calculate growth rates, set absolute references, and chart cash flow.