Download - 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?
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
2
A LIST YOU CAN MAKE SENSE OF LATER
Useful Expense List?
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
3
A list that can answer, “How much did I spend on
_______?” anytime
Useful Expense List?
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
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
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
5
Software to Use
MICROSOFT EXCEL
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
6
Open a spreadsheet
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
7
Create three columns
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
8
The “ITEM” Column
Specific Description of Item Purchased
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
9
The “AMOUNT” Column
The amount of cash disbursed or paid
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
10
The “CATEGORY” Column
The particulars of the expense summary that you want
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
11
Add a ‘nothing follows’ row. This row is very important.
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
12
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
13
Additional rows must be inserted
only above ‘nothing follows’ row
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
14
Never insert rows below “nothing
follows” row
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
15
If you violate this rule, then this won’t
work for you
Add a “TOTAL” row
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
16
Jump to cell under “Amount” and opposite “Total”
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
17
Sum all the cells above it
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
18
Select all cells under ‘Amount’ down to the Total
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
19
Click ‘comma’ icon
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
20
Double click on the tab name
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
21
Rename to ‘Expense List’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
22
Click the “+” icon
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
23
You just created a new tab
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
24
Double-click on tab name
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
25
Rename to ‘Expense Summary’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
26
Make two columns in the Expense Summary tab
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
27
Type the categories here
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
28
Make sure you have the ‘nothing follows’ row
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
29
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
30
Again, do not play around with the “nothing follows” row. It’s a rule.
Add the ‘TOTAL’ row
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
31
Sum all cells above the ‘Total’ cell under ‘Amount’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
32
Select all cells under ‘Amount’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
33
Click the ‘comma’ icon
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
34
Select all categories, including ‘nothing follows’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
35
Click ‘Insert’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
36
Hover your mouse on ‘Name’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
37
Click ‘Define’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
38
You’ll see this box
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
39
Rename to ‘CATEGORY’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
40
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
41
CATEGORY is now a name for a range
of cells
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
42
CATEGORY is a range of cells containing the
expense categories
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
43
You will use the CATEGORY range
name later.
Click ‘OK’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
44
Click Expense List Tab
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
45
Select cells under ‘Category’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
46
Click ‘Data’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
47
Click ‘Validation’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
48
Click menu for ‘Allow’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
49
Choose ‘List’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
50
Type ‘=CATEGORY’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
51
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
52
You remember CATEGORY, right?
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
53
That’s the name of the array containing
the expense categories
I mean, these . . .
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
54
Click ‘OK’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
55
Click this icon
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
56
These are the expense categories you defined earlier
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
57
Click ‘Supplies’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
58
No need to type, right?
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
59
Try typing something else
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
60
It won’t be accepted
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
61
Click ‘Cancel’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
62
Highlight the cells as shown
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
63
Click “Insert” from main menu
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
64
Select “Name”
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
65
Click “Define”
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
66
Name it as “Expenses”
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
67
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
68
The EXPENSES cell range contains two columns. You will use this later.
Click ‘OK’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
69
Highlight cells under ‘Amount’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
70
Click ‘Insert’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
71
Select Name-Define
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
72
Name the range “Amount”
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
73
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
74
The AMOUNT cell range contains only one column. Again,
you will use this later.
Click ‘OK’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
75
Encode the expenses now
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
76
To add rows, highlight rows from ‘nothing follows’ down
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
77
Click “Insert”
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
78
Click “Rows”
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
79
Continue encoding
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
80
Once done, click Summary
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
81
Go to cell B2
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
82
Encode the SUMIF() formula
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
83
Values we will use
range = EXPENSES
criteria = {cell to the left}
sum_range = AMOUNT
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
84
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
85
Of course, you recall EXPENSES
and AMOUNT, right?
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
86
They are cell ranges that we defined earlier
Our formula looks this way
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
87
Click on the cell
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
88
Click and drag down
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
89
That’s your live summary
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
90
Click ‘Expense List’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
91
Add a row
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
92
Add another entry
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
93
Click ‘Expense Summary’
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
94
Updating is automatic
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
95
Spice it Up if you like with percentages
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
96
Or, spice it up with budgets and variances
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
97
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
98
Set it up once
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
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
Dec 10, 2010 Sid Macatol - How to Create a Useful Expense List
100
