Business Analysis with Microsoft Excel

by Conrad Carlberg

Overview

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!

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

Product Details

ISBN-13:	9780789759580
Publisher:	Pearson Education
Publication date:	03/22/2019
Pages:	576
Sales rank:	1,067,249
Product dimensions:	7.00(w) x 9.00(h) x 1.20(d)

About the Author

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.

Table of Contents

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

Editorial Reviews

The Barnes & Noble Review
A blank spreadsheet is like a chunk of granite: It can be sculpted into just about anything you desire. But if you want to use Excel for business analysis -- for maximizing profit -- it's not easy to find specific, real-world guidance. Fortunately, you just did.

Business Analysis with Microsoft Excel, Second Edition walks you start-to-finish through building the spreadsheets you really need as a businessperson. Better yet, it's supported by dozens of downloadable spreadsheets, making it supremely useful from the get-go.

Let's say you need to value your inventories, but you've purchased the same item at different times and at different prices. Conrad Carlberg shows you how to handle that. Need to develop a business case for a new product or service? Carlberg demonstrates how --- and also shows you how to introduce sensitivity analyses that handle the uncertainties the world might just throw at you.

You'll find easy-to-understand explanations of forecasting with Excel: starting from a useful baseline, working with moving averages, and using Excel's regression and smoothing functions, which Carlberg makes a whole lot easier than you might expect. There's even a taste of more advanced techniques, notably the Box-Jenkins ARIMA Approach, which gives you greater scope and flexibility than Excel's built-in functions.

Carlberg presents a full chapter on making pricing decisions with Excel: using absorption and contribution costing, working with contribution analysis (a tricky, oft-misunderstood technique), and estimating the effects of cross-elasticity -- the tendency of products to draw sales from one another.

If you simply want to learn Excel, there are plenty of excellent books. But if you want to use Excel to solve specific business problems, this is the one to choose. (Bill Camarda)

Bill Camarda is a consultant, writer, and web/multimedia content developer with nearly 20 years' experience in helping technology companies deploy and market advanced software, computing, and networking products and services. His 15 books include Special Edition Using Word 2000 and Upgrading & Fixing Networks For Dummies®, Second Edition.

bn.com

Carlberg (Ph.D., statistics), the president of a software and consulting firm that specializes in developing quality control and forecasting tools, distinguishes his book from others on Excel by its bottom line orientation. In coverage of financial statements and analysis, financial planning and control, investment decisions, and sales and marketing, he stresses making worksheets self- documenting for interpretation by others. Includes case studies, tips, a glossary, and auxiliary website. No date is given for the first edition. Annotation c. Book News, Inc., Portland, OR (booknews.com)

Booknews