Business Analysis with Microsoft Excel, Fifth Edition

Business Analysis with Microsoft Excel, Fifth Edition

by Conrad Carlberg
Released December 2018
Publisher(s): Que
ISBN: 9780134862538

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.

Start your free trial

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

Book: Microsoft Excel Data Analysis and Business Modeling (Office 2021 and Microsoft 365), 7th Edition
Video: Data Analysis Fundamentals with Excel (Video), Introduction to Microsoft Power BI (Video)

 

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

Table of contents

  1. Cover Page
  2. About This E-Book
  3. Title Page
  4. Copyright Page
  5. Contents at a Glance
  6. Contents
  7. About the Author
  8. Dedication
  9. We Want to Hear from You!
  10. Reader Services
  11. 1 Working with Income Statements
    1. Keeping Score
      1. Choosing the Right Perspective
      2. Defining Two Purposes for Accounting
    2. Using the Income Statement
      1. Choosing a Reporting Method
      2. Cells in Excel
      3. Measuring the Operating and Nonoperating Segments
    3. Moving from the General Journal to the Income Statement
      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
    4. Managing the Financial Analyses with Accrual Accounting
      1. Using Straight-Line Depreciation
      2. Preparing the Trial Balance
      3. Moving Information into an Income Statement
    5. Organizing with Traditional Versus Contribution Approaches
    6. About Power BI
      1. Power BI Desktop
      2. Power BI Service and Mobile Apps
    7. Summary
  12. 2 Balance Sheet: Current Assets
    1. Designing the Balance Sheet
      1. Understanding Balance Sheet Accounts
      2. Understanding Debit and Credit Entries
    2. Getting a Current Asset Cash Balance
      1. Using Sheet-Level Names
      2. Getting a Cash Balance for Multiple Cash Accounts
      3. Handling Restricted Cash Accounts
    3. Getting a Current Asset Accounts Receivable Balance
      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
    4. Getting a Prepaid Expenses Balance
      1. Dealing with Insurance as a Prepaid Expense
    5. Getting a Current Asset Balance
      1. Understanding the Inventory Flow
      2. Closing the Inventory Account
      3. Closing the Revenue and Expense Accounts
    6. Summary
  13. 3 Valuing Inventories for the Balance Sheet
    1. Understanding Perpetual and Periodic Inventory Systems
      1. Perpetual Inventory Systems
      2. Periodic Inventory Systems
    2. Valuing Inventories
      1. Valuation Methods Summarized
      2. Using Specific Identification
      3. Using Average Cost
      4. Using the Moving Average Method
      5. Using FIFO
      6. Using LIFO
    3. Comparing the Four Valuation Methods
      1. Specification Identification
      2. Average Cost
      3. FIFO
      4. LIFO
    4. Handling Purchase Discounts
    5. Calculating Turns Ratios
    6. Summary
  14. 4 Summarizing Transactions: From the Journals to the Balance Sheet
    1. Understanding Journals
      1. Understanding Special Journals
      2. Structuring the Special Sales Journal
      3. Structuring the Special Purchases Journal
      4. Structuring the Cash Receipts Journal
      5. Structuring the Cash Payments Journal
    2. Excel Tables and Dynamic Range Names
      1. Building Dynamic Range Names
      2. Using Dynamic Range Names in the Journals
      3. Choosing Between Tables and Dynamic Range Names
    3. Understanding Ledgers
      1. Creating the General Ledger
      2. Using Subsidiary Ledgers
      3. Automating the Posting Process
    4. Getting a Current Liabilities Balance
    5. Summary
  15. 5 Working Capital and Cash Flow Analysis
    1. Matching Costs and Revenues
    2. Broadening the Definition: Cash Versus Working Capital
      1. Determining the Amount of Working Capital
      2. Determining Changes in Working Capital
    3. Analyzing Cash Flow
      1. Developing the Basic Information
      2. Summarizing the Sources and Uses of Working Capital
      3. Identifying Cash Flows Due to Operating Activities
      4. Combining Cash from Operations with Cash from Nonoperating Transactions
    4. Summary
  16. 6 Statement Analysis
    1. Understanding a Report by Means of Common-Sizing
      1. Using Common-Sized Income Statements
      2. Using Common-Sized Balance Sheets
      3. Using Comparative Financial Statements
    2. Using Dollar and Percent Changes in Statement Analysis
      1. Assessing the Financial Statements
      2. Handling Error Values
      3. Evaluating Percentage Changes
    3. Common-Sizing for Variance Analysis
    4. Common-Sizing by Headcount
    5. Showing Common-Sized Statements with Power BI
    6. Summary
  17. 7 Ratio Analysis
    1. Interpreting Industry Averages and Trends
    2. Comparing Ratios Within Industries
      1. Analyzing Ratios Vertically and Horizontally
      2. Getting a Basis for Ratios
    3. Analyzing Profitability Ratios
      1. Finding and Evaluating Earnings Per Share
      2. Determining Gross Profit Margin
      3. Determining Net Profit Margin
      4. Determining the Return on Assets
      5. Determining the Return on Equity
    4. Analyzing Leverage Ratios
      1. Determining the Debt Ratio
      2. Determining the Equity Ratio
      3. Determining the Times Interest Earned Ratio
    5. Analyzing Liquidity Ratios
      1. Determining the Current Ratio
      2. Determining the Quick Ratio
    6. Analyzing Activity Ratios
      1. Determining the Average Collection Period
      2. Determining Inventory Turnover
    7. Displaying Financial Ratios in Power BI Reports
    8. Summary
  18. 8 Budgeting and Planning Cycle
    1. Creating Pro Forma Financial Statements
      1. Forecasting by Percentage of Sales
    2. Using Excel to Manage the Analysis
      1. Performing Sensitivity Analysis
    3. Moving from the Pro Forma to the Budget
      1. Projecting Quarterly Sales
      2. Estimating Inventory Levels
      3. Fitting the Budget to the Business Plan
    4. Summary
  19. 9 Forecasting and Projections
    1. Making Sure You Have a Useful Baseline
    2. Moving Average Forecasts
      1. Creating Forecasts with the Moving Average Add-In
      2. Dealing with the Layout of Excel’s Moving Averages
      3. Creating Moving Average Forecasts with Excel’s Charts
    3. Forecasting with Excel’s Regression Functions
      1. Making Linear Forecasts: The TREND Function
      2. Making Nonlinear Forecasts: The GROWTH Function
      3. Creating Regression Forecasts with Excel’s Charts
    4. Forecasting with Excel’s Smoothing Functions
      1. Projecting with Smoothing
      2. Using the Exponential Smoothing Tool
      3. Choosing a Smoothing Constant
      4. Making Smoothed Forecasts Handle Seasonal Data
    5. Using the Box-Jenkins ARIMA Approach: When Excel’s Built-In Functions Won’t Do
      1. Understanding ARIMA Basics
      2. Charting the Correlograms
      3. Starting with Correlograms to Identify a Model
      4. Identifying Other Box-Jenkins Models
    6. Displaying Forecast Data with Power BI
      1. Displaying Forecasts with Power BI
      2. Using Power BI to Display Correlograms
    7. Summary
  20. 10 Measuring Quality
    1. Monitoring Quality Through Statistical Process Control
      1. Using Averages from Samples
      2. Using X-and-S Charts for Variables
      3. Interpreting the Control Limits
      4. Manufacturing
      5. Publishing Control Charts with Power BI
      6. Using P-Charts for Dichotomies
      7. Choosing the Sample Size
      8. Determining That a Process Is Out of Control
      9. Using X-and-MR Charts for Individual Observations
      10. Creating SPC Charts Using Excel
    2. Performing Acceptance Sampling
      1. Charting the Operating Characteristic Curve
    3. Using Worksheet Functions for Quality Control
    4. Sampling Units from a Finite Population
    5. Sampling Units from a Nonfinite Population
      1. Using NORM.S.DIST to Approximate BINOM.DIST
      2. Sampling Defects in Units
      3. Using the BINOM.INV Function
    6. Summary
  21. 11 Examining a Business Case: Investment
    1. Developing a Business Case
      1. Getting Consensus for the Plan
      2. Showing Your Work
    2. Developing the Excel Model
      1. Developing the Inputs
      2. Identifying the Costs
      3. Moving to the Pro Forma
      4. Preparing the Cash Flow Analysis
    3. Summary
  22. 12 Examining Decision Criteria for a Business Case
    1. Understanding Payback Periods
    2. Understanding Future Value, Present Value, and Net Present Value
      1. Calculating Future Value
      2. Calculating Present Value
      3. Calculating Net Present Value
      4. Optimizing Costs
    3. Summary
  23. 13 Creating a Sensitivity Analysis for a Business Case
    1. Reviewing the Business Case
    2. Managing Scenarios
      1. Saving a Scenario for the Base Case
      2. Developing Alternative Scenarios
      3. Developing Scenarios That Vary Expenses
      4. Summarizing the Scenarios
    3. Measuring Profit
      1. Calculating Internal Rate of Return
      2. Calculating Profitability Indexes
      3. Estimating the Continuing Value
    4. Varying the Discount Rate Input
    5. Using the Goal Seek Tool
    6. Summary
  24. 14 Planning Profits
    1. Understanding the Effects of Leverage
      1. The Effect of Business Risk
    2. Analyzing Operating Leverage
      1. Evaluating the Financial Implications of an Operational Change
      2. Evaluating Fixed Expenses
      3. Evaluating Effect of Increasing Fixed Costs
      4. Planning by Using the DOL
    3. Analyzing Financial Leverage
      1. Distinguishing Business from Financial Risk
      2. Determining the Debt Ratio
      3. Determining the Times Interest Earned Ratio
    4. Summary
  25. 15 Making Investment Decisions Under Uncertain Conditions
    1. Using Standard Deviations
      1. Using Excel’s Standard Deviation Functions
    2. Understanding Confidence Intervals
      1. Using Confidence Intervals in a Market Research Situation
      2. Calculating a Confidence Interval
      3. Interpreting the Interval
      4. Refining Confidence Intervals
    3. Using Regression Analysis in Decision Making
      1. Regressing One Variable onto Another
      2. Interpreting the Trendline
      3. Avoiding Traps in Interpretation: Association Versus Causation
      4. Regressing One Variable onto Several Other Variables: Multiple Regression
      5. Using Excel’s Regression Add-In
      6. Interpreting Regression Output
      7. Estimating with Multiple Regression
      8. Using Excel’s TREND Function
    4. Creating Charts in Power BI
      1. Creating a Scatter Chart
      2. Creating a Clustered Column Chart in Power BI
    5. Summary
  26. 16 Fixed Assets
    1. Determining Original Cost
      1. Determining Costs
      2. Choosing Between Actual Cost and Replacement Cost
    2. Depreciating Assets
      1. Understanding the Concept of Depreciation
      2. Matching Revenues to Costs
      3. Using Straight-Line Depreciation
      4. Using the Declining Balance Method
      5. Using the Double Declining Balance Function to Calculate Depreciation
      6. Using Variable Declining Balance Depreciation
      7. Using Sum-of-Years’-Digits Depreciation
    3. Summary
  27. 17 Importing Business Data into Excel
    1. Creating and Using ODBC Queries
      1. Preparing to Import Data
      2. Specifying Data Sources
      3. Creating Queries with the Query Wizard
      4. Creating Queries with Microsoft Query
      5. Creating Parameterized Queries in Microsoft Query
      6. Using Joins in Microsoft Query
    2. Working with External Data Ranges
      1. Include Row Numbers
      2. Adjust Column Width
      3. Preserve Column Sort/Filter/Layout
      4. Preserve Cell Formatting
      5. Insert Cells for New Data, Delete Unused Cells
      6. Insert Entire Rows for New Data, Clear Unused Cells
      7. Overwrite Existing Cells with New Data, Clear Unused Cells
      8. Managing Security Information
      9. Arranging Automatic Refreshes
      10. Setting Other Data Range Options
      11. Importing Data to Pivot Tables and Charts
    3. Creating and Using Web Queries
      1. Get External Data and Get Data From Web
      2. Using Get Data with a Website
      3. Using Get External Data and VBA
    4. Summary
  28. 18 Exporting Business Data from Excel
    1. Using VBA to Update an External Database
      1. Getting at VBA
      2. Structuring the Worksheet
      3. Establishing Command Buttons
    2. Editing the Record’s Values
      1. Using Database Objects
    3. Using With Blocks
      1. Finding the Right Record
      2. Editing the Record
    4. Adding New Records to the Recordset
    5. Choosing to Use ADO
    6. Summary
  29. 19 Using Excel 2016 and Power BI to Analyze QuickBooks Data
    1. Exporting an Income Statement to Excel
    2. Publishing a QuickBooks Report in Power BI
      1. Preparing the Data in Excel
      2. Moving the Report to Power BI
    3. Using the QuickBooks Software Development Kit
      1. Parsing a Simple Subroutine
      2. Invoking QBFC
      3. Identifying the Sources of the Variables
      4. Understanding then Rationale
    4. Running the Assembly Tracker
      1. Opening the QuickBooks Company File
      2. Opening the Excel File
      3. Allowing Access to QuickBooks Data
  30. 20 Analyzing Contributions and Margins
    1. Calculating the Contribution Margin
      1. Classifying Costs
      2. Estimating Semivariable Costs
    2. Using Unit Contribution
      1. Producing Digital Video Discs (Continued)
      2. Increasing the Contribution Margin
      3. Creating an Operating Income Statement
    3. Finding the Break-Even Point
      1. Calculating Breakeven in Units
      2. Calculating Breakeven in Sales
      3. Calculating Breakeven in Sales Dollars with a Specified Level of Profit
      4. Charting the Break-Even Point
      5. Choosing the Chart Type
    4. Displaying a Break-Even Chart in Power BI
    5. Making Assumptions in Contribution Analysis
      1. Linear Relationships
      2. Assignment of Costs
      3. Constant Sales Mix
      4. Worker Productivity
    6. Determining Sales Mix
    7. Summary
  31. 21 Pricing and Costing
    1. Using Absorption and Contribution Costing
      1. Understanding Absorption Costing
      2. Understanding Contribution Costing
    2. Applying the Contribution Approach to a Pricing Decision: Goal Seek
    3. Applying the Contribution Approach to a Pricing Decision: Solver
    4. Using Contribution Analysis for New Products
      1. Allocating Expenses to Product Lines
      2. Varying the Inputs
    5. Estimating the Effect of Cross-Elasticity
    6. Summary
  32. Index

Product information

  • Title: Business Analysis with Microsoft Excel, Fifth Edition
  • Author(s): Conrad Carlberg
  • Release date: December 2018
  • Publisher(s): Que
  • ISBN: 9780134862538