CAGR - ExcelModels

Document Sample
CAGR - ExcelModels Powered By Docstoc
					Chapter 1: Spreadsheet Basics

           What is a spreadsheet?
                      Matrix of cells
                      Recalculates as you change contents of other cells
                      Can graph quickly and easily
                      Shows impact of 1 variable on an entire system

          My spreadsheet history
                   Grandfather's graph paper
                   Fantasy baseball in school
                   Messed up first time using
                   15+ years work experience
                   Teaching Fin 325 (CSULA) and FINC 474 (Pepperdine)

Parts of an Excel spreadsheet
           Very different than the pre-2007 version, including common operations as bottons on top
           More record lines (1,000,000)

          The Office Button
                    Takes the place of the File menu
                    Quick-access toolbar is at top, for macros or favorite buttons.
          Menu Bar
                    Below the title bar, everything is displayed in groups of butons
                    Click or use Alt-(underlined letter)
          Formula Bar
                   Type formula
                   x to clear, checkmark to accept
                   fx to select formula

          Worksheet Area
                   256 columns x 1+ million rows
                   Nomenclature: (Column Letter)(Row Number)
                   Active cell displayed in upper left of Formula Bar

          Sheet Tabs- a 3-D workspace
                    Combine multiple Worksheets into Workbook
                    Right-click to copy, paste, move, delete, color, etc.
                    Hold dow Shift and drag/drop data cells to work with elwhere.
                    Control-select multiple worksheets to clone work
                    DVD buttons move between large numbers of sheets
                    Right-click DVD buttons to move to a specific sheet

          Status Bar
                       Select multiple number-filled cells to show Sum

Basic Spreadsheet Usage
                    Arrows for short distances
                    Scroll bars for larger distances
                    Control-X cut, Control-V Paste, Control-C copy
                    Click on cell or type cell in formula bar to select
                    Click-and-drag or Shift-arrows to select multiple cells
                    Control-click to select discontiguous cells
                    Excel uses Select + Act to complete tasks

          Entering Data (i.e. text and numbers)
                    Excel formats text differently from numbers
                    AutoFill to repeat patterns

          Formatting & Alignment
                    Text defaulted to align-left
                    Numbers defaulted to align-right
                      Change font type and size using Font color in Font section of the home toolbar
                      Most can be acessed without ay help but thre is a button to blow out the Alignment
                      Use Horizontal "Center Across Selection" for multiple cells
                      Change number type using the little box in the Font section.

            Changing Data Positioning
                     Select column letter or row number and use Insert-Cells
                     Or Drag-and-Drop data into new cells
                     Double-click on line between column labels to resize
                     Or use Format-Column Width-Auto Fit Selection

Basic Formula Usage
          Entering Formulas
                    Start with "="
                    Type cell names or click on cells to make references
                    Can type directly in cell or use Formula Bar

            Formatting Numbers
                      Can use Number Formatting Icons or Format-Cells-Number
                      Use Format-Cells-Number to change number type or decimals displayed
                      Or use Increase Decimal Places or Decrease Decimal Places icons

            Copying and Moving Formulas
                     Excel uses "relative" cell references unless specified
                     This means any copied formulas will update automatically.
                     Use Edit-Copy or Control-C to copy; Edit-Paste or Control-V to paste.
                     Can also use AutoFill just like with hardcoded numbers
                     Can paste copied formulas into multiple cells by selecting them with mouse.
                     Move formulas with, Edit-Cut, Control-X Control-V, or Drag-and-Drop
                     Moving formulas will not change their references. Copying them will.

            Order of Operation
                      Excel uses same rules as algebra: PEMDAS
                      x = 2 + 4 / 3 = 3.33
                      x = (2 + 4) / 3 = 2
            Sales Growth
                      Cannot simply look at the first and last figures
                      Need to compound growth percentage over n periods
                      May not match individual yearly numbers, but the total will
                      CAGR is the same as the geometric mean of all yearly growth rates
            Using Excel's Built-In Functions
                    Insert-Function and make choice
                    Function macros are additional functions not included with Excel
                    GeoMean is the average componed growth rate
           Formula Button

Charts & Graphs
          Purpose of Charts & Graphs
                     Look nice
                     Help you spot trends immediately that you might otherwise miss
          Creating a Graph
                     Select range of cells to view
                     Click chart shortcut button
                     1. Select chart type
                     2. Confirm source data
                     3. Create titles for chart and both axes
                     4. Confirm placement on current page to embed with numbers
          Formatting Axes
                     Double-click axis and select Scale Tab
                     Check "Categories in Reverse Order" box
                     Check "Value Y Axis Crosses at Maximum Category" box
          Formatting Legend and Other Items
                     Click graph and select Chart-Chart Options
                     Select Legend tab and click Bottom box
          Changing Chart Type
                     Click graph and select Chart-Chart Type
                     Choose Line Graph or another type
                     Different data sets are better on different types of graphs

                        First, do a Print Preview to see where things stand
                        Select Print-Page Setup to make adjustments
           Print Area
                        Select range of cells to print
                        It is recommended that you select the entire page
                        Select Print Area-Set Print Area
                        Do a final Print Preview
                        If not scaled, select entire spreadsheet and Print Area-Clear Print Area to clear
                        Repeat Setup
                        Select Print-Print and modify pages to print and number of copies as needed
Working with Excel Files
                    Use File-Save and File-Save as to save your work
                    Be careful! Do not overwrite current file if you want to make incremental copies.
                    Use File-Open, or double-click filename from folder, or use recently-saved shortcuts.
         Pasting into Word document
                    Copy range of cells
                    Use default Paste in Word.
                    Use Paste Special-Excel Worksheet Object to do additional work (not recommended).
bottons on top
he home toolbar
w out the Alignment

 with mouse.
nt Area to clear

ies as needed
ncremental copies.

ently-saved shortcuts.

rk (not recommended).

                        Microsoft Corporation Profitability Analysis
                        (Millions of Dollars)
                        2003 to 2008
                        2008          2007     2006        2005      2004    2003
Sales                     $60,420      $51,122 $44,282 $39,788 $36,835 $32,187
Net Profit                $17,681      $14,065 $12,599 $12,254 $8,168 $7,561
Net Profit %                   29.3%     27.5%    28.5%      30.8%     22.2%   23.5%

 (with Profit totals)
                                 Microsoft Corporation Profitability Analysis
                                            (Millions of Dollars)

                                             2000 to 2005
                           2008      2007    2006      2005    2004    2003
Sales                     $60,420   $51,122 $44,282 $39,788 $36,835 $32,187
Net Profit                  $17,681 $14,065 $12,599 $12,254 $8,168 $7,531
Net Profit %                 29.3%    27.5%   28.5%     30.8%   22.2%   23.4%
Sales growth                13.42%
Net Income growth           18.61%
% Change in Sales             1.182    1.154   1.113     1.080   1.144



                     $40,000   Sales
                               Net Profit
                               Net Profit %


2003   2004   2005
The Compounded Annual Growth Rate (CAGR) is:
                        "The growth rate that, if applied every year, grows the starting number to the ending number in a sequence"
or, alternatively,
                        "The average annual growth rate, taking into account compounding, that grows the starting number to the ending numbe

                              Microsoft Corporation Profitability Analysis
                                         (Millions of Dollars)
                                            2000 to 2005
                         2005   2006      2007      2003        2009       2010
Sales                   $22,956 $25,296      $28,365    $32,187     $36,835      $39,788
Year-to-Year Growth                 10.2%      12.1%      13.5%         14.4%      8.0%
Overall Growth            73.3%
Simple Average Growth     14.7%
CAGR                      11.6%

                                      Simple Growth Applied                                          Compounded Annual Growth Applied
                         Year                Sales           Year Gr%                         Year             Sales
                         2005              $22,956     *        14.7%                         2005            $22,956
                         2006         =    $26,322     *        14.7%                         2006       =    $25,625
                         2007         =    $30,182     *        14.7%                         2007       =    $28,605
                         2008         =    $34,609     *        14.7%                         2008       =    $31,931
                         2009         =    $39,684     *        14.7%                         2009       =    $35,644
                         2010         =    $45,503                                            2010       =    $39,788
                                          Does not equal actual 2005 Value                                  Equals actual 2005 Value
mber in a sequence"

ing number to the ending number in a sequence"

nded Annual Growth Applied
                        Year Gr%
                 *         11.6%
                 *         11.6%
                 *         11.6%
                 *         11.6%
                 *         11.6%

 Equals actual 2005 Value
 Compound Annual Growth Rate                                          CAGR
          (CAGR)                          2.25                        = (Last / First) ^ (1 / # P
                                                                      = (6.75 / 2) ^ (1 / 3) -1
                                                                      = (3.375) ^ (0.333) -1
                                                 50% Growth to 6.75   = 50%

                                                                      Simple Average
                                                                      Total Growth = (Last / Fir
                            1.5           1.5                         Total Growth / 3 Growth P
                                                                      = 79.1%
                                                                      ^^^^^^^^^^ A Meaningless
                                                 50% Growth to 4.5

               1             1             1

                                                  50% Growth to 3

 2             2             2             2

Growth from 1 to 2
              Growth from 2 to 3
                            Growth from 3 to 4
= (Last / First) ^ (1 / # Periods) -1
= (6.75 / 2) ^ (1 / 3) -1
= (3.375) ^ (0.333) -1

Simple Average
Total Growth = (Last / First) -1
Total Growth / 3 Growth Periods

^^^^^^^^^^ A Meaninglesss Number

Shared By: