Excel for receipts accounting?

carveone

Hi all,

I've been given a task to do for a charity organisation in Africa (no, I'm not being paid!). There's a big pile of receipts for water and sanitation projects and I guess I'm supposed to sort this out somehow. Apparently I know computers therefore I know Excel, which doesn't follow but I'll try. I'm trying in OpenOffice as I'm sitting at a Camara computer running Ubuntu. It's not bad for a P4?!

I guess the wanted result is a breakdown of project costs for things like materials, equipment, construction, transport, labour etc. The materials could be broken down by pipes,roofing, fasteners,etc.

I'm scratching my head on where to get started. I can make a big mess that gives the right answers but when I leave that creates an maintenance issue. I'm not sure Excel is the right tool for the job - GnuCash is on the Applications menu but I haven't used it before.

I have many receipts like:

Addil's Corner
23/04/2013
Qty    Description            @    Amount
--------------------------------------------
1      1 1/2" PVC Elbow       15       15.00
40     wood screws            .40      16.00
1      waste pipe             57.50    57.50
2      1/2" GI Pipe           93.50    187.00
--------------------------------------------
                              Total    275.50

I can class these items into plastic pipe, gi pipe, roofing, doors, fasteners and then big items like gates, basins, taps etc and use "validity" to get a drop down list of those types. My thinking is to force a database view of things - firstly so I can do a kind of "select from * where type = "pvcpipe" and secondly so someone else doesn't have the option of misspelling "pvcpipe" and making an total mess of things. And then sum all the types in some way. I initially tried it the database way - types have a type id and a type string but that seemed to be a pain - Excel isn't a database.

That's as far as I've got but I'm still struggling through. Should I throw all these receipts into one sheet? The totals are just used to verify the receipt so I could put them to the side somehow. I'm just not sure how to lay this out. And then I've no idea how to do the "Sum of all entries in Column G where type in Column C is "pvcpipe"...

Thanks!

PrzemoF

Try OpenOffice/LibreOffice Base (it's like MS access).

You'll be typing in the data from the receipts or you have it in an electronic form?

Overheal

Database, not spreadsheet I think. Either way, it sounds like it will be a mess to figure out without more specialized software. Try installing a free trial of YNAB and see if that makes life easier on you, you can set up different expense categories and its designed for budgeting.

carveone

I agree with both of you - a database or appropriate financial package like GnuCash or YNAB would be way way more appropriate. Especially given that there is no electronic anything. Zip. There's just a big pile of paper in front of me

Excel/OpenOffice Calc is what non-accountant type people are used to though so I might produce a big mess before showing them the alternatives. Thanks.

PrzemoF

It might be worth to design a good data entry form: http://www.techrepublic.com/blog/doityourself-it-guy/diy-create-data-entry-forms-for-databases-in-libreoffice-base/1331

superb choice of username

I've had to do this once or twice myself. For me, spreadsheets all the way - simple to manipulate the data, make changes, and never really learned how to use a database program!

If you were to carry on with a spreadsheet, the simplest way it seems, would be to have one sheet to list everything. Have set columns etc. If, for example, column A was date, B type, C amount, it would just be a 'sumif' calculation. So, then in column e, you would make a list of the transaction types (material/labour - whatever other ones you want. So say, materials is in e1, the formula in f1 would be =sumif(b:b, h1, c:c)

This would search the entire B column for every transaction which you had called 'materials', and then add the corresponding amount from the C column. Then just drag that formula down to copy - probably easier if I just show you with an attachment lol (although its not in ods as boards wont let me attach that...).

In presentation terms, you could have one sheet for all the raw data, and then a second sheet to present some kind of accounts - would be the same sumif, but you'd be able to make it a bit more better looking! But that's something which you can work on after!

KAGY

If you are going the openoffice calc or excel route you need one massive sheet with your raw data and other columns for any categorisation you need to do ( e.g project, accounting period etc)
Then to get your summary or report look at data pilot (in open office calc or pivot tables in excel). It looks difficult at first but its not really. It boils down to choosing row and column headings for a summary table.
E.g. raw data
Receipt no / date/ item/ cost /project /category
1 / 1-Jan / pipes/ 100 / building a / material
1 / 1-Jan / pipes/ 100 / building b / material
2 / 1-Jan / pumps/ 100 / building a / material
3 / 1-Jan / stationary / 100 / building a / admin

Pivot this is created / updated automatically
/ Building a. / building b
Material / 200. / 100
Admin / 100. /0
Total / 300 / 100

Hope that helps. Its not as powerful as a database but it coukd be imported later. It also involves a bit of duplication I.e. receipt dates/suppliers are reentered on each line for each receipt.

If you are on a Linux system try kmymoney. The interface is easier to follow and learn than gnucash. If you are heading that route let me know and I'll see if I can help. I've some (limited) experience with both

Edit: the first part of this is the same as superb's method above but the pivot table is more powerful and flexible as it will automatically add new categories and can use several layers of subcategories. But the basic idea of getting all the raw data into a sheet in a consistent manner is key to both.

RainyDay

Forget about the choice of tool and understand the business requirement first. The key issue is whether you need to record the data at receipt level (i.e. the total of each receipt) or at receipt line level (i.e. the individual items on each receipt). This probably depends on the variety of stuff on each invite. If you have food and roofing materials on the same receipt, then you need to record at receipt line level, and assign a category to each line. If you need to record at receipt level, that will mean a lot less data entry, and you assign a category to each receipt.

Excel should suffice either way. Forget about totals until you get the data entered. Then you can use pivot tables or sorting/totalling to give you the totals you need.

carveone

Actually, after some time thinking about it, I ended up with exactly what Superb has described with sumifs. I created a Data/Validation list to contrain the types to strings I want - that way people working after me don't get to type in a type of "Pterol" (instead of Petrol) and create confusion.

I also came to conclusion that, as RainyDay said, I was thinking too much about the tool and not about the actual end requirement - typical over engineering. Unfortunately the receipts are sometimes (but not always) mixed with different line items. But I can still categorise a lot of them like the way RainyDay describes - There's a whole bunch of them which are just petrol and a whole bunch which are just lunch. None which have food and roofing on the same receipt

Thanks guys - I'll bash ahead and stop thinking about turning Excel into an accountancy program. Someone else's job!

Edit: Thanks KAGY - I'll checkout kmymoney too...

KAGY

Now that you have some data in, select it and the headings and try insert pivot table. On the sidebar that pops up drag the headinss into the relevant sections and the cost into the value/data? Section. You may need to right click to change the summary opt from count to sum.
Not trying to push this way on you, but it was a bit of a wow moment when I discovred them many years ago.