Conrad Carlberg

Business Analysis with Microsoft Excel (eBook, ePUB)

• Format: ePub

Produktbeschreibung

Whether you own a small business or work for a large corporation-whether you are looking for help making financial and business decisions-this book is for you. Business Analysis with Microsoft® Excel, Third Edition, provides in-depth information that will streamline your use of the tools within Excel. Professional advice and guidance from an experienced author provide the answers to your most pressing questions:

. What's the relationship between my cost of goods sold and my inventory?

. How do I get Excel to keep these values up-to-date on my income statement and my balance sheet?

. I have to track service quality over time. How can I automate that using Excel charts?

. How can I forecast future demand for my products, based on prior sales results?

. What's the difference between financial leverage and operating leverage? How do I calculate them using my financial records?

. I need to project my financials for next year. Does Excel have a tool for that? What do I need to know to use it effectively?

. How do I do all these things using the new Ribbon in Excel 2007?

. What are the best ways to automate a connection between Excel and an external database?

Category Spreadsheets

Covers Applicable for versions of Microsoft Excel 97 to 2007

User Level Intermediate-Advanced

More great stuff... is just a click away!

. Sample journals and ledgers; examples of trial balances, income statements, and balance sheets

. Custom functions, such as FIFO and LIFO, for inventory management

. VBA routines that automate the creation of forecasts, quality control charts, and sales and marketing analysis

. Statistical process control charts: P charts, X-and-S charts, X-and-MR charts

. Forecasting tools: Seasonal smoothing and ARIMA model identification

quote from the front cover

"Conrad takes the time to give the readers an easy-to-follow step-by-step understanding of the material, accompanied by clear illustrations, making this an excellent book to learn the material. I recommend this book to anyone wanting to gain more expertise in using and manipulating business data from within Excel."

-Bob Umlas, Excel MVP

---

Dieser Download kann aus rechtlichen Gründen nur mit Rechnungsadresse in A, B, BG, CY, CZ, D, DK, EW, E, FIN, F, GR, HR, H, IRL, I, LT, L, LR, M, NL, PL, P, R, S, SLO, SK ausgeliefert werden.

Produktdetails

• Verlag: Pearson ITP
• Erscheinungstermin: 19. Juni 2007
• Englisch
• ISBN-13: 9780132713597
• Artikelnr.: 52973311

Autorenporträt

Conrad Carlbergis president of Network Control Systems, Inc., a software-development and consulting firm that specializes in statistical and database applications. He holds a Ph.D. in statistics and is a many-time recipient of Microsoft's Most Valuable Professional Award. He lives near San Diego.

Inhaltsangabe

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