Excel Formula Exercises Budget by yth51367

VIEWS: 101 PAGES: 9

• pg 1
```									                   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
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
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
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