Business Analysis with Microsoft Excel and Power BI

About This E-Book
Title Page
Copyright Page
Contents at a Glance
Contents
About the Author
Dedication
We Want to Hear from You!
Reader Services
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Index