Business Analysis with Microsoft Excel, 5th edition
Published by Que Publishing (March 22, 2019) © 2019
- Conrad Carlberg
eTextbook
- Available for purchase from all major ebook resellers, including InformIT.com.
- To request a review copy, click on the "Request a Review Copy" button.
- A print text (hardcover or paperback)Â
- Free shipping
- Also available for purchase as an ebook from all major ebook resellers, including InformIT.com
Business analysis expert Conrad Carlberg shows readers 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 readers 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.
- Detailed workbooks for every chapter as well as standalone workbooks that illustrate key business tasks
- Power BI examples will illustrate the underlying data source, query results, and visual representations as rendered by Power BI Desktop or Power BI Premium Service
- Shows how to use Excel to take total control over company finances
- Covers everything from financial statements and marketing to investment decision-making and quality management, using real-world examples
The last edition was written for Excel 2010. In this proposed edition, we are not only updating the content for Excel 2019 but we are including Power BI content where applicable.
Introduction: new section introducing Power BI
New Chapter 2, "Power BI: An Overview"
Power BI Desktop Loading a Data Source
 Creating and Editing Queries Building a Report
 Publishing a Report to Power BI ServicePower BI Service
 Starting from Power BI Service Arranging the Correct Format and Properties
 Creating the Report Ensuring Automatic Updates
Using BI to Structure a Dashboard with Different Income Statement Formats  Reporting in a Traditional Format
  Reporting to Detail Inventory Management  Reporting to Focus on Managing COGS
  Reporting to Emphasize Management of CapitalChapter 4, "Valuing Inventories for the Balance Sheet"
--New subheaded section titled "Creating a Dashboard to Display Valuation Methods"--New section on adding Inventory Ratios to the Power BI Dashboard
Chapter 6, "Working Capital and Cash Flow Analysis"
--New Power BI coverage of structuring queries (3 different types) and designing a working capital dashboard
Chapter 7, "Statement Analysis"--New content on using Power BI to post comparative financial statements
Chapter 8, "Ratio Analysis"
--New Power BI content on arranging ratio dashboards
Chapter 10, "Forecasts and Projections" -- new coverage of how to manage problems with the Forecast Sheet
--new subheaded section on how to publish forecasts with Reports and Dashboards
Chapter 11 Measuring Quality-- new subhead section titled, "Displaying Control Charts on a Dashboard"
-- new coverage of how to add the Operating Characteristic curve to the Dashboard
Part IV renamed to Sales and Production Analysis
New Chapter 20, Using Excel 2016 and Power BI to Analyze QuickBooks Data
Moving QuickBooks Reports to Excel Exporting from the QuickBooks Interface
 Importing Existing Reports with VBA
Using the QuickBooks Application Programming Interface
Getting and Installing the Query Library
Implementing the Queries in VBAÂ Citing the Correct Version in the Call
 Structuring the Procedures
A Full Example: Valuing Inventory Using FIFOÂ Better Phone Your Accountant
  What the Code Does  Financial Impact of the Cost of Inventory
  Invoking the Queries  Using the Results
Chapter 21, "Analyzing Contributions and Margins"
--New subheaded section on designing a break-even dashboard
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Â
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.
Need help? Get in touch