how to create a useful expense list

How to Create a Useful Expense List

Sid Macatol

Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List

1

Useful Expense List?


2

A LIST YOU CAN MAKE SENSE OF LATER



3

A list that can answer, “How much did I spend on

_______?” anytime



4

1. Small scissors … P20!2. Adhesive tape … P30!3. Jollibee meals for 3 … P300!4. Jeepney fare … P35!5. Cartolina … P30!6. Printer ink (HP60) … P800!7. Bond paper, 1 ream … P270!8. Messenger, 1 day … P350!9. Gasoline for messenger … P100!10. Styrofoam (2x2x1), 10pcs … P220!11. Elmer’s Glue (large), 2 bot …

P124!12. Pepsi Large … P40!13. KFC meals for 3 … P300!14. Taxi … P150!15. Thumb tacks … P50!

REAL TIME SUMMARY Materials … P _______? Supplies ... P _______? Transportation …. P_______? Food … P_______? Others …. P_________?

ARE YOU WITHIN BUDGET?

Where do we use Expense Lists? Household Expenses

Project Expenses

Personal Expenses


5

Software to Use

MICROSOFT EXCEL


6

Open a spreadsheet


7

Create three columns


8

The “ITEM” Column

Specific Description of Item Purchased


9

The “AMOUNT” Column

The amount of cash disbursed or paid


10

The “CATEGORY” Column

The particulars of the expense summary that you want


11

Add a ‘nothing follows’ row. This row is very important.


12


13

Additional rows must be inserted

only above ‘nothing follows’ row


14

Never insert rows below “nothing

follows” row


15

If you violate this rule, then this won’t

work for you

Add a “TOTAL” row


16

Jump to cell under “Amount” and opposite “Total”


17

Sum all the cells above it


18

Select all cells under ‘Amount’ down to the Total


19

Click ‘comma’ icon


20

Double click on the tab name


21

Rename to ‘Expense List’


22

Click the “+” icon


23

You just created a new tab


24

Double-click on tab name


25

Rename to ‘Expense Summary’


26

Make two columns in the Expense Summary tab


27

Type the categories here


28

Make sure you have the ‘nothing follows’ row


29


30

Again, do not play around with the “nothing follows” row. It’s a rule.

Add the ‘TOTAL’ row


31

Sum all cells above the ‘Total’ cell under ‘Amount’


32

Select all cells under ‘Amount’


33

Click the ‘comma’ icon


34

Select all categories, including ‘nothing follows’


35

Click ‘Insert’


36

Hover your mouse on ‘Name’


37

Click ‘Define’


38

You’ll see this box


39

Rename to ‘CATEGORY’


40


41

CATEGORY is now a name for a range

of cells


42

CATEGORY is a range of cells containing the

expense categories


43

You will use the CATEGORY range

name later.

Click ‘OK’


44

Click Expense List Tab


45

Select cells under ‘Category’


46

Click ‘Data’


47

Click ‘Validation’


48

Click menu for ‘Allow’


49

Choose ‘List’


50

Type ‘=CATEGORY’


51


52

You remember CATEGORY, right?


53

That’s the name of the array containing

the expense categories

I mean, these . . .


54

Click ‘OK’


55

Click this icon


56

These are the expense categories you defined earlier


57

Click ‘Supplies’


58

No need to type, right?


59

Try typing something else


60

It won’t be accepted


61

Click ‘Cancel’


62

Highlight the cells as shown


63

Click “Insert” from main menu


64

Select “Name”


65

Click “Define”


66

Name it as “Expenses”


67


68

The EXPENSES cell range contains two columns. You will use this later.

Click ‘OK’


69

Highlight cells under ‘Amount’


70

Click ‘Insert’


71

Select Name-Define


72

Name the range “Amount”


73


74

The AMOUNT cell range contains only one column. Again,

you will use this later.

Click ‘OK’


75

Encode the expenses now


76

To add rows, highlight rows from ‘nothing follows’ down


77

Click “Insert”


78

Click “Rows”


79

Continue encoding


80

Once done, click Summary


81

Go to cell B2


82

Encode the SUMIF() formula


83

Values we will use

range = EXPENSES

criteria = {cell to the left}

sum_range = AMOUNT


84


85

Of course, you recall EXPENSES

and AMOUNT, right?


86

They are cell ranges that we defined earlier

Our formula looks this way


87

Click on the cell


88

Click and drag down


89

That’s your live summary


90

Click ‘Expense List’


91

Add a row


92

Add another entry


93

Click ‘Expense Summary’


94

Updating is automatic


95

Spice it Up if you like with percentages


96

Or, spice it up with budgets and variances


97


98

Set it up once


99

Enjoy its convenience for life

Thanks for watching the slides.

Polite comments are welcome at http://virtualsidekick.blogspot.com

You may follow me at: http://www.google.com/profiles/sid.macatol

Dream a life! Live your dreams!

Sid Macatol


100

how to create a useful expense list

Self Improvement