Business Analysis with Microsoft Excel

Conrad G. Carlberg is president of Network Control Systems, Inc., a software- development and consulting firm that specializes in statistical and database applications. He holds a PhD in statistics and is a many-time recipient of Microsoft's Most Valuable Professional (MVP) award. He lives near San Diego, California.

Chapter 1 Working with Income Statements

Keeping Score

Choosing the Right Perspective

Defining Two Purposes for Accounting

Using the Income Statement

Choosing a Reporting Method

Cells in Excel

Measuring the Operating and Nonoperating Segments

Moving from the General Journal to the Income Statement

Getting the General Journal into Excel

Understanding Absolute, Relative, and Mixed References

Getting the Journal Data to the Ledger

Getting the Ledger Data to the Income Statement

Managing the Financial Analyses with Accrual Accounting

Using Straight-Line Depreciation

Preparing the Trial Balance

Moving Information into an Income Statement

Organizing with Traditional Versus Contribution Approaches

About Power BI

Power BI Desktop

Power BI Service and Mobile Apps

Summary

Chapter 2 Balance Sheet: Current Assets

Designing the Balance Sheet

Understanding Balance Sheet Accounts

Understanding Debit and Credit Entries

Getting a Current Asset Cash Balance

Using Sheet-Level Names

Getting a Cash Balance for Multiple Cash Accounts

Handling Restricted Cash Accounts

Getting a Current Asset Accounts Receivable Balance

Allowing for Doubtful Accounts

Using the Aging Approach to Estimating Uncollectibles

Using the Percentage of Sales Approach to Estimating Uncollectibles

Displaying Doubtful Account Balances with Power BI

Managing the Sort Order Via the Axis Values

Managing the Sort Order with Another Field

Getting a Prepaid Expenses Balance

Dealing with Insurance as a Prepaid Expense

Getting a Current Asset Balance

Understanding the Inventory Flow

Closing the Inventory Account

Closing the Revenue and Expense Accounts

Summary

Chapter 3 Valuing Inventories for the Balance Sheet

Understanding Perpetual and Periodic Inventory Systems

Perpetual Inventory Systems

Periodic Inventory Systems

Valuing Inventories

Valuation Methods Summarized

Using Specific Identification

Using Average Cost

Using the Moving Average Method

Using FIFO

Using LIFO

Comparing the Four Valuation Methods

Specification Identification

Average Cost

FIFO

LIFO

Handling Purchase Discounts

Calculating Turns Ratios

Summary

Chapter 4 Summarizing Transactions: From the Journals to the Balance Sheet

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

Excel Tables and Dynamic Range Names

Building Dynamic Range Names

Using Dynamic Range Names in the Journals

Choosing Between Tables and Dynamic Range Names

Understanding Ledgers

Creating the General Ledger

Using Subsidiary Ledgers

Automating the Posting Process

Getting a Current Liabilities Balance

Summary

Chapter 5 Working Capital and Cash Flow Analysis

Matching Costs and Revenues

Broadening the Definition: Cash Versus Working Capital

Determining the Amount of Working Capital

Determining Changes in Working Capital

Analyzing Cash Flow

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

Summary

Chapter 6 Statement Analysis

Understanding a Report by Means of Common-Sizing

Using Common-Sized Income Statements

Using Common-Sized Balance Sheets

Using Comparative Financial Statements

Using Dollar and Percent Changes in Statement Analysis

Assessing the Financial Statements

Handling Error Values

Evaluating Percentage Changes

Common-Sizing for Variance Analysis

Common-Sizing by Headcount

Showing Common-Sized Statements with Power BI

Summary

Chapter 7 Ratio Analysis

Interpreting Industry Averages and Trends

Comparing Ratios Within Industries

Analyzing Ratios Vertically and Horizontally

Getting a Basis for Ratios

Analyzing Profitability 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

Analyzing Leverage Ratios

Determining the Debt Ratio

Determining the Equity Ratio

Determining the Times Interest Earned Ratio

Analyzing Liquidity Ratios

Determining the Current Ratio

Determining the Quick Ratio

Analyzing Activity Ratios

Determining the Average Collection Period

Determining Inventory Turnover

Displaying Financial Ratios in Power BI Reports

Summary

Chapter 8 Budgeting and Planning Cycle

Creating Pro Forma Financial Statements

Forecasting by Percentage of Sales

Using Excel to Manage the Analysis

Performing Sensitivity Analysis

Moving from the Pro Forma to the Budget

Projecting Quarterly Sales

Estimating Inventory Levels

Fitting the Budget to the Business Plan

Summary

Chapter 9 Forecasting and Projections

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

Forecasting with Excel's Regression Functions

Making Linear Forecasts: The TREND Function

Making Nonlinear Forecasts: The GROWTH Function

Creating Regression Forecasts with Excel's Charts

Forecasting with Excel's Smoothing Functions

Projecting with Smoothing

Using the Exponential Smoothing Tool

Choosing a Smoothing Constant

Making Smoothed Forecasts Handle Seasonal Data

Using the Box-Jenkins ARIMA Approach: When Excel's Built-In Functions Won't Do

Understanding ARIMA Basics

Charting the Correlograms

Starting with Correlograms to Identify a Model

Identifying Other Box-Jenkins Models

Displaying Forecast Data with Power BI

Displaying Forecasts with Power BI

Using Power BI to Display Correlograms

Summary

Chapter 10 Measuring Quality

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

Performing Acceptance Sampling

Charting the Operating Characteristic Curve

Using Worksheet Functions for Quality Control

Sampling Units from a Finite Population

Sampling Units from a Nonfinite Population

Using NORM.S.DIST to Approximate BINOM.DIST

Sampling Defects in Units

Using the BINOM.INV Function

Summary

Chapter 11 Examining a Business Case: Investment

Developing a Business Case

Getting Consensus for the Plan

Showing Your Work

Developing the Excel Model

Developing the Inputs

Identifying the Costs

Moving to the Pro Forma

Preparing the Cash Flow Analysis

Summary

Chapter 12 Examining Decision Criteria for a Business Case

Understanding Payback Periods

Understanding Future Value, Present Value, and Net Present Value

Calculating Future Value

Calculating Present Value

Calculating Net Present Value

Optimizing Costs

Summary

Chapter 13 Creating a Sensitivity Analysis for a Business Case

Reviewing the Business Case

Managing Scenarios

Saving a Scenario for the Base Case

Developing Alternative Scenarios

Developing Scenarios That Vary Expenses

Summarizing the Scenarios

Measuring Profit

Calculating Internal Rate of Return

Calculating Profitability Indexes

Estimating the Continuing Value

Varying the Discount Rate Input

Using the Goal Seek Tool

Summary

Chapter 14 Planning Profits

Understanding the Effects of Leverage

The Effect of Business Risk

Analyzing Operating Leverage

Evaluating the Financial Implications of an Operational Change

Evaluating Fixed Expenses

Evaluating Effect of Increasing Fixed Costs

Planning by Using the DOL

Analyzing Financial Leverage

Distinguishing Business from Financial Risk

Determining the Debt Ratio

Determining the Times Interest Earned Ratio

Summary

Chapter 15 Making Investment Decisions Under Uncertain Conditions

Using Standard Deviations

Using Excel's Standard Deviation Functions

Understanding Confidence Intervals

Using Confidence Intervals in a Market Research Situation

Calculating a Confidence Interval

Interpreting the Interval

Refining Confidence Intervals

Using Regression Analysis in Decision Making

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 Charts in Power BI

Creating a Scatter Chart

Creating a Clustered Column Chart in Power BI

Summary

Chapter 16 Fixed Assets

Determining Original Cost

Determining Costs

Choosing Between Actual Cost and Replacement Cost

Depreciating Assets

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

Summary

Chapter 17 Importing Business Data into Excel

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

Working with External Data Ranges

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

Creating and Using Web Queries

Get External Data and Get Data From Web

Using Get Data with a Website

Using Get External Data and VBA

Summary

Chapter 18 Exporting Business Data from Excel

Using VBA to Update an External Database

Getting at VBA

Structuring the Worksheet

Establishing Command Buttons

Editing the Record's Values

Using Database Objects

Using With Blocks

Finding the Right Record

Editing the Record

Adding New Records to the Recordset

Choosing to Use ADO

Summary

Chapter 19 Using Excel 2016 and Power BI to Analyze QuickBooks Data

Exporting an Income Statement to Excel

Publishing a QuickBooks Report in Power BI

Preparing the Data in Excel

Moving the Report to Power BI

Using the QuickBooks Software Development Kit

Parsing a Simple Subroutine

Invoking QBFC

Identifying the Sources of the Variables

Understanding then Rationale

Running the Assembly Tracker

Opening the QuickBooks Company File

Opening the Excel File

Allowing Access to QuickBooks Data

Chapter 20 Analyzing Contributions and Margins

Calculating the Contribution Margin

Classifying Costs

Estimating Semivariable Costs

Using Unit Contribution

Producing Digital Video Discs (Continued)

Increasing the Contribution Margin

Creating an Operating Income Statement

Finding the Break-Even Point

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

Displaying a Break-Even Chart in Power BI

Making Assumptions in Contribution Analysis

Linear Relationships

Assignment of Costs

Constant Sales Mix

Worker Productivity

Determining Sales Mix

Summary

Chapter 21 Pricing and Costing

Using Absorption and Contribution Costing

Understanding Absorption Costing

Understanding Contribution Costing

Applying the Contribution Approach to a Pricing Decision: Goal Seek

Applying the Contribution Approach to a Pricing Decision: Solver

Using Contribution Analysis for New Products

Allocating Expenses to Product Lines

Varying the Inputs

Estimating the Effect of Cross-Elasticity

Summary





9780789759580 TOC 11/12/2018