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 StatementsKeeping ScoreChoosing the Right PerspectiveDefining Two Purposes for AccountingUsing the Income StatementChoosing a Reporting MethodCells in ExcelMeasuring the Operating and Nonoperating SegmentsMoving from the General Journal to the Income StatementGetting the General Journal into ExcelUnderstanding Absolute, Relative, and Mixed ReferencesGetting the Journal Data to the LedgerGetting the Ledger Data to the Income StatementManaging the Financial Analyses with Accrual AccountingUsing Straight-Line DepreciationPreparing the Trial BalanceMoving Information into an Income StatementOrganizing with Traditional Versus Contribution ApproachesAbout Power BIPower BI DesktopPower BI Service and Mobile AppsSummaryChapter 2 Balance Sheet: Current AssetsDesigning the Balance SheetUnderstanding Balance Sheet AccountsUnderstanding Debit and Credit EntriesGetting a Current Asset Cash BalanceUsing Sheet-Level NamesGetting a Cash Balance for Multiple Cash AccountsHandling Restricted Cash AccountsGetting a Current Asset Accounts Receivable BalanceAllowing for Doubtful AccountsUsing the Aging Approach to Estimating UncollectiblesUsing the Percentage of Sales Approach to Estimating UncollectiblesDisplaying Doubtful Account Balances with Power BIManaging the Sort Order Via the Axis ValuesManaging the Sort Order with Another FieldGetting a Prepaid Expenses BalanceDealing with Insurance as a Prepaid ExpenseGetting a Current Asset BalanceUnderstanding the Inventory FlowClosing the Inventory AccountClosing the Revenue and Expense AccountsSummaryChapter 3 Valuing Inventories for the Balance SheetUnderstanding 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 FIFOUsing LIFOComparing 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 ActivitiesCombining Cash from Operations with Cash from Nonoperating TransactionsSummary 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 ProjectionsMaking 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 SmoothingUsing 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 CorrelogramsStarting 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 QualityMonitoring 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 UnitsUsing the BINOM.INV FunctionSummaryChapter 11 Examining a Business Case: InvestmentDeveloping 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 ValueCalculating 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 FunctionCreating 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 CellsManaging Security Information Arranging Automatic Refreshes Setting Other Data Range Options Importing Data to Pivot Tables and ChartsCreating 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 ExcelUsing VBA to Update an External DatabaseGetting at VBA Structuring the WorksheetEstablishing 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 BIPreparing 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 PointChoosing 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