Docstoc

Excel 2007 Sales Spreadsheet Examples Toy Company

Document Sample
Excel 2007 Sales Spreadsheet Examples Toy Company Powered By Docstoc
					       Here is a list of what a cell can contain: TEXT, NUMBER, TRUE or FALSE, Blank cell, FORMULA, Blank from formula, erro
  TRUE FALSE logic formulas in Excel like: =B23=C23. This formula asks the question is the content of B23 the same as C23? T
                            because 15 <> 15.1. Case in words is not considered. For example Case = case is TRUE to Excel.
                                                        Comparative Operators: =, <>, >, >=, <, <=
             AND function (2 up to 255 logical tests that all must be TRUE for the AND function to deliver a TRUE to the cell or
            OR function (2 up to 255 logical tests that if at least one is TRUE then the OR function delivers a TRUE to the cell o
                                                NOT function converts TRUE to FALSE and FALSE to TRUE
 IS functions deliver a TRUE or FALSE to a cell or formula. These are some IS functions: ISTEXT function, ISNUMBER function,
                                                ISBLANK function, ISERROR function, ISNONTEXT function
           IF function can put one of two things in a cell. The IF function has three parts: 1) Logical Test, 2) Value If True, 3) Va
  IFERROR function looks to see if there is an error from a formula. If there is an error it puts something in the cell, otherwise
                                                                         calculate.



Logic, TRUE FALSE
formulas, IS & IF
functions
              List of what can go in a cell
TEXT                               rad
NUMBER                                                4.23
              TRUE                          TRUE
              FALSE                         FALSE
Error from formula                         #DIV/0!

                                                                       Logical    Comparative
Logical Question                   No.                       No.       Formula    Operator
Is 15=15.1?                                           15.0         15.1 FALSE     =
Is 15<>15.1?                                          15.0         15.1 TRUE      <>
Is 15>15?                                             15.0         15.0 FALSE     >

Is 15>=15?                                            15.0         15.0   TRUE    >=
Is 15<15?                                             15.0         15.0   FALSE   <
Is 15<=15?                                            15.0         15.0   TRUE    <=


Customer Credit Score                                    3
Customer Last Year Sales            $          500,000.00

Company Credit Score Hurdle                              3

Company Last Year Sales Hurdle      $         1,000,000.00
Rule 1: We have two logical tests
and both must be met before we
extend credit: AND function                 FALSE           AND

Rule 2: We have two logical tests
and "one or the other or both"
(also said in this way; 'at least
one') can be met before we
extend credit: OR function                  TRUE            OR

         ISTEXT function            rad                      TRUE    TRUE    Value refers to text.
         ISTEXT function                              23     FALSE   FALSE
      ISNUMBER function                               12     TRUE    TRUE    Value refers to a number.
      ISNUMBER function             Rad                      FALSE   FALSE
       ISLOGICAL function                   TRUE             TRUE    TRUE    Value refers to a logical value.
        ISBLANK function                                     TRUE    TRUE    Value refers to an empty cell.
        ISERROR function                    #REF!            TRUE    TRUE    Value refers to any error value (#N/A, #VALUE!
          ISERR function                    #N/A             FALSE   FALSE   Value refers to any error value except #N/A.
           ISNA function                    #N/A             TRUE    TRUE    Value refers to the #N/A (value not available) e
      ISNONTEXT function            Excel                    FALSE   FALSE   Value refers to any item that is not text. (Note

 IF function can put one of two things in a cell. The IF
               function has three parts:
                     1) Logical Test
                     2) Value If True
                    3) Value If False.
For the 'Value If True' and 'Value If False', you can put
numbers, text (must be in double quotes), formulas, or
                       cell ranges

         This example puts one of two words (text) into a cell
                                                           Text or
                                                           Number
                                   Text or Number          Formula
Big List                           Formula 1               2
word1                              Text                    Text
                                12 Number                  Number
word3                              Text                    Text
word4                              Text                    Text
                             5896 Number                   Number
word6                              Text                    Text
word7                              Text                    Text

        This example puts one of two numbers into a cell
Your Sales For Month                     $6,000.00
                 Do you get a bonus?
     You get Bonus if you sell $6,000.00 or more
Bonus Hurdle                                $6,000.00
Bonus Amount                                  $200.00
Your Bonus =                                  $200.00

            Put 1 of 2 Formulas in a cell
Revenues                                        $6.00
Expenses                                    $6,000.00
Net Loss                                    $5,994.00


Conditional Formatting (Home Ribbon in 2007, Format menu in 2003) uses Logical Formulas
 k cell, FORMULA, Blank from formula, error from formula
 n is the content of B23 the same as C23? The answer is FALSE
  example Case = case is TRUE to Excel.
  >, >=, <, <=
ND function to deliver a TRUE to the cell or formula)
he OR function delivers a TRUE to the cell or formula)
  and FALSE to TRUE
 ons: ISTEXT function, ISNUMBER function, ISLOGICAL function,
 SNONTEXT function
parts: 1) Logical Test, 2) Value If True, 3) Value If False
rror it puts something in the cell, otherwise it lets the formula




             Comparative Operator
             Equal
             Not Equal
             Great Than

             Greater Than Or Equal To
             Less Than
             Less Than Or Equal To
ue refers to text.

ue refers to a number.

ue refers to a logical value.
ue refers to an empty cell.
ue refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ue refers to any error value except #N/A.
ue refers to the #N/A (value not available) error value.
ue refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)
DIV/0!, #NUM!, #NAME?, or #NULL!).


unction returns TRUE if value refers to a blank cell.)
         Account Not Suspended, No Overdue Balance and
Rule 1            Last Year's Sales > $50,000.00
          Account Not Suspended and Both Credit Hurdles
         must be met and at least 1 of the remaining criteria
Rule 2                     must be met
                    Formula Inputs
Custome Accou Rating1 Rating2 Asset   Last    Over
r Name nt     Hurdle Hurdle Value     Year's Due
        Yes        2.5     4.5 200,000 50,000 Yes
                                                   Customer Credit Analysis For Accounts Receivable


        Accou                                          Over     Rule 1
        nt                                   Last      Due      Account Not Suspended, No
Custome Suspe                Asset           Year's    Balan    Overdue Balance and Last
r Name nded? Rating1 Rating2 Value           Sales     ce       Year's Sales > $50,000.00
Cust01              5     7.4 208,339         90,550
Cust02              5     5.9 374,775         51,255 Yes
Cust03            3.3     3.9 371,040         56,241
Cust04 Yes        1.2     2.4 331,439         69,920
Cust05 Yes        3.4     5.3 336,505         58,534 Yes
Cust06            4.5       6 336,794         60,423 Yes
Cust07            4.4     2.4 500,000         49,850
Cust08            2.9     5.5 375,800         62,100
Cust09            2.5     8.9 254,888         75,000
r Accounts Receivable
            Rule 2
            Account Not Suspended and
            Both Credit Hurdles must be                   ONLY: Two
            met and at least 1 of the                     rules are false,
            remaining criteria must be NONE               but Asset
            met                         Fail Both Rules   Hurdle Met         Decision
         Account Not Suspended, No Overdue Balance and
Rule 1            Last Year's Sales > $50,000.00
          Account Not Suspended and Both Credit Hurdles
         must be met and at least 1 of the remaining criteria
Rule 2                     must be met
                      Formula Inputs

        Accou                                        Over
        nt                                   Last    Due
Custome Suspe Rating1 Rating2 Asset          Year's Balan
r Name nded? Hurdle Hurdle Value             Sales   ce
        Yes        2.5     4.5 200,000        50,000 Yes
                                                   Customer Credit Analysis For Accounts Receivable


        Accou                                          Over     Rule 1
        nt                                   Last      Due      Account Not Suspended, No
Custome Suspe Rating1 Rating2 Asset          Year's    Balan    Overdue Balance and Last
r Name nded? Hurdle Hurdle Value             Sales     ce       Year's Sales > $50,000.00
Cust01               5     7.4 208,339        90,550                        TRUE
Cust02               5     5.9 374,775        51,255 Yes                    FALSE
Cust03             3.3     3.9 371,040        56,241                        TRUE
Cust04 Yes         1.2     2.4 331,439        69,920                        FALSE
Cust05 Yes         3.4     5.3 336,505        58,534 Yes                    FALSE
Cust06             4.5       6 336,794        60,423 Yes                    FALSE
Cust07             4.4     2.4 500,000        49,850                        FALSE
Cust08             2.9     5.5 375,800        62,100                        TRUE
Cust09             2.5     8.9 254,888        75,000                        TRUE
r Accounts Receivable
            Rule 2
            Account Not Suspended and
            Both Credit Hurdles must be                   ONLY: Two
            met and at least 1 of the                     rules are false,
            remaining criteria must be None               but Asset
            met                         Fail Both Rules   Hurdle Met         Decision
                        TRUE                  FALSE             FALSE        Approved
                        TRUE                  FALSE             FALSE        Approved
                       FALSE                  FALSE             FALSE        Take A Second Look
                       FALSE                  TRUE              TRUE         Reject
                       FALSE                  TRUE              TRUE         Reject
                        TRUE                  FALSE             FALSE        Approved
                       FALSE                  TRUE              TRUE         Reject
                        TRUE                  FALSE             FALSE        Approved
                        TRUE                  FALSE             FALSE        Approved
Is Account                          Credit           Credit                                   Past Year's
Suspended?      Customer            Rating 01        Rating 02        Asset Value             Sales
Not Suspended   Customer 01                      4                9              $159,441         $332,082
Not Suspended   Customer 02                      4                6              $796,571         $455,968
Not Suspended   Customer 03                      2                3             $1,243,611        $373,892
Suspended       Customer 04                      3                3             $4,939,327        $285,446
Not Suspended   Customer 05                      2                7             $2,647,055        $191,068
Not Suspended   Customer 06                      2                2             $1,409,633        $168,029
Not Suspended   Customer 07                      3                8             $4,350,814        $353,054
Not Suspended   Customer 08                      4                9             $2,395,694        $198,841
Not Suspended   Customer 09                      0                3             $1,629,030        $199,163
Not Suspended   Customer 10                      3                8              $207,371         $189,052
Suspended       Customer 11                      0                5             $4,857,701        $347,454
Not Suspended   Customer 12                      3                4             $1,968,563          $90,424
Not Suspended   Customer 13                      3                8             $3,183,148        $169,508
Not Suspended   Customer 14                      2                7             $3,898,837        $238,658
Not Suspended   Customer 15                      0                9             $4,643,757        $439,679

                                                                                  Assumptions
                Credit Criteria Credit               Credit                                Credit
                1               Criteria 2           Criteria 3       Credit Criteria 4    Criteria 5


                                    Hurdle
                Hurdle Credit       Credit           Hurdle                                   Current Over
                Rating 01           Rating 02        Asset Value Past Year's Sales            Due Balance

                                2           4.5         $200,000                    $50,000              0

Low                             0                0
High                            4               10
                    Rule 1              Rule 2
                    Not Suspended, No   Both Credit Hurdles must
                    Overdue Balance     be met and at least 1 of NONE: 2     ONLY: Two rules are
Current Over        and Past sales >    the remaining criteria   rules not   false, but Asset
Due Balance         $50,000.00          must be met              met)        Hurdle Met
                0
                1
                1
                1
                0
                0
                1
                0
                1
                1
                1
                1
                1
                0
                1




                    Rule 1              Rule 2

                    Not Suspended, No   Both Credit Hurdles must
                    Overdue Balance     be met and at least 1 of
Is Account          and Past sales >    the remaining criteria
Suspended?          $50,000.00          must be met

Suspended

Not Suspended
Is Account                          Credit           Credit                           Past Year's Current Over
Suspended?      Customer            Rating 01        Rating 02        Asset Value     Sales         Due Balance
Not Suspended   Customer 01                      4                9        $159,441       $332,082                  0
Not Suspended   Customer 02                      4                6        $796,571       $455,968                  1
Not Suspended   Customer 03                      2                3      $1,243,611       $373,892                  1
Suspended       Customer 04                      3                3      $4,939,327       $285,446                  1
Not Suspended   Customer 05                      2                7      $2,647,055       $191,068                  0
Not Suspended   Customer 06                      2                2      $1,409,633       $168,029                  0
Not Suspended   Customer 07                      3                8      $4,350,814       $353,054                  1
Not Suspended   Customer 08                      4                9      $2,395,694       $198,841                  0
Not Suspended   Customer 09                      0                3      $1,629,030       $199,163                  1
Not Suspended   Customer 10                      3                8        $207,371       $189,052                  1
Suspended       Customer 11                      0                5      $4,857,701       $347,454                  1
Not Suspended   Customer 12                      3                4      $1,968,563         $90,424                 1
Not Suspended   Customer 13                      3                8      $3,183,148       $169,508                  1
Not Suspended   Customer 14                      2                7      $3,898,837       $238,658                  0
Not Suspended   Customer 15                      0                9      $4,643,757       $439,679                  1

                                                                              Assumptions
                Credit Criteria Credit               Credit           Credit Criteria Credit
                1               Criteria 2           Criteria 3       4               Criteria 5


                                    Hurdle
                Hurdle Credit       Credit           Hurdle      Past Year's          Current Over Is Account
                Rating 01           Rating 02        Asset Value Sales                Due Balance Suspended?

                                2           4.5         $200,000            $50,000                0 Suspended

Low                             0                0                                                  Not Suspended
High                            4               10
Rule 1              Rule 2
Not Suspended, No   Both Credit Hurdles must
Overdue Balance     be met and at least 1 of NONE: 2     ONLY: Two rules are
and Past sales >    the remaining criteria   rules not   false, but Asset
$50,000.00          must be met              met)        Hurdle Met
       TRUE                   TRUE               FALSE           FALSE
       FALSE                  TRUE               FALSE           FALSE
       FALSE                 FALSE               TRUE             TRUE
       FALSE                 FALSE               TRUE             TRUE
       TRUE                   TRUE               FALSE           FALSE
       TRUE                  FALSE               FALSE           FALSE
       FALSE                  TRUE               FALSE           FALSE
       TRUE                   TRUE               FALSE           FALSE
       FALSE                 FALSE               TRUE             TRUE
       FALSE                  TRUE               FALSE           FALSE
       FALSE                 FALSE               TRUE             TRUE
       FALSE                 FALSE               TRUE             TRUE
       FALSE                  TRUE               FALSE           FALSE
       TRUE                   TRUE               FALSE           FALSE
       FALSE                 FALSE               TRUE             TRUE




Rule 1              Rule 2

Not Suspended, No   Both Credit Hurdles must
Overdue Balance     be met and at least 1 of
and Past sales >    the remaining criteria
$50,000.00          must be met
                                                                                        Taxable Earnings
Name        Hours Wage    Gross      Beg Cumulative  End Cumulative UnEm
Employee 1      33  12.55     414.15       25,001.37       25,415.52
Employee 2      30  25.00     750.00       36,589.74       37,339.74
Employee 3      38  23.00     874.00      115,450.01      116,324.01
Employee 4      40  22.00     880.00        5,000.06        5,880.06
Employee 5      32  24.00     768.00        6,999.00        7,767.00
Employee 6      40  17.00     680.00        7,000.34        7,680.34
Employee 7      37  13.00     481.00        7,001.93        7,482.93
Employee 8      39  24.75     965.25       94,199.64       95,164.89
Employee 9      32  17.00     544.00       94,200.50       94,744.50
Employee 10     38  24.19     919.22       94,201.13       95,120.35
Employee 11     33  19.00     627.00       95,000.49       95,627.49
Employee 12     39  20.35     793.65      125,000.35      125,794.00
Employee 13     39  18.00     702.00          500.67        1,202.67
Employee 14     32  11.00     352.00        6,850.76        7,202.76
Employee 15     30  18.00     540.00       20,000.00       20,540.00
Employee 16     37  12.45     460.65       93,990.66       94,451.31
               569 301.29  10,750.92      826,986.65      837,737.57             -

                                                                               Assumptions
                                                    UnEm State      UnEm Fed
                                                              5.40%        0.08%
                                                                     UnEm Fed Ceiling
                                                                               7,000




                                                           105,000

                                                            73,900
                                                            67,000
                                                            62,000
                                                            57,000
                                                            55,000
                                                             5,000
Taxable Earnings                        Deductions
 SS             Medicare      SS         Medicare Pension Total Ded. Net Pay     Expense
                       414.15       -         6.01    8.28      14.29     399.86      414.15
                       750.00       -       10.88    15.00      25.88     724.12      750.00
                       874.00       -       12.67    17.48      30.15     843.85      874.00
                       880.00       -       12.76    17.60      30.36     849.64      880.00
                       768.00       -       11.14    15.36      26.50     741.50      768.00
                       680.00       -         9.86   13.60      23.46     656.54      680.00
                       481.00       -         6.97    9.62      16.59     464.41      481.00
                       965.25       -       14.00    19.31      33.31     931.94      965.25
                       544.00       -         7.89   10.88      18.77     525.23      544.00
                       919.22       -       13.33    18.38      31.71     887.51      919.22
                       627.00       -         9.09   12.54      21.63     605.37      627.00
                       793.65       -       11.51    15.87      27.38     766.27      793.65
                       702.00       -       10.18    14.04      24.22     677.78      702.00
                       352.00       -         5.10    7.04      12.14     339.86      352.00
                       540.00       -         7.83   10.80      18.63     521.37      540.00
                       460.65       -         6.68    9.21      15.89     444.76      460.65
           -       10,750.92        -      155.90   215.01    370.91  10,380.01    10,750.92

Assumptions
 SS             Medicare      Pension
          6.20%         1.45%         2%
 SS Ceiling
        105,000



 CURRENT Pay check



                                            23100
                                             9000
         6,900                               6900
         5,000
         5,000
         2,000
        50,000
         5,000
                                                                                         Taxable Earnings
Name        Hours Wage    Gross      Beg Cumulative  End Cumulative UnEm
Employee 1      33  12.55     414.15       25,001.37       25,415.52         -
Employee 2      30  25.00     750.00       36,589.74       37,339.74         -
Employee 3      38  23.00     874.00      115,450.01      116,324.01         -
Employee 4      40  22.00     880.00        5,000.06        5,880.06      880.00
Employee 5      32  24.00     768.00        6,999.00        7,767.00        1.00
Employee 6      40  17.00     680.00        7,000.34        7,680.34         -
Employee 7      37  13.00     481.00        7,001.93        7,482.93         -
Employee 8      39  24.75     965.25       94,199.64       95,164.89         -
Employee 9      32  17.00     544.00       94,200.50       94,744.50         -
Employee 10     38  24.19     919.22       94,201.13       95,120.35         -
Employee 11     33  19.00     627.00       95,000.49       95,627.49         -
Employee 12     39  20.35     793.65      125,000.35      125,794.00         -
Employee 13     39  18.00     702.00          500.67        1,202.67      702.00
Employee 14     32  11.00     352.00        6,850.76        7,202.76      149.24
Employee 15     30  18.00     540.00       20,000.00       20,540.00         -
Employee 16     37  12.45     460.65       93,990.66       94,451.31         -
               569 301.29  10,750.92      826,986.65      837,737.57    1,732.24

                                                                               Assumptions
                                                    UnEm State      UnEm Fed
                                                              5.40%        0.08%
                                                                      UnEm Fed Ceiling
                                                                                7,000




                                                            106,000            1,000
                                                            105,000
                                                             82,900           22,100
                                                             73,900           23,100
                                                             67,000
                                                             62,000
                                                             57,000
                                                             55,000
                                                              5,000
Taxable Earnings                        Deductions
 SS             Medicare      SS         Medicare Pension Total Ded. Net Pay     Expense
        414.15         414.15     25.68       6.01    8.28      39.97     374.18      414.15
        750.00         750.00     46.50     10.88    15.00      72.38     677.62      750.00
           -           874.00       -       12.67    17.48      30.15     843.85      874.00
        880.00         880.00     54.56     12.76    17.60      84.92     795.08      880.00
        768.00         768.00     47.62     11.14    15.36      74.12     693.88      768.00
        680.00         680.00     42.16       9.86   13.60      65.62     614.38      680.00
        481.00         481.00     29.82       6.97    9.62      46.41     434.59      481.00
        965.25         965.25     59.85     14.00    19.31      93.16     872.09      965.25
        544.00         544.00     33.73       7.89   10.88      52.50     491.50      544.00
        919.22         919.22     56.99     13.33    18.38      88.70     830.52      919.22
        627.00         627.00     38.87       9.09   12.54      60.50     566.50      627.00
           -           793.65       -       11.51    15.87      27.38     766.27      793.65
        702.00         702.00     43.52     10.18    14.04      67.74     634.26      702.00
        352.00         352.00     21.82       5.10    7.04      33.96     318.04      352.00
        540.00         540.00     33.48       7.83   10.80      52.11     487.89      540.00
        460.65         460.65     28.56       6.68    9.21      44.45     416.20      460.65
      9,083.27     10,750.92     563.16    155.90   215.01    934.07    9,816.85   10,750.92

Assumptions
 SS             Medicare      Pension
          6.20%         1.45%         2%
 SS Ceiling
        105,000



 CURRENT Pay check



        23,100                             23100
         9,000                              9000
         6,900                              6900
         5,000
         5,000
         2,000
        50,000
         5,000
 Put 1 of 3 Percentages in a
           Formula               Mutually Exclusive Categories Sales    %
Your Sales        $6,000.00      0 >= Your Sales < 5000              $0 0.00%
Your Bonus                       5000 >= Your Sales < 10000      $5,000 2.00%
                                 Your Sales > = 10000           $10,000 4.00%

  Multiple IF Rule # 1: Be sure to start at top and go to bottom, or at the
                            bottom and go to the top
 Multiple IF Rule # 2: If there are 3 possibilities, there are 2 IFs, If there are 4
                         possibilities, there are 3 IFs, etc.


  VLOOKUP to Replace IF          Mutually Exclusive Categories Sales    %
Your Sales     $6,000.00         0 >= Your Sales < 5000              $0 0.00%
Your Bonus                       5000 >= Your Sales < 10000      $5,000 2.00%
                                 Your Sales > = 10000           $10,000 4.00%
 Put 1 of 3 Percentages in a
           Formula              Mutually Exclusive Categories Sales    %
Your Sales        $6,000.00     0 >= Your Sales < 5000              $0 0.00%
Your Bonus          $120.00     5000 >= Your Sales < 10000      $5,000 2.00%
                                Your Sales > = 10000           $10,000 4.00%

Multiple IF Rule # 1: Be sure to start at top and go to bottom, or at the bottom
                                 and go to the top
 Multiple IF Rule # 2: If there are 3 possibilities, there are 2 IFs, If there are 4
                         possibilities, there are 3 IFs, etc.


  VLOOKUP to Replace IF   Mutually Exclusive Categories Sales    %
Your Sales     $6,000.00 0 >= Your Sales < 5000               $0 0.00%
Your Bonus            120 5000 >= Your Sales < 10000      $5,000 2.00%
                          Your Sales > = 10000           $10,000 4.00%
1)                 Range means a group of cells such as B12:E12
2)              The Range B12:E12 can contain the values: 5, 7, 9, 11

      An array (different than a range) means a group of things (values, text,
3)    TRUE/FALSE) stored in a range of cells or in an array using array syntax

       Array syntax uses the: 1) curly bracket to contain the array, 2) comma
4)            for column and 3) semi-colon for row, 4) Text is in quotes
5)                           An array could be: {5, 7, 9, 11}
      If the values did not need to be in cells, instead of =SUM(B12:E12), you
6)                     could use the formula =SUM({5, 7, 9, 11})
       To create an array from a range of values, click in a cell, type the equal
        sign, highlight a range of cells, then highlight the range of cells in the
      formula, hit the F9 key, then Ctrl + C to copy the array with all the array
7)                                        syntax.

8)      An array could be: {''Week1'',''Week2'',''Week3'',''Week4'';5,7,9,11}

        Instead of the formula, =VLOOKUP(B20,$B$15:$C$17,2,0), you could
                 use this (then the data is hard coded into formula),
          =VLOOKUP(B20,{""Part1"",25;""Part2"",10;""Part3"",5},2,0). This
       formula is not an Array Formula (because it does not use Ctrl + Shift +
9)        Enter to put formula in cell), but it is a formula that uses an array.

      Week1          Week2            Week3       Week4               Total          Total
                 5                7           9                  11

      Item           Price
      Part1                  $25.00
      Part2                  $10.00
      Part3                   $5.00

      Product        Price
      Part2


                Different than regular formulas, array formulas can:
                1) Do operations on arrays instead of individual cells

       2) Allow you to enter functions such as TRANSPOSE, FREQUENCY and
                  MMULT, which are by definition array formulas.
       3) Allow ranges or arrays in function arguments where the function is
                              expecting a single value
                Array Formulas can do operations on arrays such as
10)                          =SUM(B35:B38*C35:C38)
    In order to get Excel to recognize that your formula is an array formula,
     you must use the key strokes Ctrl + Shift + Enter to put the formula in
11)                          the cell or range of cells.

     When you use Ctrl + Shift + Enter to put an array formula into a cell or
12) range of cells Curley Brackets appear automatically around the formula.
     If you enter =SUM(B35:B38*C35:C38), {=SUM(B35:B38*C35:C38)} will
13)                                   appear
     Array formulas can save spreadsheet real-estate. For example, instead
    of using an extra column to calculate sales for each transaction and then
        adding the total for each transaction, you can simply use an array
14)                             formula in one cell.


       In cell D40 create this formula: =SUM(B35:B38*C35:C38), then use Ctrl + Shift +
                   Enter to create the Array Formula. The result will look like
         {=SUM(B35:B38*C35:C38)}. The curley brackets will be put in automatically.
      Units         Price          Sales
                 5        $22.00              What are we doing: multiply, then add
                 2        $23.00              Array formula advantage: use less space
                 4        $25.00              Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul
                 6        $24.50
                    Total                               $403.00
                    Total                     Array formula in 1 cell
                    Total                     SUMPRODUCT multiplies the related ranges and then adds. SUMPRODUCT can b
       To Enter Array Formula you must use keystrokes: Ctrl +
                           Shift + Enter

        In cell D53 create this formula: =MIN(C46:C51-B46:B51), then use Ctrl + Shift +
          Enter to create the Array Formula. The result will look like {=MIN(C46:C51-
                   B46:B51)}. The curley brackets will be put in automatically.

      Sample       Sample
      Weight Beg Weight End Change
  1      10.00 lbs     11.20 lbs              What are we doing: subtracting then find MIN
  2      12.50 lbs     13.00 lbs              Array formula advantage: use less space
  3       9.00 lbs     10.00 lbs              Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul
  4      11.50 lbs     12.00 lbs
  5      10.00 lbs     10.25 lbs
  6      14.00 lbs     14.75 lbs

                                              Array formula in 1 cell
       To Enter Array Formula you must use keystrokes: Ctrl +
                           Shift + Enter
     In cell D65 create this formula: =MAX(C58:C63-B58:B63), then use Ctrl + Shift +
       Enter to create the Array Formula. The result will look like {=MAX(C58:C63-
                B58:B63)}. The curley brackets will be put in automatically.

    Stock $ End Stock $ Beg    Change
1             26            27                  What are we doing: subtracting then find MAX
2             28            19                  Array formula advantage: use less space
3             13            15                  Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul
4             27            15
5             17            29
6             28          32.5

                                                Array formula in 1 cell
     To Enter Array Formula you must use keystrokes: Ctrl +
                         Shift + Enter
                Weight in
                Portfolio           0.333333         0.333333333 0.33333333

    Economic    Probability         Stock1      Stock2               Stock3           Expected
    State       of State            Return      Return               Return           Returns
    Weak                      0.4         0.1                 0.15              0.2
    Boom                      0.6        0.08                 0.04                0

                                                                                                 Array formula in 1 cell
                                                                                                 Don't forget Ctrl + Shift + Enter
                Weight in
                Portfolio             0.25                    0.75
    Economic    Probability     Stock1     Stock2                    Expected
    State       of State        Return     Return                    Returns
    Weak                  0.55       -0.02                    0.05
    Normal                  0.3     0.059                    0.065
    Boom                  0.15        0.12                   0.085

                                                                                      Array formula in 1 cell Don't forget Ctrl + Shift
                                                                                      Does not require Ctrl + Shift + Enter
    Example of SUMPRODUCT formula that will not work:                 #VALUE!         For SUMPRODUCT, you must use multiplication
      If you want to multiply ranges and add, instead of the SUM function
    with the keystrokes Ctrl + Shift = Enter, you can use the SUMPRODUCTS
    function. This is not an array formula because we do not use Ctrl + Shift
    + Enter. SUMPRODUCT can handle arrays without Ctrl + Shift + Enter. If
      the ranges used in SUMPRODUCT are the same size, use ranges and
     commas. If the ranges used in SUMPRODUCT are NOT the same size,
15)                 use ranges and the multiplication symbol.
16)              See this video for more about SUMPRODUCT:                    Excel Formula Efficiency 1: SUMPRODUCT function 12 E


17)     For other videos and video series about array formulas, see these:      06 Excel Series: Array Formulas
                                                                                Excel Magic Trick 313: Some Facts About Array Formula
                                                                                07 Excel Formula Efficiency Series



                  Weight in
                  Portfolio             0.25                 0.75
      Economic    Probability     Stock1     Stock1
      State       of State        Return     Return
      Weak                  0.55       -0.02                0.05
      Normal                  0.3     0.059                0.065
      Boom                  0.15        0.12               0.085




                  E. Returns
and larger array formulas may take a long time to calculate




ds. SUMPRODUCT can be used with commas when the ranges are the same size




and larger array formulas may take a long time to calculate
and larger array formulas may take a long time to calculate




Ctrl + Shift + Enter




 Don't forget Ctrl + Shift + Enter
Shift + Enter
 must use multiplication instead of ranges and commas because the size of the ranges/arrays are not the same.
SUMPRODUCT function 12 Examples



e Facts About Array Formulas
1)                 Range means a group of cells such as B12:E12
2)              The Range B12:E12 can contain the values: 5, 7, 9, 11

      An array (different than a range) means a group of things (values, text,
3)    TRUE/FALSE) stored in a range of cells or in an array using array syntax

       Array syntax uses the: 1) curly bracket to contain the array, 2) comma
4)            for column and 3) semi-colon for row, 4) Text is in quotes
5)                           An array could be: {5, 7, 9, 11}
      If the values did not need to be in cells, instead of =SUM(B12:E12), you
6)                     could use the formula =SUM({5, 7, 9, 11})
       To create an array from a range of values, click in a cell, type the equal
        sign, highlight a range of cells, then highlight the range of cells in the
      formula, hit the F9 key, then Ctrl + C to copy the array with all the array
7)                                        syntax.

8)      An array could be: {''Week1'',''Week2'',''Week3'',''Week4'';5,7,9,11}

        Instead of the formula, =VLOOKUP(B20,$B$15:$C$17,2,0), you could
                 use this (then the data is hard coded into formula),
          =VLOOKUP(B20,{""Part1"",25;""Part2"",10;""Part3"",5},2,0). This
       formula is not an Array Formula (because it does not use Ctrl + Shift +
9)        Enter to put formula in cell), but it is a formula that uses an array.

      Week1          Week2            Week3       Week4               Total            Total
                 5                7           9                  11            32              32

      Item           Price                                                         1
      Part1                  $25.00                                                1
      Part2                  $10.00                              25                1
      Part3                   $5.00                                                3

      Product        Price
      Part2                      10
                                 10

                Different than regular formulas, array formulas can:
                1) Do operations on arrays instead of individual cells

       2) Allow you to enter functions such as TRANSPOSE, FREQUENCY and
                  MMULT, which are by definition array formulas.
       3) Allow ranges or arrays in function arguments where the function is
                              expecting a single value
                Array Formulas can do operations on arrays such as
10)                          =SUM(B35:B38*C35:C38)
    In order to get Excel to recognize that your formula is an array formula,
     you must use the key strokes Ctrl + Shift + Enter to put the formula in
11)                          the cell or range of cells.

     When you use Ctrl + Shift + Enter to put an array formula into a cell or
12) range of cells Curley Brackets appear automatically around the formula.
     If you enter =SUM(B35:B38*C35:C38), {=SUM(B35:B38*C35:C38)} will
13)                                   appear
     Array formulas can save spreadsheet real-estate. For example, instead
    of using an extra column to calculate sales for each transaction and then
        adding the total for each transaction, you can simply use an array
14)                             formula in one cell.


       In cell D40 create this formula: =SUM(B35:B38*C35:C38), then use Ctrl + Shift +
                   Enter to create the Array Formula. The result will look like
         {=SUM(B35:B38*C35:C38)}. The curley brackets will be put in automatically.
      Units         Price          Sales
                 5        $22.00 $110.00 What are we doing: multiply, then add
                 2        $23.00      $46.00 Array formula advantage: use less space
                 4        $25.00 $100.00 Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul
                 6        $24.50 $147.00
                    Total            $403.00            $403.00
                    Total            $403.00 Array formula in 1 cell
                    Total            $403.00 SUMPRODUCT multiplies the related ranges and then adds. SUMPRODUCT can b
       To Enter Array Formula you must use keystrokes: Ctrl +
                           Shift + Enter

        In cell D53 create this formula: =MIN(C46:C51-B46:B51), then use Ctrl + Shift +
          Enter to create the Array Formula. The result will look like {=MIN(C46:C51-
                   B46:B51)}. The curley brackets will be put in automatically.

      Sample       Sample
      Weight Beg Weight End Change
  1      10.00 lbs     11.20 lbs 1.20 lbs What are we doing: subtracting then find MIN
  2      12.50 lbs     13.00 lbs 0.50 lbs Array formula advantage: use less space
  3       9.00 lbs     10.00 lbs 1.00 lbs Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul
  4      11.50 lbs     12.00 lbs 0.50 lbs
  5      10.00 lbs     10.25 lbs 0.25 lbs
  6      14.00 lbs     14.75 lbs 0.75 lbs
                                 0.25 lbs
                                 0.25 lbs Array formula in 1 cell
       To Enter Array Formula you must use keystrokes: Ctrl +
                           Shift + Enter
      In cell D65 create this formula: =MAX(C58:C63-B58:B63), then use Ctrl + Shift +
        Enter to create the Array Formula. The result will look like {=MAX(C58:C63-
                 B58:B63)}. The curley brackets will be put in automatically.

    Stock $ End Stock $ Beg    Change
1             26            27        -1 What are we doing: subtracting then find MAX
2             28            19         9 Array formula advantage: use less space
3             13            15        -2 Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul
4             27            15        12
5             17            29       -12
6             28          32.5      -4.5
                                      12
                                      12 Array formula in 1 cell
     To Enter Array Formula you must use keystrokes: Ctrl +
                         Shift + Enter
                 Weight in
                 Portfolio           0.333333        0.333333333 0.33333333

    Economic     Probability         Stock1      Stock2            Stock3       Expected
    State        of State            Return      Return            Return       Returns
    Weak                       0.4         0.1              0.15            0.2      0.06
    Boom                       0.6        0.08              0.04              0     0.024
                                                                                    0.084
                                                                                    0.084 Array formula in 1 cell
                                                                                          Don't forget Ctrl + Shift + Enter
                 Weight in
                 Portfolio             0.25                 0.75
    Economic     Probability     Stock1     Stock2         Expected
    State        of State        Return     Return         Returns
    Weak                   0.55       -0.02           0.05    0.017875
    Normal                   0.3     0.059          0.065       0.01905
    Boom                   0.15        0.12         0.085 0.0140625
                                                             0.0509875
                                                             0.0509875 Array formula in 1 cell Don't forget Ctrl + Shift
                                                             0.0509875 Does not require Ctrl + Shift + Enter
    Example of SUMPRODUCT formula that will not work:        #VALUE! For SUMPRODUCT, you must use multiplication
      If you want to multiply ranges and add, instead of the SUM function
    with the keystrokes Ctrl + Shift = Enter, you can use the SUMPRODUCTS
    function. This is not an array formula because we do not use Ctrl + Shift
    + Enter. SUMPRODUCT can handle arrays without Ctrl + Shift + Enter. If
      the ranges used in SUMPRODUCT are the same size, use ranges and
     commas. If the ranges used in SUMPRODUCT are NOT the same size,
15)                 use ranges and the multiplication symbol.
16)              See this video for more about SUMPRODUCT:                    Excel Formula Efficiency 1: SUMPRODUCT function 12 E


17)     For other videos and video series about array formulas, see these:      06 Excel Series: Array Formulas
                                                                                Excel Magic Trick 313: Some Facts About Array Formula
                                                                                07 Excel Formula Efficiency Series



                  Weight in
                  Portfolio             0.25                 0.75
      Economic    Probability     Stock1     Stock1
      State       of State        Return     Return
      Weak                  0.55       -0.02                0.05
      Normal                  0.3     0.059                0.065
      Boom                  0.15        0.12               0.085
                                   -0.00275            0.020625
                                   0.004425            0.014625
                                      0.0045          0.0095625
                  E. Returns       0.050988           0.0509875
                                   0.050988           0.0509875
and larger array formulas may take a long time to calculate




ds. SUMPRODUCT can be used with commas when the ranges are the same size




and larger array formulas may take a long time to calculate
and larger array formulas may take a long time to calculate




Ctrl + Shift + Enter




 Don't forget Ctrl + Shift + Enter
Shift + Enter
 must use multiplication instead of ranges and commas because the size of the ranges/arrays are not the same.
SUMPRODUCT function 12 Examples



e Facts About Array Formulas
                       SUMPRODUCT function
                SUM = add ==> The "sum" of 3 + 4 = 7
         PRODUCT = multiply ==> The "product" of 1 * 3 = 3
The SUMPRODUCT function multiplies arrays of the same size and then
                         adds the products                          note: The array arguments must have the same dim
        The SUMPRODUCT function multiplies 1st, then adds                 SUMPRODUCT treats array entries that are no
             SUMPRODUCT can deal with ranges or arrays
 1 row by 2 column array * 1 row by 2 column array =
              A8:B8*A9:B9 = 1*3+2*2 = 7
                                         1           2
                                         3           2
1*3+2*2 = 7
SUMPRODUCT(A8:B8,A9:B9) = 7. Notice
commas between arrays.
    You can do this: SUMPRODUCT(A8:B8*A9:B9) = 7 but for large
             spreadsheets it may take longer to calculate.

                                        1          2
                                                                    3   2
1*3+2*2 = 7
SUMPRODUCT(A14:B14,C15:D15) = 7.
Notice commas between arrays.

                                        1          2                    3
                                                                        2
 The array arguments must have the same dimensions. If they do not,
           SUMPRODUCT returns the #VALUE! error value.
SUMPRODUCT(A19:B19,D19:D20) will not
work because the arrays are not the same
dimensions. The dimensions are 1 by 2 * 2
by 1.

You can get around this by using a
multiplication symbol instead of a comma.
SUMPRODUCT(A19:B19*D19:D20)
This is what it did: 1*3+2*3+1*2+2*2 =


   Rule about when to use comma: When ranges/arrays are same
                            dimensions
 Rule about when to use multiplication symbol: When ranges/arrays
                     are different dimensions

  SUMPRODUCT is great for dealing with arrays of TRUEs and FALSEs
           when you are doing conditional adding
 Although you can use *1, /1, +0 to convert TRUEs & FALSEs to 1s and
      0s, the method that calculates fastest is double negative --.
Month                                         Feb
Year                                                2009
Sales Rep                                     Julie
Add
Count Records
Date                                          SalesRep # Phone Calls
                                  1/2/2009 Mo                        5 SUMIFS cannot do this because SUMIFS cannot handl
                                  2/1/2009 Julie                     2 =SUMIFS(C38:C45,--(TEXT(A38:A45,"mmmyyyy")),B3
                                 1/15/2009 Mo                        5
                                  2/1/2009 Mo                        3
                                 1/30/2009 Julie                     2
                                 2/15/2009 Julie                     2
                                 2/15/2009 Mo                        3
                                 1/28/2009 Julie                     3


SUMPRODUCT can deal with arrays, whereas the SUMIFS function can
 not (our TEXT example is proof of this). Note: SUMIFS see workbook
    references as arrays and so it is best to use SUMPRODUCT for
       conditional adding when you have workbook references.




See this video for more about SUMPRODUCT and SUMIF:
Excel Magic Trick 315: SUMIF Only Accept Ranges, Not Arrays
 must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
s array entries that are not numeric as if they were zeros.
                                          Months   Years        SalesRep
                                          Jan              2009 Mo
                                          Feb                   Julie




use SUMIFS cannot handle arrays.
38:A45,"mmmyyyy")),B32&B33,B38:B45,B34)
                       SUMPRODUCT function
                SUM = add ==> The "sum" of 3 + 4 = 7
         PRODUCT = multiply ==> The "product" of 1 * 3 = 3
The SUMPRODUCT function multiplies arrays of the same size and then
                         adds the products                          note: The array arguments must have the same dim
        The SUMPRODUCT function multiplies 1st, then adds                 SUMPRODUCT treats array entries that are no
             SUMPRODUCT can deal with ranges or arrays
 1 row by 2 column array * 1 row by 2 column array =
              A8:B8*A9:B9 = 1*3+2*2 = 7
                                         1           2
                                         3           2
1*3+2*2 = 7                                          7
SUMPRODUCT(A8:B8,A9:B9) = 7. Notice
commas between arrays.                               7            7
    You can do this: SUMPRODUCT(A8:B8*A9:B9) = 7 but for large
             spreadsheets it may take longer to calculate.

                                        1          2
                                                                    3   2
1*3+2*2 = 7
SUMPRODUCT(A14:B14,C15:D15) = 7.
Notice commas between arrays.                      7

                                        1          2                    3
                                                                        2
 The array arguments must have the same dimensions. If they do not,
           SUMPRODUCT returns the #VALUE! error value.
SUMPRODUCT(A19:B19,D19:D20) will not
work because the arrays are not the same
dimensions. The dimensions are 1 by 2 * 2
by 1.                                     #VALUE!

You can get around this by using a
multiplication symbol instead of a comma.
SUMPRODUCT(A19:B19*D19:D20)                       15
This is what it did: 1*3+2*3+1*2+2*2 =            15


   Rule about when to use comma: When ranges/arrays are same
                            dimensions
 Rule about when to use multiplication symbol: When ranges/arrays
                     are different dimensions

  SUMPRODUCT is great for dealing with arrays of TRUEs and FALSEs
           when you are doing conditional adding
 Although you can use *1, /1, +0 to convert TRUEs & FALSEs to 1s and
      0s, the method that calculates fastest is double negative --.
Month                                         Feb
Year                                                2009
Sales Rep                                     Julie
Add                                                    4
Count Records                                          2
Date                                          SalesRep # Phone Calls
                                  1/2/2009 Mo                        5 SUMIFS cannot do this because SUMIFS cannot handl
                                  2/1/2009 Julie                     2 =SUMIFS(C38:C45,--(TEXT(A38:A45,"mmmyyyy")),B3
                                 1/15/2009 Mo                        5
                                  2/1/2009 Mo                        3
                                 1/30/2009 Julie                     2
                                 2/15/2009 Julie                     2
                                 2/15/2009 Mo                        3
                                 1/28/2009 Julie                     3


SUMPRODUCT can deal with arrays, whereas the SUMIFS function can
 not (our TEXT example is proof of this). Note: SUMIFS see workbook
    references as arrays and so it is best to use SUMPRODUCT for
       conditional adding when you have workbook references.




See this video for more about SUMPRODUCT and SUMIF:
Excel Magic Trick 315: SUMIF Only Accept Ranges, Not Arrays
 must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
s array entries that are not numeric as if they were zeros.
                                          Months   Years        SalesRep
                                          Jan              2009 Mo
                                          Feb                   Julie




use SUMIFS cannot handle arrays.
38:A45,"mmmyyyy")),B32&B33,B38:B45,B34)
     Array Functions must be entered as array formulas with the key strokes Ctrl +
1)                                  Shift + Enter


     TRANSPOSE (turns flips the range 90 degrees) is an array function
      that: 1) must be entered using Ctrl + Shift + Enter 2) All the cell
            must be highlighted before you create the formula.

      In the case of TRANSPOSE you must count the number of columns
        and rows in the original and then highlight a similar range, but
                  flipped 90 degrees ==> 4 x 2 becomes 2 x 4.
     Item                  Price
     Part1                    $50.00
     Part2                    $10.00
     Part3                     $5.00




           To Enter Array Formula you must use
              keystrokes: Ctrl + Shift + Enter
      FREQUENCY is an array function that: 1) must be entered using Ctrl + Shift +
       Enter 2) All the cell must be highlighted before you create the formula. The
       FREQUENCY function uses two arguments: data_array and bins_array (see
                                           below)

     Data_array                      Bins_array (Upper
     (Numbers to be                  Limit of each
     counted)                        class)
                       5                            10 <= 10
                       9                            20 10 > and <= 20
                      10                            30 20 > and <= 30
                      15
                                         To Enter Array Formula you must use
                      22                    keystrokes: Ctrl + Shift + Enter
                      21
                       4             Some Other Array Functions and videos to watch:
                                     FREQUENCY         Excel Array Formula Series #8: FREQUENCY function
                                     TRANSPOSE         Excel Array Formula Series #2: TRANSPOSE function
                                     MMULT             Excel Array Formula Series 12: MMULT function Matrix Algebra
                                     ROW               Excel Array Formula Series #11: ROW & COLUMN functions
                                     COLUMN            Excel Array Formula Series #11: ROW & COLUMN functions
                                     MINVERSE
                                     LINEST            Excel Array Formula Series #13: LINEST function
UENCY function
 SPOSE function
 LT function Matrix Algebra
W & COLUMN functions
W & COLUMN functions
     Array Functions must be entered as array formulas with the key strokes Ctrl +
1)                                  Shift + Enter


     TRANSPOSE (turns flips the range 90 degrees) is an array function
      that: 1) must be entered using Ctrl + Shift + Enter 2) All the cell
            must be highlighted before you create the formula.

      In the case of TRANSPOSE you must count the number of columns
        and rows in the original and then highlight a similar range, but
                  flipped 90 degrees ==> 4 x 2 becomes 2 x 4.
     Item                  Price
     Part1                    $25.00
     Part2                    $10.00
     Part3                     $5.00

     Item                  Part1    Part2                Part3
     Price                   $25.00             $10.00             $5.00

             To Enter Array Formula you must use
                keystrokes: Ctrl + Shift + Enter
      FREQUENCY is an array function that: 1) must be entered using Ctrl + Shift +
       Enter 2) All the cell must be highlighted before you create the formula. The
       FREQUENCY function uses two arguments: data_array and bins_array (see
                                           below)

     Data_array                      Bins_array (Upper
     (Numbers to be                  Limit of each
     counted)                        class)
                       5                            10 <= 10                         4
                       9                            20 10 > and <= 20                1
                      10                            30 20 > and <= 30                2
                      15
                                         To Enter Array Formula you must use
                      22                    keystrokes: Ctrl + Shift + Enter
                      21
                       4             Some Other Array Functions and videos to watch:
                                     FREQUENCY         Excel Array Formula Series #8: FREQUENCY function
                                     TRANSPOSE         Excel Array Formula Series #2: TRANSPOSE function
                                     MMULT             Excel Array Formula Series 12: MMULT function Matrix Algebra
                                     ROW               Excel Array Formula Series #11: ROW & COLUMN functions
                                     COLUMN            Excel Array Formula Series #11: ROW & COLUMN functions
                                     MINVERSE
                                     LINEST            Excel Array Formula Series #13: LINEST function
UENCY function
 SPOSE function
 LT function Matrix Algebra
W & COLUMN functions
W & COLUMN functions
              If you enter a range of values or an array into a function argument that is
                                        expecting a single value:
                   1) You must use Ctrl + Shift + Enter to put the formula in the cell
             2) You will return an array of values. If the function is in one cell, it will only
              show the first value in the array. If the function is in a formula or function
1)                        that expects an array, the whole array will be used.


 TEXT function with custom number
  format to count Dates in Jan 2009
Criteria 1 Jan
Criteria 2                       2009
Count                               3
Count                               3
Sum Sales                          10
Sum Sales                          10

Dates      Sales
 1/25/2009                       $5.00
 2/20/2009                       $2.00
  7/1/2009                       $5.00
  5/2/2009                       $6.00
  1/2/2009                       $1.00
 1/18/2009                       $4.00
 4/23/2009                       $2.00

Date         Item                        VLOOUP Price
  5/5/2009   Part1                                                     Item         Price
  5/6/2009   Part2                                                     Part1           $25.00
  5/7/2009   Part2                                                     Part2           $10.00
  5/8/2009   Part3                                                     Part3            $5.00
  5/9/2009   Part1
             Total
             Total
             Total

SalesRep     Sales
Joe                              $5.00
Sioux                            $2.00
Joe                              $5.00
Sioux                            $6.00
Joe                              $1.00
Joe                              $4.00
Sioux                            $2.00

criteria     Joe
MEDIAN                                                             =MEDIAN(IF(A31:A37=B39,B31:B37))
MAX                                                                =MAX(IF(A31:A37=B39,B31:B37))
MIN                                                                =MIN(IF(A31:A37=B39,B31:B37))

Names         Count Unique Records
Tom                                                                =SUMPRODUCT(--(A45:A51<>""),1/COUNTIF(A45:A51,A45:
Sioux
Sue
Sioux

Jo
Mo




2)                                           Array Formulas: Add the 3 highest values
3)                                     Remember to enter array formula with Ctrl + Shift + Enter
4)                                         The formula is: =SUM(LARGE(D11:H11,{1,2,3}))

                                              Boomerang Maximun Time Aloft
              Throw 1                     Throw 2       Throw 3 Throw 4 Throw 5 Sum Top 3
Thrower 1                          35.1            42.3       43     16.5   22.15
Thrower 2                          42.3            35.1     16.5    22.15      43
Thrower 3                          6.87            38.2     47.8    27.76   20.34
Thrower 4                         22.15            16.5       43     42.3    35.1
Thrower 5                         26.36            49.8    11.97    27.46   19.96
Thrower 6                         26.36           42.85    45.62    24.54    3.98
Thrower 7                         28.18           17.03    35.21    32.38    6.66
Thrower 8                         10.82           39.95    27.78     2.05    54.3
Thrower 9                          35.1            42.3     16.5       43   22.15
Thrower 10                         8.47           32.62    11.95    13.07   16.36


              Warning about Array Formulas:
      In large spreadsheets array formulas can slow
           calculating speed down considerably.
     For tips about large spreadhseets, see this video
                           Series:
07 Excel Formula Efficiency Series
=SUM((TEXT(A12:A18,"mmmyyyy")=B4&B5)*B12:B18)
=SUMPRODUCT(--(TEXT(A12:A18,"mmmyyyy")=B4&B5),B12:B18)




=SUM(SUMIF(E22:E24,B21:B25,F22:F24))
=SUMPRODUCT(SUMIF(E22:E24,B21:B25,F22:F24))
B39,B31:B37))




5:A51<>""),1/COUNTIF(A45:A51,A45:A51&""))




+ Enter
))


           Rank
                               =SUM(LARGE(B61:F61,{1,2,3}))
                If you enter a range of values or an array into a function
                          argument that is expecting a single value:
             1) You must use Ctrl + Shift + Enter to put the formula in the
                                              cell
              2) You will return an array of values. If the function is in one
                  cell, it will only show the first value in the array. If the
             function is in a formula or function that expects an array, the
1)                                  whole array will be used.

  TEXT function with
custom number format
 to count Dates in Jan
           2009
Criteria 1 Jan
Criteria 2        2009
Count                 3
Count                 3
Sum Sales            10                                                          =SUM((TEXT(A12:A18,"mmmyyyy")=
Sum Sales            10                                                          =SUMPRODUCT(--(TEXT(A12:A18,"m
                          Jan2009
Dates      Sales
 1/25/2009     $5.00
 2/20/2009     $2.00
  7/1/2009     $5.00
  5/2/2009     $6.00
  1/2/2009     $1.00
 1/18/2009     $4.00
 4/23/2009     $2.00

Date         Item         VLOOUP Price
  5/5/2009   Part1              $25.00                Item         Price
  5/6/2009   Part2              $10.00                Part1           $25.00
  5/7/2009   Part2              $10.00                Part2           $10.00
  5/8/2009   Part3               $5.00                Part3            $5.00
  5/9/2009   Part1              $25.00
             Total              $75.00
             Total              $75.00                                           =SUM(SUMIF(E22:E24,B21:B25,F22:F
             Total              $75.00                                           =SUMPRODUCT(SUMIF(E22:E24,B21

SalesRep     Sales
Joe              $5.00
Sioux            $2.00
Joe              $5.00
Sioux            $6.00
Joe              $1.00
Joe              $4.00
Sioux            $2.00
criteria     Joe
MEDIAN              4.5                             =MEDIAN(IF(A31:A37=B39,B31:B37))
MAX                   5                             =MAX(IF(A31:A37=B39,B31:B37))
MIN                   1                             =MIN(IF(A31:A37=B39,B31:B37))

Names        Count Unique Records
Tom                  5                              =SUMPRODUCT(--(A45:A51<>""),1/COUNTIF(A45:A51,A45:A51&""))
Sioux
Sue
Sioux

Jo
Mo




2)                                   Array Formulas: Add the 3 highest values
3)                             Remember to enter array formula with Ctrl + Shift + Enter
4)                                 The formula is: =SUM(LARGE(D11:H11,{1,2,3}))

                                    Boomerang Maximun Time Aloft
             Throw 1      Throw 2       Throw 3 Throw 4 Throw 5 Sum Top 3 Rank
Thrower 1         35.1             42.3        43    16.5      22.15   120.4                  3.5
Thrower 2         42.3             35.1      16.5   22.15          43  120.4                  3.5
Thrower 3         6.87             38.2      47.8   27.76      20.34  113.76                    7
Thrower 4        22.15             16.5        43    42.3        35.1  120.4                  3.5
Thrower 5        26.36             49.8     11.97   27.46      19.96  103.62                    8
Thrower 6        26.36            42.85     45.62   24.54        3.98 114.83                    6
Thrower 7        28.18            17.03     35.21   32.38        6.66  95.77                    9
Thrower 8        10.82            39.95     27.78    2.05        54.3 122.03                    1
Thrower 9         35.1             42.3      16.5      43      22.15   120.4                  3.5
Thrower 10        8.47            32.62     11.95   13.07      16.36   62.05                  10


     Warning about Array Formulas:
  In large spreadsheets array formulas
    can slow calculating speed down
               considerably.
 For tips about large spreadhseets, see
             this video Series:
07 Excel Formula Efficiency Series
 XT(A12:A18,"mmmyyyy")=B4&B5)*B12:B18)
ODUCT(--(TEXT(A12:A18,"mmmyyyy")=B4&B5),B12:B18)




 MIF(E22:E24,B21:B25,F22:F24))
ODUCT(SUMIF(E22:E24,B21:B25,F22:F24))
(A45:A51,A45:A51&""))




           =SUM(LARGE(B61:F61,{1,2,3}))
Succeeding in Business with MS Excel 2007, ISBN 978-1-4239-0605-6, Authors:
                          Gross, Akaiwa, Nordquist
                    PO527 Credit Analysis HW P242-243
                     WWC Hiring Analysis HW P276-278
    Unpaid Invoice Penalties P257-259, Painting Job Estimator P 278-280
605-6, Authors:




P 278-280
                                  04839dba-f672-4ee9-9af3-2bc2c09774cd.xls - P214-241 - Level 3


                        Rule #1                                      Rule #2                      Rule #3
Reject                                  FALSE                                     FALSE                     FALSE
Further Evaluate                        TRUE                                      FALSE                     FALSE
                                                                     OR
                                        FALSE                                     TRUE                      FALSE
                                        FALSE                                     FALSE                     TRUE
                                        FALSE                                     TRUE                      TRUE
         Inside an or
Accept                                   TRUE                                     TRUE                      TRUE
                                         TRUE                                     FALSE                     TRUE
                                         TRUE                                     TRUE                      FALSE
                                                                                          Accounts Receivable Department - Custo

                                                                                                                            D&B
                                                                                                                 D&B     Composite
                                                                                                                Credit     Credit
                                                                            Current    Past Due   Net Worth     Rating   Appraisal (1
Customer Name              Current Credit Limit   Previous Year's Sales   Year's Sales Balance     in (000)     Class      Best)
Athletic Gear Corp.    $                  9,000   $             15,382    $ 11,952 $      0       $       450    BA           4
Baltimore O's                            39,000                 10,033       7,789        0             1,950    3A           1
Baseball & More                          75,000                 60,009      55,342   13,892            37,500    4A           2
Canadian Ski Club                        33,000                 35,039      50,921      495             1,650    BA           2
Concord Pro Shop                                                                                       10,000    4A           1
Everything Golf                         25,000                  15,221        9,483      2,899          1,250    3A           3
Lake Pro Shops                          42,000                  80,498       81,126          0          2,100    3A           2
Mars Dept. Store                        27,000                  35,354       20,666          0            213    BB           3
RG Bradley                              46,000                  90,970       18,343          0          2,300    3A           1
RX for Sports                           15,000                   5,663        3,014          0            750    2A           1
School Sports Supply                    45,000                  50,278       32,338          0          2,250    3A           3
Ski World                               26,000                  25,864       28,154          0            300    BA           2
Sneaker Kingdom                         45,000                  40,157       25,379          0          2,250    3A           2
Sports & Stuff                          15,000                  15,898       14,732     14,383            450    BA           1
Toy Kingdom                             22,000                  10,073        1,047          0          1,100    3A           3
Under the Sea                           45,000                  95,411       64,418          0            150    CB           4
US Olympic Team                         20,000                   5,621        6,171          0          1,000    3A           1
WWW Sports Inc.                        100,000                  60,009       60,354          0        500,000    5A           2
Zip & Sons                              10,000                  15,490       22,760          0            620    1A           2



                       Formatting Rules
                       Net worth is less
                       than
Rule 1                 $1,000,000.00              $   1,000,000.00
                       Net worth is
                       greater than
Rule 2                 $10,000,000.00             $ 10,000,000.00
vable Department - Customer Credit Analysis

                                               Rule 1 (FALSE is Reject):   Rule 2 (FALSE is Reject):
             D&B                               Past due balance is less    Composite credit appraisal
           PAYDEX      D&B Stress Risk Class   than 10% of this year's     value of 1 OR a PAYDEX
          (100 Best)         (1 Best)          total sales                 score over 90
             15                 3                          TRUE                       FALSE
             51                 1                          TRUE                       TRUE
             70                 1                          FALSE                      FALSE
             43                 1                          TRUE                       FALSE
             91                 1                                                     TRUE
             76                 1                        FALSE                        FALSE
             87                 1                        TRUE                         FALSE
             94                 1                        TRUE                         TRUE
             21                 1                        TRUE                         TRUE
             59                 1                        TRUE                         TRUE
             91                 1                        TRUE                         TRUE
             82                 1                        TRUE                         FALSE
             71                 1                        TRUE                         FALSE
             67                 1                        FALSE                        TRUE
             14                 1                        TRUE                         FALSE
             79                 2                        TRUE                         FALSE
             87                 1                        TRUE                         TRUE
             97                 1                        TRUE                         TRUE
             96                 1                        TRUE                         TRUE
Rule 3 (FALSE is Reject): Net worth of
at least $500,000.00 AND a composite
credit appraisal value less than or
equal to 2 AND PAYDEX score over 70      Reject? Further Evaluate?
AND a stress risk class of 1             Accept?
                 FALSE                   Accept
                 FALSE                   FurtherEvaluate
                 FALSE                   Reject
                 FALSE                   Accept
                  TRUE                   FurtherEvaluate
                 FALSE                   Reject
                  TRUE                   FurtherEvaluate
                 FALSE                   FurtherEvaluate
                 FALSE                   FurtherEvaluate
                 FALSE                   FurtherEvaluate
                 FALSE                   FurtherEvaluate
                 FALSE                   Accept
                  TRUE                   FurtherEvaluate
                 FALSE                   FurtherEvaluate
                 FALSE                   Accept
                 FALSE                   Accept
                  TRUE                   FurtherEvaluate
                  TRUE                   FurtherEvaluate
                  TRUE                   FurtherEvaluate
                                                                                PO 527 Bidder List Financial Evalu

                                                                                                  D&B
                                                                                               Composite
                                                                                                 Credit
                                                                               Net Worth       Appraisal (1
    Bidder List                   Previous Experience Grade                     (Dollars)        Best)
Logistic SB Inc.     None                                                     $ 2,500,000                 2
XBE Corporation      Satisfactory                                              237,000,000                1
Software Solutions   Satisfactory                                                 1,823,000               3
Exceed RW            Unsatisfactory                                                  75,000               2
Roberts Wise Inc.    Satisfactory                                                   680,000               3

                                                                              Did any evaluate to TRUE?
                                                                              Did all not evaluate to TRUE? Or Did all come

                                                                            Assumptions
Rule 1               D&B Stress Risk Class (1 Best) Hurdle = 1                                1
                     D&B PAYDEX (100 Best) is greater than 60 AND D&B
                     Composite Credit Appraisal (1 Best) is less than or equal
                     to 2 AND Net Worth (Dollars) are greater than
Rule 2               $1,000,000.00                                                          60              2
                     D&B PAYDEX (100 Best) is greater than or equal to 85
Rule 3               OR Previous Experience Grade is Satisfactory                           85 Satisfactory
                     Format if TRUE = Green                                        TRUE
                     Format if FALSE = Grey                                       FALSE
                     Format Bidder name who had Previous Experience
                     Grade "Unsatisfactory"                                    Unsatisfactory

Note:
Conditional formatting in column A is based on values in Column B. The formula is: =B3=$C$18
er List Financial Evaluation

                                                     Rule 2: D&B PAYDEX (100 Best)
                                                        is greater than 60 AND D&B
                                       Rule 1: D&B
                                                       Composite Credit Appraisal (1      Rule 3: D&B PAYDEX (100 Best)
                            D&B Stress Stress Risk   Best) is less than or equal to 2 AND is greater than or equal to 85 OR
             D&B PAYDEX Risk Class Class (1 Best)    Net Worth (Dollars) are greater than   Previous Experience Grade is
              (100 Best)     (1 Best) Hurdle = 1                 $1,000,000.00                        Satisfactory
                         85      1          TRUE                 TRUE                                TRUE
                         90      1          TRUE                 TRUE                                TRUE
                         55      2          FALSE                FALSE                               TRUE
                         38      1          TRUE                 FALSE                               FALSE
                         43      1          TRUE                 FALSE                               TRUE

                                          TRUE                   TRUE                                TRUE
to TRUE? Or Did all come out false?       FALSE                  FALSE                               FALSE




            $ 1,000,000.00
                                                                                  TheZone Customer Accounts - Credit & Payme




                                                                                                                  60-Days     90-Days
Customer Name                                     Customer Type      Current Balance Due       30-Days Past Due   Past Due    Past Due
Athletic Gear Corp.                                      A           $            8,612    $                  0   $       0   $       0
Baltimore O's                                            B                            0                       0           0           0
Baseball & More                                          A                        2,345                   3,473       5,557       4,862
Canadian Ski Club                                        C                            0                     345           0         150
Everything Golf                                          A                            0                       0       2,000         899
Sports & Stuff                                           A                            0                  14,000         383           0

                                                                                  Assumptions
Credit Approved
Credit Denied

 Note: Order for non-mutually exclusive criteria matters here because you can have balances in all three categories (not mutua
                                                             and so you must test


                                              Minimum before          Minimum               Surcharge % of 90
                                              applying surcharge     surcharge             day balance
 If the 90-Days Past Due balance is greater
than $200.00 then the large of $100.00 or
10% of 90 day balance                         $             200      $             100                 10.00%
                                              Fee for 30-Days        Fee for 60-Days       Fee for 90-Days
                                              Past Due               Past Due              Past Due
Alternative Penalty Scheme                    $               25     $               50    $              100
                                              Past Due               Past Due              Past Due
Days                                                           30                     60                    90

                           This example is of criteria that is mutually exclusive, the order of the logical tests does not matter.

                                              Late Fee             Cust Type
A Cust Type Late Fee                                            50 A
B Cust Type Late Fee                                           100 B
C Cust Type Late Fee                                           150 C



             VLOOKUP if often better than many Ifs. But if you use Ifs, better to go from low to high or high to low instead of betw
                                              Hurdle                 Penalty
 Level 1: less tha $1,000.00                  $              1,000   $               25
 Level 2: less than $5,000.00 and greater
than or equal to $1,000.00                    $              1,000   $               50
 Level 3 greater than $5,000.00               $              5,000   $              100




Past Due                                       =IF(A40<B$33,C$33,IF(A40<B$35,C$34,C$35))                           =IF(A40>B$35,C$35,IF(A40>B$34,C$34,C$3
$                                       670   $          25.00                                                    $ 25.00
$                                       141   $          25.00                                                    $ 25.00
$                                     3,893   $          50.00                                                    $ 50.00
$                                     4,741   $          50.00                                                    $ 50.00
$                                     8,434   $         100.00                                                    $ 100.00
$                                     7,401   $         100.00                                                    $ 100.00
$   4,819   $    50.00   $ 50.00
$   8,344   $   100.00   $ 100.00
counts - Credit & Payment Status
                                                                              If the 90-Days Past   Alternative
                                                                                 Due balance is      Penalty
                          Current Credit Status (FALSE
                                                                            greater than $200.00 Scheme: 30 =
                           means that they have been                           then the large of   $25.00; 60 =      Customer
               Total Past  denied credit but may still   Credit Approved    $100.00 or 10% of 90 $50.00; 90 =       Type Late
              Due Balance            owe $)              or Credit Denied         day balance        $100.00           Fee
               $        0           TRUE                  Credit Approved   $                  -   $         -     $       -
                        0           TRUE                  Credit Approved   $                  -   $         -     $       -
                   13,892           TRUE                  Credit Approved   $                  486 $        100    $        50
                      495           TRUE                  Credit Approved   $                  -   $        100    $       150
                    2,899           FALSE                  Credit Denied    $                  100 $        100    $        50
                   14,383           FALSE                  Credit Denied    $                  -   $          50   $        50




 all three categories (not mutually exclusive),




al tests does not matter.




h or high to low instead of between.




 >B$35,C$35,IF(A40>B$34,C$34,C$33))                       =IF(AND(A40>=B$34,A40<B$35),C$34,IF(A40<B$33,C$33,C$35))
                                                         $          25.00
                                                         $          25.00
                                                         $          50.00
                                                         $          50.00
                                                         $         100.00
                                                         $         100.00
$    50.00
$   100.00
           Fixed Fee & 90 Day Penalties                             Graduated Penalties - Range                     Penalty
Fixed Fee Penalty                $               50             Graduated penalty <                    $    1,000   $         0
Hurdle for penalty               $           100                Graduated penalty >=1000 but <              5,000       100
90-day penalty percentage                    10%                Graduated penalty >=                        5,000       200



                                     TheZone Accounts Payable - Outstanding Balance Report
                                                      Applies                                          Total Past
                                                       Fixed      30-Days       60-Days 90-Days          Due         Fixed
Vendor Name                      Category             Penalty    Past Due      Past Due Past Due        Balance     Penalty
RTF Electric                     Utilities            TRUE      $     16,254   $       0   $      0    $   16,254   $    50
Ross County Water & Sewer        Utilities            FALSE            1,435           0          0         1,435   $         0
YNC Trucking                     Transportation       TRUE                 0           0       3,300        3,300   $    50
Italian Leather Group Ltd.       Raw Materials        TRUE             6,756           0       5,674       12,430   $    50
Union Plastics                   Raw Materials        TRUE                 0       436            0          436    $    50
Freight to Go                    Transportation       FALSE                0           0        873          873    $         0
Temps R'Us                       Labor                FALSE            2,700           0          0         2,700   $         0
Notworth Telephone               Telephone            TRUE                 0           0       2,345        2,345   $    50
                           Category Penalties
                       Utilities                  10%
                       Labor                      15%
                       Other                       5%



nce Report

             90-Day    Graduated             Category
             Penalty    Penalty              Penalty
         $         0   $           200   $       1,625
                   0               100            144
                 330               100            165
                 567               200            622
                   0                0              22
                  87                0              44
                   0               100            405
                 235               100            117
                                                                                                                         Appl
                                                                                           Personal
                              College         Major                                        Interview       Work
Name                          GPA             Code          Refer-ences                    Rating          Experience
Michael                                 3.6             2                              3               5      TRUE
Brian                                   3.9            18                              3               3      FALSE
George                                  2.1            15                              3               2      FALSE
Jasen                                   3.0            30                              3               5      TRUE
Cindy                                   3.2           100                              4               3      TRUE
Sara                                    3.7             4                              3               6      TRUE
Courtney                                4.0            16                              5               4      FALSE
Scott                                   2.1           260                              2               2      FALSE
Mark                                    2.8            20                              3               5      FALSE




                                                                            Assumptions
    Job Applicants must supply the
         following information                                     Automatically disqualified                              Automa
Valid GPA min                      1.5                             If any of these are TRUE                             If all of the
Valid GPA max                        4                      Valid GPA min                          1.5
Standardized universal
Major Code                                                  Valid GPA max                              4
                                                            Valid Employment Exam
Engineering                              1                  Score min                              200

                                                            Valid Employment Exam
Business                                 2                  Score max                              800

                                                            Valid Standardized
Economics                                3                  universal Major Code min                   1

                                                            Valid Standardized
Physical Science                      4                     universal Major Code max               250
Last one                            250                     GPA less than                           2.5

Reference letters less than              3                  Reference letters less than                3
Personal Interview rating                                   Employment Exam less
less than                                3                  than                                   600
                                                            Personal Interview rating
Work Experience                 TRUE           FALSE        less than                                  3
Valid Employment Exam
Score min                           200
Valid Employment Exam
Score max                           800
Undergraduate school
ranking

Automatically disqualified
Automatically hired
Undecided   If the applicant does not fit into one of the other categories
          Application for Employment
                                  School         InvalidScores/                                 Automatically
Employment ExamScore              Rank           Major?             Automatically Disqualified Hired
                            700            12        FALSE                   FALSE                  FALSE
                            850             1         TRUE                   TRUE                   FALSE
                            780             5        FALSE                   TRUE                   FALSE
                            710             3        FALSE                   FALSE                  FALSE
                            600            26        FALSE                   FALSE                  FALSE
                            740            15        FALSE                   FALSE                  TRUE
                            720             8        FALSE                   FALSE                  FALSE
                            590            33         TRUE                   TRUE                   FALSE
                            760             6        FALSE                   FALSE                  FALSE
                                  Total                           2                           3               1
                                  Total                           2                           3               1
All are invalid                                      FALSE                   FALSE                  FALSE
Any are invalid?                                     TRUE                    TRUE                   TRUE


          Automatically hired
       If all of these are TRUE
GPA score greater than                     3.5

Major Code min                              1

Major Code max                             20

Undergraduate school
ranking less than or equal to              25

Employment Exam greater
than                                       700

Personal Interview rating
greater than or equal to                    4
Work Experience                     TRUE
Not Automatically       Not Automatically
Disqualified            Hired                   No Decision       No Decision
      TRUE                    TRUE                  TRUE              TRUE
      FALSE                   TRUE                  FALSE            FALSE
      FALSE                   TRUE                  FALSE            FALSE
      TRUE                    TRUE                  TRUE              TRUE
      TRUE                    TRUE                  TRUE              TRUE
      TRUE                    FALSE                 FALSE            FALSE
      TRUE                    TRUE                  TRUE              TRUE
      FALSE                   TRUE                  FALSE            FALSE
      TRUE                    TRUE                  TRUE              TRUE
                    6                       8                 5
                    6                       8                 5
      FALSE                   FALSE                 FALSE
      TRUE                    TRUE                  TRUE
                                                                            RJ Construction - Painting Estimator

                                                                          Square
                                                                          Feet (SF)
                                                                          of
                           Length in Width in                             Wall/Ceilin Wall      New Color
Room                       Feet       Feet       Height in Feet           g           Condition Lighter ?
Kitchen                            20         15                      8          860           2 TRUE
Bedroom1                           16         12                      8          640           1 FALSE
Bedroom2                           10         12                      8          472           1 FALSE
Bath                                8          6                      8          272           3 FALSE
 Total
 Total Discounted Price:


                                             Assumptions
        Wall Condition        NO.        Cost primer Cost of first coat
Excellent                           1              0               0.45
Reasonable                          2              0                0.5
Poor                                3            0.5                0.6

Cost per square foot for
second coat when poor           0.35
New Color Lighter ?          TRUE          FALSE                    0.3

Grade of Paint             Cost                                Discounts
Premium                           0.15                               0          0%
Economy                           -0.1                            1000         10%
Superior                             0                            2500         12%
                                                                  5000         15%
High Price                        450
Painting Estimator


                      Wall                      Second    Paint
                      Repairs & First Coat      Coat      Quality
             Grade of Primer    Painting        Painting  Adjustmen              High
             Paint    Costs     Costs           Costs     t          Total Costs Price?
             Premium         -          430        258.00    129.00      817.00    TRUE
             Economy         -          288           -      (64.00)     224.00 FALSE
             Superior        -        212.4           -          -       212.40 FALSE
             Superior    136.00       163.2         95.20        -       394.40 FALSE
                         136.00    1,093.60        353.20       65.00     1,647.80
                                                                           1483.02

                                  alternative               alternative alternative
                                     430.00                        129       1483.02
                                     288.00                         -64
                                     212.40                           0
                                     163.20                           0
                                                         PO 611 Bidder List Financial Evaluation

                                     Net Worth    D&B CCAR (1
Customer Name                         (Dollars)      Best)                D&B PAYDEX (100 Best)
BFF Industries                   $    157,795                   1                                 90
NRKK                                  387,000                   4                                 83
Pergo Molding                         775,961                   2                                 98
Allma                              17,043,973                   3                                 55
JF & Sons                          35,446,678                   1                                 32
Argree Inc.                        63,545,102                   3                                  0
RGM Plastics                       64,831,692                   2                                 85
Soto Services                     182,686,331                   1                                 73
NE Plastic                        239,227,375                   2                                 79
SPDM                              487,215,654                   2                                 97


             Assumptions (HURDLES)                                  Total outcomes =
Net Worth (Dollars)              $ 500,000.00
D&B CCAR (1 Best)                            1                      Experiments
D&B Stress Risk Class (1 Best)               1                      Outcomes for each experiment
D&B PAYDEX (100 Best)                       90
Allow to bid                                                        Exclude
Exclude
Get more data                                                       Get more data


                                                                    Allow to bid
al Evaluation
                D&B Stress
                Risk Class                                       Final
                 (1 Best)    Rule #1   Rule #2   Rule #3    Recommendation
                    1        FALSE     TRUE      FALSE     Get more data
                    3        FALSE     FALSE     FALSE     Exclude
                    1        TRUE      FALSE     TRUE      Allow to bid
                    2        TRUE      FALSE     FALSE     Get more data
                    1        TRUE      TRUE      FALSE     Allow to bid
                    1        TRUE      FALSE     FALSE     Get more data
                    1        TRUE      FALSE     FALSE     Get more data
                    1        TRUE      TRUE      FALSE     Allow to bid
                    2        TRUE      FALSE     FALSE     Get more data
                    1        TRUE      FALSE     TRUE      Allow to bid


                         8

                         3
                         2

                 FALSE       FALSE     FALSE

                 TRUE        FALSE     FALSE
                 FALSE       TRUE      FALSE

                 TRUE        TRUE      FALSE
                 TRUE        TRUE      TRUE
                 FALSE       TRUE      TRUE
                 TRUE        FALSE     TRUE
                 FALSE       TRUE      TRUE

				
DOCUMENT INFO
Description: Excel 2007 Sales Spreadsheet Examples Toy Company document sample