Excel 2003 Workbooks

[jon72]

We are a small business working with Excel 2003. I am wanting to manage our budgets better and would like help with the following.

When we pay out expences we record this on a worksheet for how we paid e.g credit card, bank transfer etc. Each expense is given a code eg MOTOR for car expenses. I want to be able to have a seperate worksheet where, when I record all the information on the individual workbooks this updates a single workbook.

This will make it easier for me to be able to budget as even if I've paid out motor expenses by credit card and then, at a later date paid for more expenses by cash when I input this it will update the one single record and make it easier for me to keep on top of the global amount spent.

Thanks in advance for your help.

Jon

 

[pedro-egoli]

Jon, On worksheet where you record your expenses do something like this.

In cells say E2:E9 record the expense categories. In cells D2:D9 record a symbol (code for) like “F” for Fuel etc to coincide with the expense category in column E.

The following is what a worksheet I have for recording of trip expenses

Excel Workbook
	C	D	E	F	G	H	I	J	K
10	*	*	*	*	*	*	Fuel	Details	Amount
11	Date	Code	Description from code	Paid to	Visa (V),Cash (C )	Paid by cash(C )	Litres	speedo	spent
12	8-07-09	l	Liquor	dan murphy gold coast	v	*	*	*	$368
13	8-07-09	l	Liquor	marina quays	v	*	*	*	$87
14	8-07-09	m	Meat	Butcher shop oxenford	v	*	*	*	$74
15	8-07-09	o	Other	rolls $10 CARavan electrics	c	*	*	*	$25
Trip costs

Please disregard heading for column H as it is not needed.

Now on your summary sheet set it up with formulae like this

Excel Workbook
	G	H	I	J
11	Summary of costs for trip up to	*	*	12-Oct-09
12	*	Accomodation	*	$2,133.35
13	*	*	Fuel	$3,577.22
14	*	Groceries	*	$1,613.18
15	*	*	Liquor	$1,126.56
16	*	Entertainment	*	$2,680.80
17	*	*	Trips	*
18	*	*	Other	$2,108.44
19	*	*	Meat	$350.64
20	*	*	*	*
21	*	*	TOTAL	$13,590
Summary

You will note that Column G of Trip Cost worksheet has a letter C for Cash and V for Visa
To transfer this to Summary sheet do something like this

Excel Workbook
	L	M
11	BY CASH	VISA
12	2450.6	$11,140
Summary

Good luck

Pedro

 

[jon72]

Hi Pedro,

Thanks for taking the time to post. The details you have supplied are fantastic.

Many thanks again.

All the best,

Jon

 

[pedro-egoli]

Pleased to have been of assistance.
The spreadsheet I developed was with the aid of forum members

Pedro