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