- ホーム
- > 洋書
- > 英文書
- > Computer / Spreadsheets
Full Description
ANSWER KEY BUSINESS QUESTIONSCONTROL COMPANY FINANCESFORECAST SALESPREPARE BUSINESS CASESMAKE BETTER INVESTMENT DECISIONSIMPROVE QUALITYUSE EXCEL 2010 TO GAIN DEEPER INSIGHTS, MAKE SMARTER DECISIONS, AND EARN MORE PROFITSUsing real-world examples, Carlberg helps you put Excel's features and functions to work and get the power of quantitative analysis behind your management decisions.Excel expert Conrad Carlberg shows how to use Excel 2010 to perform the core financial tasks every manager and entrepreneur must master: analyzing statements, planning and controlling company finances, making investment decisions, and managing sales and marketing. Using real-world examples, Carlberg helps you get the absolute most out of Excel 2010's newest features and functions. Along the way, you'll discover the fastest, best ways to handle essential tasks ranging from importing business data to analyzing profitability ratios.Becoming an Excel expert has never been easier! You'll find crystal-clear instructions, insider insights, complete step-by-step projects, and more. It's all complemented by an extraordinary set of web-based resources, from sample journals and ledgers to business forecasting tools. * Use Excel analysis tools to solve problems throughout the business* Build and work with income statements and balance sheets* Value inventories and current assets, and summarize transactions* Calculate working capital and analyze cash flows* Move from pro formas to operating budgets that help guide your management decisions* Prepare business cases incorporating everything from discount rates to margin and contribution analysisAbout MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excel skills, and presents focused tasks and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will:* Dramatically increase your productivity-saving you 50 hours a year, or more* Present proven, creative strategies for solving real-world problems * Show you how to get great results, no matter how much data you have* Help you avoid critical mistakes that even experienced users make
Contents
Introduction .................................................................................................... 1Taking It on Faith ............................................................................................................................ 2Renamed and Improved Functions in Excel 2010 ........................................................................... 3Compatibility ............................................................................................................................ 4Consistency ............................................................................................................................... 4How This Book Is Organized ............................................................................................................ 6Two Special Skills: Named Ranges and Array Formulas .................................................................. 6Assigning Names ...................................................................................................................... 7Using Array Formulas ............................................................................................................... 8Conventions Used in This Book ....................................................................................................... 9PART I ANALYZING FINANCIAL STATEMENTS1 Working with Income Statements ...............................................................11Keeping Score ............................................................................................................................... 11Choosing the Right Perspective .............................................................................................. 12Defining Two Purposes for Accounting ................................................................................... 12Using the Income Statement ........................................................................................................ 13Choosing a Reporting Method ................................................................................................ 14Cells in Excel ........................................................................................................................... 15Measuring the Operating and Nonoperating Segments ......................................................... 19Moving from the General Journal to the Income Statement ......................................................... 20Getting the General Journal into Excel .................................................................................... 20Understanding Absolute, Relative, and Mixed References ...................................................... 22Getting the Journal Data to the Ledger .................................................................................. 23Getting the Ledger Data to the Income Statement ................................................................. 27Managing the Financial Analyses with Accrual Accounting .......................................................... 28Using Straight-Line Depreciation............................................................................................ 32Preparing the Trial Balance .................................................................................................... 33Moving Information into an Income Statement ..................................................................... 33Organizing with Traditional Versus Contribution Approaches ...................................................... 342 Balance Sheet: Current Assets .....................................................................37Designing the Balance Sheet ........................................................................................................ 38Understanding Balance Sheet Accounts ................................................................................. 38Understanding Debit and Credit Entries ................................................................................. 39Getting a Current Asset Cash Balance ........................................................................................... 41Using Sheet-Level Names ....................................................................................................... 41Getting a Cash Balance for Multiple Cash Accounts ................................................................ 44Handling Restricted Cash Accounts ......................................................................................... 46Getting a Current Asset Accounts Receivable Balance ................................................................... 46Allowing for Doubtful Accounts .............................................................................................. 47Using the Aging Approach to Estimating Uncollectibles ......................................................... 48Using the Percentage of Sales Approach to Estimating Uncollectibles ................................... 51Getting a Prepaid Expenses Balance ............................................................................................. 52Dealing with Insurance as a Prepaid Expense ......................................................................... 53Getting a Current Asset Balance.................................................................................................... 54Understanding the Inventory Flow ......................................................................................... 55Closing the Inventory Account ................................................................................................ 56Closing the Revenue and Expense Accounts ........................................................................... 563 Valuing Inventories for the Balance Sheet ...................................................59Understanding Perpetual and Periodic Inventory Systems ........................................................... 60Perpetual Inventory Systems .................................................................................................. 61Periodic Inventory Systems .................................................................................................... 62Valuing Inventories ....................................................................................................................... 64Valuation Methods Summarized ............................................................................................ 64Using Specific Identification ................................................................................................... 66Using Average Cost ................................................................................................................. 72Using the Moving Average Method ........................................................................................ 77Calculating the Moving Average and Weighted Average ....................................................... 79Using FIFO .............................................................................................................................. 81Using LIFO .............................................................................................................................. 87Comparing the Four Valuation Methods ....................................................................................... 89Specification Identification ..................................................................................................... 89Average Cost ........................................................................................................................... 89FIFO ....................................................................................................................................... 90LIFO ....................................................................................................................................... 90Handling Purchase Discounts ........................................................................................................ 91Calculating Turns Ratios ................................................................................................................ 924 Summarizing Transactions: From the Journals to the Balance Sheet .............95Understanding Journals ................................................................................................................ 97Understanding Special Journals .............................................................................................. 98Structuring the Special Sales Journal ...................................................................................... 98Structuring the Special Purchases Journal ............................................................................ 100Structuring the Cash Receipts Journal .................................................................................. 101Structuring the Cash Payments Journal ................................................................................ 103Excel Tables and Dynamic Range Names .................................................................................... 104Building Dynamic Range Names ........................................................................................... 106Using Dynamic Range Names in the Journals ....................................................................... 108Choosing Between Tables and Dynamic Range Names ......................................................... 108Understanding Ledgers............................................................................................................... 110Creating the General Ledger ................................................................................................. 110Creating Subsidiary Ledgers ................................................................................................. 112Automating the Posting Process........................................................................................... 113Getting a Current Liabilities Balance ........................................................................................... 1215 Working Capital and Cash Flow Analysis ....................................................123Matching Costs and Revenues .................................................................................................... 123Broadening the Definition: Cash Versus Working Capital............................................................ 125Determining the Amount of Working Capital ....................................................................... 126Determining Changes in Working Capital ............................................................................. 133Analyzing Cash Flow ................................................................................................................... 137Developing the Basic Information ........................................................................................ 138Summarizing the Sources and Uses of Working Capital ........................................................ 140Identifying Cash Flows Due to Operating Activities .............................................................. 141Combining Cash from Operations with Cash from Nonoperating Transactions ..................... 1426 Statement Analysis ..................................................................................145Understanding a Report by Means of Common-Sizing ............................................................... 146Using Common-Sized Income Statements............................................................................ 146Using Common-Sized Balance Sheets ................................................................................... 148Using Comparative Financial Statements ............................................................................. 149Using Dollar and Percent Changes in Statement Analysis ........................................................... 152Assessing the Financial Statements ...................................................................................... 152Handling Error Values ........................................................................................................... 154Evaluating Percentage Changes ........................................................................................... 155Common-Sizing and Comparative Analyses in Other Applications ............................................. 156Working in Excel with a Profit & Loss from QuickBooks ........................................................ 156Working in Excel with a QuickBooks Balance Sheet .............................................................. 158Common-Sizing for Variance Analysis ........................................................................................ 160Ratio to Ratio Comparisons .................................................................................................. 163Common-Sizing by Headcount ................................................................................................... 1647 Ratio Analysis ..........................................................................................169Interpreting Industry Averages and Trends ................................................................................ 170Comparing Ratios Within Industries ........................................................................................... 171Analyzing Ratios Vertically and Horizontally ........................................................................ 172Getting a Basis for Ratios ...................................................................................................... 173Analyzing Profitability Ratios ...................................................................................................... 176Finding and Evaluating Earnings Per Share .......................................................................... 176Determining Gross Profit Margin .......................................................................................... 177Determining Net Profit Margin ............................................................................................. 179Determining the Return on Assets ........................................................................................ 180Determining the Return on Equity ........................................................................................ 182Analyzing Leverage Ratios .......................................................................................................... 183Determining the Debt Ratio.................................................................................................. 184Determining the Equity Ratio ............................................................................................... 184Determining the Times Interest Earned Ratio ....................................................................... 185Analyzing Liquidity Ratios .......................................................................................................... 186Determining the Current Ratio ............................................................................................. 186Determining the Quick Ratio ................................................................................................ 187Analyzing Activity Ratios ............................................................................................................ 188Determining the Average Collection Period .......................................................................... 188Determining Inventory Turnover .......................................................................................... 1908 Budgeting and Planning Cycle ..................................................................191Creating Pro Forma Financial Statements ................................................................................... 191Forecasting by Percentage of Sales....................................................................................... 193Using Excel to Manage the Analysis ............................................................................................ 199Performing Sensitivity Analysis ............................................................................................ 200Moving from the Pro Forma to the Budget ................................................................................. 201Projecting Quarterly Sales..................................................................................................... 201Estimating Inventory Levels ................................................................................................. 202Fitting the Budget to the Business Plan................................................................................ 2059 Forecasting and Projections ......................................................................207Making Sure You Have a Useful Baseline .................................................................................... 208Moving Average Forecasts .......................................................................................................... 210Creating Forecasts with the Moving Average Add-In ........................................................... 212Dealing with the Layout of Excel's Moving Averages ............................................................ 213Creating Moving Average Forecasts with Excel's Charts ....................................................... 215Forecasting with Excel's Regression Functions ........................................................................... 216Making Linear Forecasts: The TREND Function ..................................................................... 217Making Nonlinear Forecasts: The GROWTH Function ............................................................ 220Creating Regression Forecasts with Excel's Charts ................................................................ 223Forecasting with Excel's Smoothing Functions ........................................................................... 225Projecting with Smoothing................................................................................................... 225Using the Exponential Smoothing Add-In ............................................................................ 226Choosing a Smoothing Constant........................................................................................... 228Making Smoothed Forecasts Handle Seasonal Data ............................................................. 229Using the Box-Jenkins ARIMA Approach: When Excel's Built-In Functions Won't Do ................. 234Understanding ARIMA Basics ................................................................................................ 234Charting the Correlograms ................................................................................................... 235Starting with Correlograms to Identify a Model ................................................................... 236Identifying Other Box-Jenkins Models .................................................................................. 23710 Measuring Quality ....................................................................................241Monitoring Quality Through Statistical Process Control .............................................................. 242Using Averages from Samples .............................................................................................. 242Using X-and-S Charts for Variables ....................................................................................... 243Interpreting the Control Limits ............................................................................................. 247Manufacturing ...................................................................................................................... 247Using P-Charts for Dichotomies ............................................................................................ 251Choosing the Sample Size ..................................................................................................... 253Determining That a Process Is Out of Control ....................................................................... 255Using X-and-MR Charts for Individual Observations ............................................................. 258Creating SPC Charts Using Excel ............................................................................................ 259Performing Acceptance Sampling ............................................................................................... 262Charting the Operating Characteristic Curve ......................................................................... 263Using Worksheet Functions for Quality Control .......................................................................... 268Sampling Units from a Finite Population .............................................................................. 269Using HYPGEOM.DIST in Excel 2010 .................................................................................. 270Sampling Units from a Nonfinite Population .............................................................................. 271Using NORMSDIST to Approximate BINOMDIST ............................................................... 271Sampling Defects in Units ..................................................................................................... 277Using the CRITBINOM Function.......................................................................................... 279PART III INVESTMENT DECISIONS11 Examining a Business Case: Investment ....................................................285Developing a Business Case ........................................................................................................ 286Getting Consensus for the Plan ............................................................................................. 286Showing Your Work .............................................................................................................. 288Developing the Excel Model ........................................................................................................ 289Developing the Inputs .......................................................................................................... 290Identifying the Costs ............................................................................................................. 292Moving to the Pro Forma ...................................................................................................... 293Preparing the Cash Flow Analysis ......................................................................................... 29612 Examining Decision Criteria for a Business Case .........................................299Understanding Payback Periods ................................................................................................. 300Understanding Future Value, Present Value, and Net Present Value .......................................... 304Calculating Future Value....................................................................................................... 305Calculating Present Value ..................................................................................................... 305Calculating Net Present Value .............................................................................................. 306Optimizing Costs ................................................................................................................... 30813 Creating a Sensitivity Analysis for a Business Case .....................................315Reviewing the Business Case ...................................................................................................... 315Managing Scenarios ................................................................................................................... 316Saving a Scenario for the Base Case ...................................................................................... 318Developing Alternative Scenarios ......................................................................................... 320Developing Scenarios That Vary Expenses ............................................................................ 323Summarizing the Scenarios .................................................................................................. 324Measuring Profit ......................................................................................................................... 325Calculating Internal Rate of Return....................................................................................... 325Calculating Profitability Indexes ........................................................................................... 327Estimating the Continuing Value .......................................................................................... 327Varying the Discount Rate Input ................................................................................................. 330Using the Goal Seek Tool ............................................................................................................ 33214 Planning Profits .......................................................................................335Understanding the Effects of Leverage ....................................................................................... 335The Effect of Business Risk .................................................................................................... 336Analyzing Operating Leverage .................................................................................................... 337Evaluating the Financial Implications of an Operational Change .......................................... 338Evaluating Fixed Expenses .................................................................................................... 339Evaluating Effect of Increasing Fixed Costs ........................................................................... 345Planning by Using the DOL ................................................................................................... 347Analyzing Financial Leverage ..................................................................................................... 348Distinguishing Business from Financial Risk ......................................................................... 348Determining the Debt Ratio.................................................................................................. 349Determining the Times Interest Earned Ratio ....................................................................... 35015 Making Investment Decisions UnderUncertain Conditions .............................................................................353Using Standard Deviations .......................................................................................................... 354Using Excel's Standard Deviation Functions.......................................................................... 356Understanding Confidence Intervals ........................................................................................... 357Using Confidence Intervals in a Market Research Situation .................................................. 358Calculating a Confidence Interval ......................................................................................... 359Interpreting the Interval ....................................................................................................... 360Refining Confidence Intervals ............................................................................................... 361Using Regression Analysis in Decision Making ............................................................................ 362Regressing One Variable onto Another ................................................................................. 362Interpreting the Trendline .................................................................................................... 364Avoiding Traps in Interpretation: Association Versus Causation ........................................... 367Regressing One Variable onto Several Other Variables: Multiple Regression ........................ 368Using Excel's Regression Add-In ........................................................................................... 373Interpreting Regression Output ............................................................................................ 375Estimating with Multiple Regression .................................................................................... 377Using Excel's TREND Function ............................................................................................... 37716 Fixed Assets .............................................................................................383Determining Original Cost .......................................................................................................... 383Determining Costs ................................................................................................................ 384Choosing Between Actual Cost and Replacement Cost ......................................................... 385Depreciating Assets .................................................................................................................... 386Understanding the Concept of Depreciation ......................................................................... 387Matching Revenues to Costs ................................................................................................. 387Using Straight-Line Depreciation.......................................................................................... 389Using the Declining Balance Method .................................................................................... 390Using the Double Declining Balance Function to Calculate Depreciation .............................. 393Using Variable Declining Balance Depreciation .................................................................... 395Using Sum-of-Years'-Digits Depreciation ............................................................................. 39717 Importing Business Data into Excel ...........................................................399Creating and Using ODBC Queries ............................................................................................... 400Preparing to Import Data...................................................................................................... 401Specifying Data Sources ........................................................................................................ 401Creating Queries with the Query Wizard ............................................................................... 405Creating Queries with Microsoft Query ................................................................................. 407Creating Parameterized Queries in Microsoft Query ............................................................. 410Using Joins in Microsoft Query .............................................................................................. 411Working with External Data Ranges ........................................................................................... 412Include Row Numbers .......................................................................................................... 412Adjust Column Width ........................................................................................................... 412Preserve Column Sort/Filter/Layout ..................................................................................... 413Preserve Cell Formatting ...................................................................................................... 413Insert Cells for New Data, Delete Unused Cells ..................................................................... 414Insert Entire Rows for New Data. Clear Unused Cells ............................................................ 415Overwrite Existing Cells with New Data, Clear Unused Cells ................................................. 416Managing Security Information ............................................................................................ 416Arranging Automatic Refreshes ............................................................................................ 418Setting Other Data Range Options ........................................................................................ 419Importing Data to Pivot Tables and Charts ........................................................................... 420Creating and Using Web Queries ................................................................................................ 424Using Parameterized Web Queries.............................................................................................. 42618 Exporting Business Data from Excel ..........................................................429Using VBA to Update an External Database ................................................................................ 429Getting at VBA ...................................................................................................................... 430Structuring the Worksheet ................................................................................................... 431Establishing Command Buttons ............................................................................................ 432Editing the Record's Values......................................................................................................... 433Using Database Objects ........................................................................................................ 435Using With Blocks ....................................................................................................................... 436Finding the Right Record ...................................................................................................... 437Editing the Record ................................................................................................................ 438Adding New Records to the Recordset ........................................................................................ 439Choosing to Use ADO .................................................................................................................. 442Back Ends Perform Data Management ................................................................................. 44219 Analyzing Contributions and Margins ........................................................445Calculating the Contribution Margin........................................................................................... 446Classifying Costs ................................................................................................................... 447Estimating Semivariable Costs .............................................................................................. 448Using Unit Contribution .............................................................................................................. 449Producing Digital Video Discs (Continued) ........................................................................... 449Increasing the Contribution Margin ...................................................................................... 450Creating an Operating Income Statement ............................................................................ 451Finding the Break-Even Point ..................................................................................................... 452Calculating Break-Even in Units ............................................................................................ 453Calculating Break-Even in Sales ............................................................................................ 453Calculating Break-Even in Sales Dollars with a Specified Level of Profit ............................... 454Charting the Break-Even Point ............................................................................................. 455Choosing the Chart Type ....................................................................................................... 457Making Assumptions in Contribution Analysis ............................................................................ 459Linear Relationships ............................................................................................................. 459Assignment of Costs ............................................................................................................. 460Constant Sales Mix ............................................................................................................... 460Worker Productivity .............................................................................................................. 461Determining Sales Mix ................................................................................................................ 46120 Pricing and Costing ..................................................................................465Using Absorption and Contribution Costing ................................................................................ 466Understanding Absorption Costing ....................................................................................... 466Understanding Contribution Costing .................................................................................... 472Applying the Contribution Approach to a Pricing Decision ................................................... 475Using Contribution Analysis for New Products ............................................................................ 477Allocating Expenses to Product Lines ................................................................................... 479Varying the Inputs ................................................................................................................ 480Estimating the Effect of Cross-Elasticity ...................................................................................... 481Glossary .......................................................................................................485TOC, 9780789743176, 5/12/10
