book

Business Analysis with Microsoft Excel, Fifth Edition

Name: Business Analysis with Microsoft Excel, Fifth Edition
Author: Conrad Carlberg
ISBN: 9780134862538

by Conrad Carlberg

December 2018

Beginner to intermediate

576 pages

20h 23m

English

Que

Read now

Unlock full access

Cover Page
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 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 AppsSummary
2 Balance Sheet: Current Assets
Designing 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 AccountsSummary
3 Valuing Inventories for the Balance Sheet
Understanding Perpetual and Periodic Inventory SystemsPerpetual Inventory SystemsPeriodic Inventory SystemsValuing InventoriesValuation Methods SummarizedUsing Specific IdentificationUsing Average CostUsing the Moving Average MethodUsing FIFOUsing LIFOComparing the Four Valuation MethodsSpecification IdentificationAverage CostFIFOLIFOHandling Purchase DiscountsCalculating Turns RatiosSummary
4 Summarizing Transactions: From the Journals to the Balance Sheet
Understanding JournalsUnderstanding Special JournalsStructuring the Special Sales JournalStructuring the Special Purchases JournalStructuring the Cash Receipts JournalStructuring the Cash Payments JournalExcel Tables and Dynamic Range NamesBuilding Dynamic Range NamesUsing Dynamic Range Names in the JournalsChoosing Between Tables and Dynamic Range NamesUnderstanding LedgersCreating the General LedgerUsing Subsidiary LedgersAutomating the Posting ProcessGetting a Current Liabilities BalanceSummary
5 Working Capital and Cash Flow Analysis
Matching Costs and RevenuesBroadening the Definition: Cash Versus Working CapitalDetermining the Amount of Working CapitalDetermining Changes in Working CapitalAnalyzing Cash FlowDeveloping the Basic InformationSummarizing the Sources and Uses of Working CapitalIdentifying Cash Flows Due to Operating ActivitiesCombining Cash from Operations with Cash from Nonoperating TransactionsSummary
6 Statement Analysis
Understanding a Report by Means of Common-SizingUsing Common-Sized Income StatementsUsing Common-Sized Balance SheetsUsing Comparative Financial StatementsUsing Dollar and Percent Changes in Statement AnalysisAssessing the Financial StatementsHandling Error ValuesEvaluating Percentage ChangesCommon-Sizing for Variance AnalysisCommon-Sizing by HeadcountShowing Common-Sized Statements with Power BISummary
7 Ratio Analysis
Interpreting Industry Averages and TrendsComparing Ratios Within IndustriesAnalyzing Ratios Vertically and HorizontallyGetting a Basis for RatiosAnalyzing Profitability RatiosFinding and Evaluating Earnings Per ShareDetermining Gross Profit MarginDetermining Net Profit MarginDetermining the Return on AssetsDetermining the Return on EquityAnalyzing Leverage RatiosDetermining the Debt RatioDetermining the Equity RatioDetermining the Times Interest Earned RatioAnalyzing Liquidity RatiosDetermining the Current RatioDetermining the Quick RatioAnalyzing Activity RatiosDetermining the Average Collection PeriodDetermining Inventory TurnoverDisplaying Financial Ratios in Power BI ReportsSummary
8 Budgeting and Planning Cycle
Creating Pro Forma Financial StatementsForecasting by Percentage of SalesUsing Excel to Manage the AnalysisPerforming Sensitivity AnalysisMoving from the Pro Forma to the BudgetProjecting Quarterly SalesEstimating Inventory LevelsFitting the Budget to the Business PlanSummary
9 Forecasting and Projections
Making Sure You Have a Useful BaselineMoving Average ForecastsCreating Forecasts with the Moving Average Add-InDealing with the Layout of Excel’s Moving AveragesCreating Moving Average Forecasts with Excel’s ChartsForecasting with Excel’s Regression FunctionsMaking Linear Forecasts: The TREND FunctionMaking Nonlinear Forecasts: The GROWTH FunctionCreating Regression Forecasts with Excel’s ChartsForecasting with Excel’s Smoothing FunctionsProjecting with SmoothingUsing the Exponential Smoothing ToolChoosing a Smoothing ConstantMaking Smoothed Forecasts Handle Seasonal DataUsing the Box-Jenkins ARIMA Approach: When Excel’s Built-In Functions Won’t DoUnderstanding ARIMA BasicsCharting the CorrelogramsStarting with Correlograms to Identify a ModelIdentifying Other Box-Jenkins ModelsDisplaying Forecast Data with Power BIDisplaying Forecasts with Power BIUsing Power BI to Display CorrelogramsSummary
10 Measuring Quality
Monitoring Quality Through Statistical Process ControlUsing Averages from SamplesUsing X-and-S Charts for VariablesInterpreting the Control LimitsManufacturingPublishing Control Charts with Power BIUsing P-Charts for DichotomiesChoosing the Sample SizeDetermining That a Process Is Out of ControlUsing X-and-MR Charts for Individual ObservationsCreating SPC Charts Using ExcelPerforming Acceptance SamplingCharting the Operating Characteristic CurveUsing Worksheet Functions for Quality ControlSampling Units from a Finite PopulationSampling Units from a Nonfinite PopulationUsing NORM.S.DIST to Approximate BINOM.DISTSampling Defects in UnitsUsing the BINOM.INV FunctionSummary
11 Examining a Business Case: Investment
Developing a Business CaseGetting Consensus for the PlanShowing Your WorkDeveloping the Excel ModelDeveloping the InputsIdentifying the CostsMoving to the Pro FormaPreparing the Cash Flow AnalysisSummary
12 Examining Decision Criteria for a Business Case
Understanding Payback PeriodsUnderstanding Future Value, Present Value, and Net Present ValueCalculating Future ValueCalculating Present ValueCalculating Net Present ValueOptimizing CostsSummary
13 Creating a Sensitivity Analysis for a Business Case
Reviewing the Business CaseManaging ScenariosSaving a Scenario for the Base CaseDeveloping Alternative ScenariosDeveloping Scenarios That Vary ExpensesSummarizing the ScenariosMeasuring ProfitCalculating Internal Rate of ReturnCalculating Profitability IndexesEstimating the Continuing ValueVarying the Discount Rate InputUsing the Goal Seek ToolSummary
14 Planning Profits
Understanding the Effects of LeverageThe Effect of Business RiskAnalyzing Operating LeverageEvaluating the Financial Implications of an Operational ChangeEvaluating Fixed ExpensesEvaluating Effect of Increasing Fixed CostsPlanning by Using the DOLAnalyzing Financial LeverageDistinguishing Business from Financial RiskDetermining the Debt RatioDetermining the Times Interest Earned RatioSummary
15 Making Investment Decisions Under Uncertain Conditions
Using Standard DeviationsUsing Excel’s Standard Deviation FunctionsUnderstanding Confidence IntervalsUsing Confidence Intervals in a Market Research SituationCalculating a Confidence IntervalInterpreting the IntervalRefining Confidence IntervalsUsing Regression Analysis in Decision MakingRegressing One Variable onto AnotherInterpreting the TrendlineAvoiding Traps in Interpretation: Association Versus CausationRegressing One Variable onto Several Other Variables: Multiple RegressionUsing Excel’s Regression Add-InInterpreting Regression OutputEstimating with Multiple RegressionUsing Excel’s TREND FunctionCreating Charts in Power BICreating a Scatter ChartCreating a Clustered Column Chart in Power BISummary
16 Fixed Assets
Determining Original CostDetermining CostsChoosing Between Actual Cost and Replacement CostDepreciating AssetsUnderstanding the Concept of DepreciationMatching Revenues to CostsUsing Straight-Line DepreciationUsing the Declining Balance MethodUsing the Double Declining Balance Function to Calculate DepreciationUsing Variable Declining Balance DepreciationUsing Sum-of-Years’-Digits DepreciationSummary
17 Importing Business Data into Excel
Creating and Using ODBC QueriesPreparing to Import DataSpecifying Data SourcesCreating Queries with the Query WizardCreating Queries with Microsoft QueryCreating Parameterized Queries in Microsoft QueryUsing Joins in Microsoft QueryWorking with External Data RangesInclude Row NumbersAdjust Column WidthPreserve Column Sort/Filter/LayoutPreserve Cell FormattingInsert Cells for New Data, Delete Unused CellsInsert Entire Rows for New Data, Clear Unused CellsOverwrite Existing Cells with New Data, Clear Unused CellsManaging Security InformationArranging Automatic RefreshesSetting Other Data Range OptionsImporting Data to Pivot Tables and ChartsCreating and Using Web QueriesGet External Data and Get Data From WebUsing Get Data with a WebsiteUsing Get External Data and VBASummary
18 Exporting Business Data from Excel
Using VBA to Update an External DatabaseGetting at VBAStructuring the WorksheetEstablishing Command ButtonsEditing the Record’s ValuesUsing Database ObjectsUsing With BlocksFinding the Right RecordEditing the RecordAdding New Records to the RecordsetChoosing to Use ADOSummary
19 Using Excel 2016 and Power BI to Analyze QuickBooks Data
Exporting an Income Statement to ExcelPublishing a QuickBooks Report in Power BIPreparing the Data in ExcelMoving the Report to Power BIUsing the QuickBooks Software Development KitParsing a Simple SubroutineInvoking QBFCIdentifying the Sources of the VariablesUnderstanding then RationaleRunning the Assembly TrackerOpening the QuickBooks Company FileOpening the Excel FileAllowing Access to QuickBooks Data
20 Analyzing Contributions and Margins
Calculating the Contribution MarginClassifying CostsEstimating Semivariable CostsUsing Unit ContributionProducing Digital Video Discs (Continued)Increasing the Contribution MarginCreating an Operating Income StatementFinding the Break-Even PointCalculating Breakeven in UnitsCalculating Breakeven in SalesCalculating Breakeven in Sales Dollars with a Specified Level of ProfitCharting the Break-Even PointChoosing the Chart TypeDisplaying a Break-Even Chart in Power BIMaking Assumptions in Contribution AnalysisLinear RelationshipsAssignment of CostsConstant Sales MixWorker ProductivityDetermining Sales MixSummary
21 Pricing and Costing
Using Absorption and Contribution CostingUnderstanding Absorption CostingUnderstanding Contribution CostingApplying the Contribution Approach to a Pricing Decision: Goal SeekApplying the Contribution Approach to a Pricing Decision: SolverUsing Contribution Analysis for New ProductsAllocating Expenses to Product LinesVarying the InputsEstimating the Effect of Cross-ElasticitySummary
Index

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft Excel Data Analysis and Business Modeling (Office 2021 and Microsoft 365), 7th Edition

Publisher Resources

ISBN: 9780134862538

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills