Business Analysis with Microsoft Excel, Fifth Edition

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Book description

Gain Deeper Insights, Make Smarter Decisions, and Earn More Profits

It’s time to put the full power of Excel quantitative analysis behind your management decisions!

Related Content

Business analysis expert Conrad Carlberg shows you how to use Excel to perform core financial tasks all managers and entrepreneurs need to master: analyzing statements, planning and controlling company finances, making investment decisions, and managing sales and marketing. Carlberg helps you make the most of Excel’s tools and features in everything from business case development to cash flow analysis.

Becoming an Excel expert has never been easier. You’ll find crystal-clear instructions, real-world examples, insider insights, step-by-step projects, and much more. It’s all complemented by extensive web-based resources, from sample journals and ledgers to business forecasting tools.

· Get more insight from income statements and balance sheets

· Manage current assets and value inventories

· Summarize transactions from journal to balance sheet

· Analyze working capital, cash flow, statements, and ratios

· Optimize budgeting and planning cycles

· Make more accurate and useful forecasts and projections

· Measure product or service quality

· Plan investments, set decision criteria, and perform sensitivity analyses

· Analyze profits, pricing, costs, contributions, and margins

· Make better decisions in uncertain conditions

· Understand and maximize the value of fixed assets

· Efficiently import and export business data

· Use Excel and Power BI to analyze data from QuickBooks or other sources

Show and hide more Table of contents Product information

Cover Page
About This E-Book
Title Page
Copyright Page
Contents at a Glance
Contents
About the Author
Dedication
We Want to Hear from You!
Reader Services
1 Working with Income Statements
1. Keeping Score
  1. Choosing the Right Perspective
  2. Defining Two Purposes for Accounting
  1. Choosing a Reporting Method
  2. Cells in Excel
  3. Measuring the Operating and Nonoperating Segments
  1. Getting the General Journal into Excel
  2. Understanding Absolute, Relative, and Mixed References
  3. Getting the Journal Data to the Ledger
  4. Getting the Ledger Data to the Income Statement
  1. Using Straight-Line Depreciation
  2. Preparing the Trial Balance
  3. Moving Information into an Income Statement
  1. Power BI Desktop
  2. Power BI Service and Mobile Apps
  1. Designing the Balance Sheet
    1. Understanding Balance Sheet Accounts
    2. Understanding Debit and Credit Entries
    1. Using Sheet-Level Names
    2. Getting a Cash Balance for Multiple Cash Accounts
    3. Handling Restricted Cash Accounts
    1. Allowing for Doubtful Accounts
    2. Using the Aging Approach to Estimating Uncollectibles
    3. Using the Percentage of Sales Approach to Estimating Uncollectibles
    4. Displaying Doubtful Account Balances with Power BI
    5. Managing the Sort Order Via the Axis Values
    6. Managing the Sort Order with Another Field
    1. Dealing with Insurance as a Prepaid Expense
    1. Understanding the Inventory Flow
    2. Closing the Inventory Account
    3. Closing the Revenue and Expense Accounts
    1. Understanding Perpetual and Periodic Inventory Systems
      1. Perpetual Inventory Systems
      2. Periodic Inventory Systems
      1. Valuation Methods Summarized
      2. Using Specific Identification
      3. Using Average Cost
      4. Using the Moving Average Method
      5. Using FIFO
      6. Using LIFO
      1. Specification Identification
      2. Average Cost
      3. FIFO
      4. LIFO
      1. Understanding Journals
        
        Understanding Special Journals
        
        Structuring the Special Sales Journal
        
        Structuring the Special Purchases Journal
        
        Structuring the Cash Receipts Journal
        
        Structuring the Cash Payments Journal
        
        Building Dynamic Range Names
        
        Using Dynamic Range Names in the Journals
        
        Choosing Between Tables and Dynamic Range Names
        
        Creating the General Ledger
        
        Using Subsidiary Ledgers
        
        Automating the Posting Process
        
        Matching Costs and Revenues
        
        Broadening the Definition: Cash Versus Working Capital
        
        Determining the Amount of Working Capital
        
        Determining Changes in Working Capital
        
        Developing the Basic Information
        
        Summarizing the Sources and Uses of Working Capital
        
        Identifying Cash Flows Due to Operating Activities
        
        Combining Cash from Operations with Cash from Nonoperating Transactions
        
        Understanding a Report by Means of Common-Sizing
        
        Using Common-Sized Income Statements
        
        Using Common-Sized Balance Sheets
        
        Using Comparative Financial Statements
        
        Assessing the Financial Statements
        
        Handling Error Values
        
        Evaluating Percentage Changes
        
        Interpreting Industry Averages and Trends
        
        Comparing Ratios Within Industries
        
        Analyzing Ratios Vertically and Horizontally
        
        Getting a Basis for Ratios
        
        Finding and Evaluating Earnings Per Share
        
        Determining Gross Profit Margin
        
        Determining Net Profit Margin
        
        Determining the Return on Assets
        
        Determining the Return on Equity
        
        Determining the Debt Ratio
        
        Determining the Equity Ratio
        
        Determining the Times Interest Earned Ratio
        
        Determining the Current Ratio
        
        Determining the Quick Ratio
        
        Determining the Average Collection Period
        
        Determining Inventory Turnover
        
        Creating Pro Forma Financial Statements
        
        Forecasting by Percentage of Sales
        
        Performing Sensitivity Analysis
        
        Projecting Quarterly Sales
        
        Estimating Inventory Levels
        
        Fitting the Budget to the Business Plan
        
        Making Sure You Have a Useful Baseline
        
        Moving Average Forecasts
        
        Creating Forecasts with the Moving Average Add-In
        
        Dealing with the Layout of Excel’s Moving Averages
        
        Creating Moving Average Forecasts with Excel’s Charts
        
        Making Linear Forecasts: The TREND Function
        
        Making Nonlinear Forecasts: The GROWTH Function
        
        Creating Regression Forecasts with Excel’s Charts
        
        Projecting with Smoothing
        
        Using the Exponential Smoothing Tool
        
        Choosing a Smoothing Constant
        
        Making Smoothed Forecasts Handle Seasonal Data
        
        Understanding ARIMA Basics
        
        Charting the Correlograms
        
        Starting with Correlograms to Identify a Model
        
        Identifying Other Box-Jenkins Models
        
        Displaying Forecasts with Power BI
        
        Using Power BI to Display Correlograms
        
        Monitoring Quality Through Statistical Process Control
        
        Using Averages from Samples
        
        Using X-and-S Charts for Variables
        
        Interpreting the Control Limits
        
        Manufacturing
        
        Publishing Control Charts with Power BI
        
        Using P-Charts for Dichotomies
        
        Choosing the Sample Size
        
        Determining That a Process Is Out of Control
        
        Using X-and-MR Charts for Individual Observations
        
        Creating SPC Charts Using Excel
        
        Charting the Operating Characteristic Curve
        
        Using NORM.S.DIST to Approximate BINOM.DIST
        
        Sampling Defects in Units
        
        Using the BINOM.INV Function
        
        Developing a Business Case
        
        Getting Consensus for the Plan
        
        Showing Your Work
        
        Developing the Inputs
        
        Identifying the Costs
        
        Moving to the Pro Forma
        
        Preparing the Cash Flow Analysis
        
        Understanding Payback Periods
        
        Understanding Future Value, Present Value, and Net Present Value
        
        Calculating Future Value
        
        Calculating Present Value
        
        Calculating Net Present Value
        
        Optimizing Costs
        
        Reviewing the Business Case
        
        Managing Scenarios
        
        Saving a Scenario for the Base Case
        
        Developing Alternative Scenarios
        
        Developing Scenarios That Vary Expenses
        
        Summarizing the Scenarios
        
        Calculating Internal Rate of Return
        
        Calculating Profitability Indexes
        
        Estimating the Continuing Value
        
        Understanding the Effects of Leverage
        
        The Effect of Business Risk
        
        Evaluating the Financial Implications of an Operational Change
        
        Evaluating Fixed Expenses
        
        Evaluating Effect of Increasing Fixed Costs
        
        Planning by Using the DOL
        
        Distinguishing Business from Financial Risk
        
        Determining the Debt Ratio
        
        Determining the Times Interest Earned Ratio
        
        Using Standard Deviations
        
        Using Excel’s Standard Deviation Functions
        
        Using Confidence Intervals in a Market Research Situation
        
        Calculating a Confidence Interval
        
        Interpreting the Interval
        
        Refining Confidence Intervals
        
        Regressing One Variable onto Another
        
        Interpreting the Trendline
        
        Avoiding Traps in Interpretation: Association Versus Causation
        
        Regressing One Variable onto Several Other Variables: Multiple Regression
        
        Using Excel’s Regression Add-In
        
        Interpreting Regression Output
        
        Estimating with Multiple Regression
        
        Using Excel’s TREND Function
        
        Creating a Scatter Chart
        
        Creating a Clustered Column Chart in Power BI
        
        Determining Original Cost
        
        Determining Costs
        
        Choosing Between Actual Cost and Replacement Cost
        
        Understanding the Concept of Depreciation
        
        Matching Revenues to Costs
        
        Using Straight-Line Depreciation
        
        Using the Declining Balance Method
        
        Using the Double Declining Balance Function to Calculate Depreciation
        
        Using Variable Declining Balance Depreciation
        
        Using Sum-of-Years’-Digits Depreciation
        
        Creating and Using ODBC Queries
        
        Preparing to Import Data
        
        Specifying Data Sources
        
        Creating Queries with the Query Wizard
        
        Creating Queries with Microsoft Query
        
        Creating Parameterized Queries in Microsoft Query
        
        Using Joins in Microsoft Query
        
        Include Row Numbers
        
        Adjust Column Width
        
        Preserve Column Sort/Filter/Layout
        
        Preserve Cell Formatting
        
        Insert Cells for New Data, Delete Unused Cells
        
        Insert Entire Rows for New Data, Clear Unused Cells
        
        Overwrite Existing Cells with New Data, Clear Unused Cells
        
        Managing Security Information
        
        Arranging Automatic Refreshes
        
        Setting Other Data Range Options
        
        Importing Data to Pivot Tables and Charts
        
        Get External Data and Get Data From Web
        
        Using Get Data with a Website
        
        Using Get External Data and VBA
        
        Using VBA to Update an External Database
        
        Getting at VBA
        
        Structuring the Worksheet
        
        Establishing Command Buttons
        
        Using Database Objects
        
        Finding the Right Record
        
        Editing the Record
        
        Exporting an Income Statement to Excel
        
        Publishing a QuickBooks Report in Power BI
        
        Preparing the Data in Excel
        
        Moving the Report to Power BI
        
        Parsing a Simple Subroutine
        
        Invoking QBFC
        
        Identifying the Sources of the Variables
        
        Understanding then Rationale
        
        Opening the QuickBooks Company File
        
        Opening the Excel File
        
        Allowing Access to QuickBooks Data
        
        Calculating the Contribution Margin
        
        Classifying Costs
        
        Estimating Semivariable Costs
        
        Producing Digital Video Discs (Continued)
        
        Increasing the Contribution Margin
        
        Creating an Operating Income Statement
        
        Calculating Breakeven in Units
        
        Calculating Breakeven in Sales
        
        Calculating Breakeven in Sales Dollars with a Specified Level of Profit
        
        Charting the Break-Even Point
        
        Choosing the Chart Type
        
        Linear Relationships
        
        Assignment of Costs
        
        Constant Sales Mix
        
        Worker Productivity
        
        Using Absorption and Contribution Costing
        
        Understanding Absorption Costing
        
        Understanding Contribution Costing
        
        Allocating Expenses to Product Lines
        
        Varying the Inputs
        
        Show and hide more
        Product information
        
        Title: Business Analysis with Microsoft Excel, Fifth Edition
        
        Author(s): Conrad Carlberg
        
        Release date: December 2018
        
        Publisher(s): Que
        
        ISBN: 9780134862538