Excel Formula Exercises Budget by yth51367

VIEWS: 101 PAGES: 9

More Info
									                   Budget
Item               Jan.   Jan.   Jan.         Percent
                   Budget Actual Difference   Of Budget
EXPENSES
Utilities            1000   900
Equipment Rental      500   600
Rent                 1500 1500
Marketing             200   200
Salaries            15000 14750
Office Supplies       750   875
TOTAL EXPENSES

INCOME
Fees                22000 23500
Retainers            5000 4000
TOTAL INCOME

Profit/LOSS
BudgetJan2007 (an)                                      2/3/2011 - 9:30 AM                                         excelifun

                                                                 Budget
                                              Jan.              Jan.               Jan.            Percent
                     Item                     Budget            Actual             Difference      Of Budget
                       Utilities                    $1,000.00          $900.00            $100.00         90.00%
                       Equipment Rental                500.00            600.00           -100.00        120.00%
                       Insurance                       500.00            450.00             50.00         90.00%
                       Rent                          1,500.00          1,500.00               0.00       100.00%
                       Marketing                       200.00            200.00               0.00       100.00%
                       Salaries                     15,000.00         14,750.00            250.00         98.33%
                       Office Supplies                 750.00            875.00           -125.00        116.67%
                     TOTAL EXPENSES                $19,450.00        $19,275.00           $175.00         99.10%

                      Fees                         $22,000.00        $23,500.00        $1,500.00        106.82%
                      Retainers                      5,000.00          4,000.00        -1,000.00         80.00%
                     TOTAL INCOME                  $27,000.00        $27,500.00         $500.00         101.85%
                     PROFIT/LOSS                    $7,550.00         $8,225.00         $675.00         108.94%

                                                            Jan.     Jan.
                                                            Actual   Budget


                          Office Supplies
                                 Salaries
                              Marketing
                                    Rent
                               Insurance
                        Equipment Rental
                                 Utilities

                                             $0        $4,000             $8,000       $12,000       $16,000



                                                            Page 2 of 9
Child Support Payments - Allen v. Allen
Beginning Balance                        1000
Monthly Payments                          750
Monthly Interest Rate =                   0.01
Interest = Accrued Arrearage = Previous Arrearage + Previous Interst + Support Due - Support Paid
Month                      Support Due         Support Paid       Accrued Arrearage Monthly Interest
Beginning Bal Jan. 2008
February                                                     100
March                                                        500
April                                                           0
May                                                          250
June                                                         750
July                                                            0
                                  Child Support Payments - Allen v. Allen
Beginning Balance                                      1000
Monthly Payments                                        750
Monthly Interest Rate =                              1.00%
Accrued Arrearage = Previous Arrearage + Previous Interest + Support Due - Support Paid
Month                                    Support Due         Support Paid      Accrued Arrearage     Monthly Interest
Beginning Bal Jan. 2008                                                         $         1,000.00   $           10.00
February                                  $         750.00 $           100.00 $           1,660.00   $           16.60
March                                     $         750.00 $           500.00 $           1,926.60   $           19.27
April                                     $         750.00 $               -    $         2,695.87   $           26.96
May                                       $         750.00 $           250.00 $           3,222.82   $           32.23
June                                      $         750.00 $           750.00 $           3,255.05   $           32.55
July                                      $         750.00 $               -    $         4,037.60   $           40.38




                                                                                 Some Notes:
                                                                   Open Format Cells dialog box with Ctrl + 1
                  An alternative to Merge and Center is Center Across Selection (Alignment tab in Format Cells dialog box - under Alignment Horizontal
                                                               If you use a dark Fill color, use a light Font color
                                                          Pointing to the edge of a cell allows you to move the cell
                                  Holding Ctrl key while selection cells allows to select non-contiguous cells (cells to next to each other).
                                                                                 Ctrl + B = Bold
                                                      With your cursor in a table of data, Ctrl + A will select the table
                                                                       Formulas start with an equal sign
                                          Formula inputs are numbers type into cells that formula point to with Cell References.
            In this example we grouped our Formula Inputs together. This makes it convenient to change them when you use the template for a new client.
                    Cell References used in formulas, like this: =B5-C5, are convenient because if you change the formula inputs, the formula updates

  Relative Cell References point a certain number of rows up or down and columns left or right relative to the cell with the formula. When you copy a formula w
                   Relative Cell Reference that is looking "two cells to the left", it will always look "two cells to the left", no matter where you copy it.
                        Absolute Cell References mean that the cell reference is locked during the copy action. Use the F$ key to add the $ signs.
                                                             Alt + = is keyboard shortcut for the SUM function.
                                                                       F2 puts your cell in Edit Mode
The tiny black box in the lower right corner of a highlighted cell is called the Fill Handle. When you hold your cursor over it, the cursor changes to a Cross Hair cu
 ("Angry Rabbit"). The "Angry Rabbit" can be used to drag and copy the formula, or it can be double-clicked to automatically copy a formula down when the is
                                                               content below, to the left or two the right.
 When you copy a formula down a column and you don't want the formatting that exists in the cell to be removed, Click on the Smart tag and point to "Fill with
                                                                                 Formatting"
Currency Number format has a floating $ sign. Accounting Number format has a fixed $ sign and always has the decimals lined up. Comma Style (Accounting wit
                                                             dollar sign) is good because it reduced clutter.
           Some Notes:
 Format Cells dialog box with Ctrl + 1
 s Selection (Alignment tab in Format Cells dialog box - under Alignment Horizontal
 e a dark Fill color, use a light Font color
he edge of a cell allows you to move the cell
s allows to select non-contiguous cells (cells to next to each other).
           Ctrl + B = Bold
 r in a table of data, Ctrl + A will select the table
 ormulas start with an equal sign
s type into cells that formula point to with Cell References.
 . This makes it convenient to change them when you use the template for a new client.
5, are convenient because if you change the formula inputs, the formula updates

wn and columns left or right relative to the cell with the formula. When you copy a formula with a
o the left", it will always look "two cells to the left", no matter where you copy it.
 eference is locked during the copy action. Use the F$ key to add the $ signs.
 keyboard shortcut for the SUM function.
 F2 puts your cell in Edit Mode
alled the Fill Handle. When you hold your cursor over it, the cursor changes to a Cross Hair cursor
y the formula, or it can be double-clicked to automatically copy a formula down when the is cell
nt below, to the left or two the right.
 formatting that exists in the cell to be removed, Click on the Smart tag and point to "Fill without
          Formatting"
 format has a fixed $ sign and always has the decimals lined up. Comma Style (Accounting with no
gn) is good because it reduced clutter.
Age Discrimination Statistical Analysis
John Snow, Plaintiff

                                 Units Sold
  Age    Sales Person                  2007                 Statistical Summary   Answer
   32    Stephen Anderson           150       Average Employee Age
   24    James Stanton              600       Most Units Sold
   23    Alice Kelly                678       Least Units Sold
   33    Rhonda Levin               389       Average Units Sold
   56    John Snow*                 477       Total Units Sold
   20    Jennifer Hartley           563       Standard Deviation Units Sold
   40    Roland Light               450
   22    Rebecca Dawson             178
   22    Abby Roads                 333
   25    Michael Morrison           567
   28    Pam Barber                 402
   35    Peter Mitchell             450
   29    Carl Somers                700
*Terminated for Not Meeting Sales Quotas
Age Discrimination Statistical Analysis
John Snow, Plaintiff

    Age     Sales Person             Units Sold 2007                                        Statistical Summary                      Answer
     32     Stephen Anderson              150                                 Average Employee Age                                       30
     24     James Stanton                 600                                 Most Units Sold                                           700
     23     Alice Kelly                   678                                 Least Units Sold                                          150
     33     Rhonda Levin                  389                                 Average Units Sold                                        457
     56     John Snow*                    477                                 Total Units Sold                                        5,937
     20     Jennifer Hartley              563                                 Standard Deviation Sold                                   163
     40     Roland Light                  450                                                                                                                                                                                   150   1   2
     22     Rebecca Dawson                178                                 Mean (Average)                                      457                                                                                           600   1   2
     22     Abby Roads                    333                                 lower                                          293.25                                                                                             678   1   2
     25     Michael Morrison              567                                 upper                                          620.14                                                                                             389   1   2
     28     Pam Barber                    402                                 68% of values lie within +/- 1 standard deviation from the mean, or between the values 293.25 and 620.14                                          477   1   2
     35     Peter Mitchell                450                                                                                                                                                                                   563   1   2   456.6923
     29     Carl Somers                   700                                                                                                                                                                                   450   1   2
*Terminated for Not Meeting Sales Quotas                                                                                                                                                                                        178   1   2

                                                                                                                                                                                   What Standard Deviation Means                333   1   2
                                                                                                                                                                                                                                567   1   2
                                                                                                                                                                                              Visually                          402   1   2
                                                                                Some Notes:                                                                                                                                     450   1   2
                                                                    Formulas start with an equal sign                                                                                        Raw Data    Average                700   1   2
 AVERAGE calculates the Mean (add them all up and divide by the count). An Average is a single numbers that represents all the data points - a typical value that
                            can be used in discussions. How do you know if the mean fairly represnets all the data points? Standard Deviation
STDEV calculates standard deviation for a sample (some of the values, not all). STDEVP calculates the standard deviation for a population (all the values). Standard
                                                                                  Deviation
                                           To name a range, highlight the range, click in the name box, type a name, hit enter
Use Ctrl + F3 to see a list of names and to edit names. If you highlight the range and the name in a cell directly above the range, the keyboard shortcut Ctrl + Shift +
                                                                          F3 will name the range.
                                                              F3 is the keyboard shortcut for Paste Name.                                                                  100   200   300      400     500   600   700   800
                                                                      F2 puts your cell in Edit Mode
 Currency Number format has a floating $ sign. Accounting Number format has a fixed $ sign and always has the decimals lined up. Comma Style (Accounting with
                                                            no dollar sign) is good because it reduced clutter.
For more about basics stats, see these videos:
Excel Magic Trick #243: MEAN MEDIAN MODE STDEV Histogram
Highline Excel Class 29: Basic Statistics Numerical Measures

								
To top