Holding company: Automatically modifying data on different workshe

[JTep]

To whoever who can help,

If anyone can give me a hand for this it would be greatly
appreciated, I am a quick learner but I lack the technical Excel know-how to
do what it is I need to do.

Allow me to explain the situation: I work for a holding company. We
manage several small businesses and some larger ones. What I need to do is
put in place an accounting system to keep track of each company including the
holding company, mostly pertaining to expenses and revenues.

If I am not mistaken, the simplest way to go about is to have the
holding company and the rest of the companies on one book. Each sheet is a
different company and an identical template is used for each sheet. I am
already using a template that ressembles a petty cash template but I need to
add-on more. To give you an idea of what kind of formula I will be needing,
I can give you an example of a situation that may arise:

-Company A has just been established. We, as the holding company, suggest
that they buy insurance. They accept. We undergo all the procedures for them
to get insurance. The insurance premium costs 450$. We pay this 450$ for
them. So it has to be debited off sheet 1 (the holding company's sheet) as an
expense for us towards Company A. Then it has to be debited off Company A's
sheet + 15% (which is what we charge for services). So in conclusion it's:
-450$ on the holding company's sheet, -450$ on Company A's sheet and
eventually +450$+67.5$ on the company's holding sheet (which is 15% of $450
for services rendered).

On the holding company's sheet, I need to be able to type in the name of a
company when i enter an amount (WHETHER EXPENSE OR REVENUE) and the numbers
to automatically be reflected on that company's sheet and then back again on
the holding sheet with the added 15%.

I hope this is clear. Please do not hesitate to ask more questions. Anyone
who has some useful input would be appreciated.

Sincerely,

Jon

 

[Duke Carey]

Can you do this in Excel? Yes.
Should you do this in Excel? No!!! This is fraught with risk. The
difficulty in protecting and validating data, the risk of errors, etc.

You need an accounting package. Quickbooks is one, and Microsoft has a new
Express accounting package that is free. There are plenty of others out
there, too.

If you insist on proceeding with Excel, then put ALL inputs on a single
sheet and use advanced filtering to pull out the data you need for individual
company reports

 

[JTep]

I understand your concern, but what I am looking to do does not concern THAT
much data nor information. It would only be expenses like the ones i
mentionned nothing much more. Do you still think I should not use Excel?

 

[Duke Carey]

Excel is my all-time favorite application - a great tool. However, it isn't
the best tool for every job. Its strength is in its analytical power. Yes,
you can maintain data within Excel, but that's like using a hammer to drive
screws. You'll get the screw in but will likely have problems down the road.
It's just so easy in Excel to accidentally delete a row, or paste something
over top of an existing value.

Short of an accounting package I'd recommend MS Access or SQL Server Express
(free) for storing your data, then use Excel to pull in the data you need and
generate the reports.

 

[JTep]

Ok. Thanks for your time Duke. I'll see what others have to say. Although I'm
even less familiar with Access than Excel, I'll try and give it a shot to see
how it goes. Thanks again.

Jon