Income and Expenditure of a small business

[Evi]

I'm designing a database to keep track of the income and expenditures of a
small gardening business. Can you give any advice on my main table?

The tables I have are
tblItems
ItemID (Primary Key)
Items (these that be things that either bring in money like a job or cost
money like paperclips)
CatID (linked to category table
TaxCatID (linked to a tax category table since items might need to be
grouped/added differently when filling in a tax return.
Income (a yes no field which I tick if the item brings in income (a job,
equipment loan) or leave blank if the item costs money

tblCategory
CatID
Category for example Asset purchase (when we buy a chain saw), Vehicle
purchase (when we buy a vehicle), Asset Cost (repairs, spare parts), Vehicle
costs (petrol, MOT, Road tax, repairs etc) Stationary, Building Costs (rent,
lighting), Staff Costs (wages,

tblTaxCategory (similar to above but provides a different way of grouping
the items)

TblTransaction
TransID
ItemID
Expend (how much money has to be spent)
ExpendPaid (how much money has actually been spent - we may not have paid it
all yet)
Income (How much a customer owes us)
IncomePaid (how much the customer has paid us which may not be all)
TransactionDate
(and a few other fields to do with transactions

Now comes the questions.
For buying paperclips or paying the rent the above fields are enough but now
come stuff to do with Assets like tools and vehicles and office computers.

They need their own set of fields
(vehicle registration), (date of purchase), (mileage),
So I might have an Item which is a chain saw blade but I would need to know
that it was bought for my Black and Decker chainsaw or my Bosch one.
If I have an item called VehicleMOT, I need to know which vehicle I bought
it for.
So I could have an Assets table which contains such things as
AsstID
Asset
Model/Reg
DateOfPurchase
PurchaseCost
StartMileage
So I could add a table to my tblTransaction called AsstID which could have a
default value of 1 (this Asset is None) and include fields in the
tblTransaction where I fill in Mileage if I require it or LitresPetrol for
when I fill up the tank of that vehicle

Then comes the income from Jobs
So I have a Customer table.
So do I include CustomerID in tblTransaction where I can record the customer
for which I have done the job? Again the Default value of CustomerID refers
to None.

And now for paying wages to my 2 staff
I have a staff table
So do I also include in TblTransaction a StaffID field where I can pay my
staff. I may also need to include such info as the hours worked, his hourly
rate on that date, a Bonus field for any extra I choose to pay him. So now
the Expend field is actually a calculated field (hours worked * hourly rate
+ bonus) I would also need to include an NI field to record his national
insurance contributions.

I tried having completely different sets tables from TblTransactions for
Jobs and Wages and Asset Expenses. It made it easy to record the info but it
made the Reports difficult to set up with lots of subreports and meant that
the customer would find it difficult to set up his own reports at a future
date since he would need to refer to Controls in Subreports in his
calculations.

So do I leave my TblTransaction as an all purpose table and just use those
fields that are required in the appropriate forms with the rest being filled
in with default values that can be filtered.
Or do I have a JobsLoans table which whenever I choose an Item in the Income
category would automatically fill in an entry in the JobsLoans table and
this would contain the extra fields (customer ID, AssetID for when customers
are loaned eg a chainsaw)
The Income and IncomePaid would still be in tblTransaction so I can keep a
running balance in my report.

I could do something similar with expenditures on Assets where my
AssetExpend table would hold the extra details like Mileage, AssetID

Can you foresee any snags with doing this latter or would you recommend
keeping all the various types on transaction and their fields in
TblTransactions and just showing the necessary fields in purpose-build
forms?

What would you recommend yourself?

Evi

 

[Jeff Boyce]

Evi

You have clearly given this considerable thought and effort. Are you
willing/able to commit a lot more to the development and support of this
application?

Or are you interested in having a way to track income and expenditure of a
small business?

If the latter, there are a number of shrink-wrap commercial accounting
packages sufficient for most small businesses' accounting needs. Do you
really want to spend your time reinventing what they've already done?

(if so, you've come to the right place ... I'm sure you'll get a lot of
folks looking at your post and offering their ideas. You did ask for our
ideas, right?!)

Regards (and good luck!)

Jeff Boyce
Microsoft Office/Access MVP

 

[Martin Fishlock]

Evi,

I can only agree with Jeff and Duane. Use an accounting package.

They generally have all the requirements to meet the legal and tax
requirments for business control and reporting as required by your
accountants.

In general you would need four/five ledgers:
General Ledger
Accounts Receivable
Accounts Payable
Fixed Assets
(Projects)

Each of these requires extensive work to get a system working that is
reliable and which you could rely on.

You will generally find that with some systems you can have cost centers or
project centers where you can charge expenses to and record income.

I suggest that it you want to proceed then take a look at Barry William's
DAtabase Answers site http://www.databaseanswers.org/data_models/index.htm

or you could try GNU Cash package at try http://www.gnucash.org/

 

[Evi]

Thank for your kind advice, all of you. You've all been such an inspiration
to me over the years. I'd love to have a go at designing something myself
that would be tailor made for this particular small business if only for the
interest of doing it but will also pass on the advice that my aquaintance
could buy an accounting package. Anything will be better than his current
system of writing on paper and hoping that he'll be able to somehow sort it
out at the end of the tax year.
Any advice on my original question about the advisability of having a
transaction table and using coded forms to put an entry into a linked table
if a particular type of transaction happens? eg if the user chooses the Item
Jobs which comes in the Income category, that TransID will be appended to
the Income table and a new form will open automatically allowing him to
enter the extra fields like CustomerID which he requires for jobs. There
are probably snags with doing this which I can't foresee yet.
Evi

 

[Martin Fishlock]

I would have a structure starting from a chart of accounts (coa) that lists
all the accounts and that can show if they are income, expense, liability or
asset.

You then could have transactions and this would have a posting date, period
and year, seq number, account, net amount, tax amount, customer no/supplier
no/cash account.

You then would have tranactions details which distrubutes the amount above
to the individual accounts : year, period, seq number, dr/cr, amount
account, details

That should get you started once that is working you need to consider the
A/R A/P and fixed assets and maybe a cash book. But start on the general
ledger first.

Good luck.