Re: Income and Expenditure of a small business

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Evi" wrote:

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" <martin_fishlock@xxxxxxxxxxxxxxxxxxx> wrote in message
news:0CBA0D3C-F7CC-40B0-B6BF-4D7948633DB4@xxxxxxxxxxxxxxxx
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/

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Evi" wrote:

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

















.

Relevant Pages

  • Re: advice
    ... I had to apply for income support as Ex took all monies from bank ... I rang Falkirk to ask them if they had been in touch and they said they ... The CSA can't just decide to close a case for no reason. ... years accounts have not been filed yet, ...
    (uk.gov.agency.csa)
  • Re: The rich and the taxes they pay (or avoid)
    ... Most millionaires do pay taxes. ... the wealthy tend to derive a higher portion of their income from ... high-income earners pay Social Security tax ... accounts offshore, and income from foreign tax shelters costs the U.S. ...
    (alt.smokers.cigars)
  • Re: Another one bites the dust
    ... Two accounts gone to India. ... > I had been transcribing for one doctor since 1996. ... > Now I don't have the safety net of his income anymore. ... > regular pay increases. ...
    (sci.med.transcription)
  • Another one bites the dust
    ... I've been bitten by the outsourcing bug. ... Two accounts gone to India. ... Now I don't have the safety net of his income anymore. ...
    (sci.med.transcription)
  • Re: Income and Expenditure of a small business
    ... Income (a yes no field which I tick if the item brings in income (a job, ... Category for example Asset purchase, Vehicle ... IncomePaid (how much the customer has paid us which may not be all) ... So I could add a table to my tblTransaction called AsstID which could have ...
    (microsoft.public.access.tablesdbdesign)