Income Statement Written in Access

[bharlow]

Has anyone ever written a P&L or Balance Sheet using Access reports? This is going to sound pretty bad but... how do I add a row to the report for each line item?

My table looks like something this...

DESC ACT BUD VAR
SALES 100 90 10
COGS 50 40 10
GROSS 50 50 0

The report gives me a bound control for DESC and pulls in all the records but I need each row to be formatted differently and I need to be able to put in spaces between rows

If anyone has a report example that would be wonderful. Any suggestions would also be greatly appreciated.

 

[dkinley]

I don't have an example of this per se, but what I think you would want to do is to create subreports. I will try to give you the idea in narrative.

Let's say you create different reports, one called rBALa_AssetsSubreport and formatted that like a Balance Sheet looks for that half. You create another called rBALb_EquityLiablitySubReport and formatted it for the other half. Now to get the data in these bubbas, you create two queries: qBALa_AssetsSubReport and qBALb_EquityLiablitySubReport and set the reports.

Now, you create yet another report, call it rBAL_BalanceSheetReport and you make a corresponding query for all the extraneous stuff you need on it (i.e., company name, etc.) and name it qBAL_BalanceSheetReport.

Here's a critical piece - in each query you ensure you have the primary (ID field) and foreign keys if designed that way for linking all of these together (just like you would a subform).

Design mode rBAL and drag and drop rBALa and rBALb over on it. Remove the control names attached to the sub, set the borders to transparent, etc. Move them around so they look like a traditional balance sheet statement. Critical move is to check the properties of the subreports. Here, you will link the subreports to the main report (just like subforms to a main form).

Now, when you call the report - especially if you filter to one record (ie, one ID) you should only get one P&L out of it.

Note that I name my reports things like rBAL, rBALa, rBALb. This is so they group together in the database window and line up nice and pretty. I also name my queries my report names (replacing r with q) for the same effect and let me know which query goes with what. Most critical, this all hinges on the design of your db to make it successful.

Hope that helps.
-dK

 

[bharlow]

Thanks Dkinley.

I was hoping to avoid sub-reports but it might be the only alternative. This P&L has 200 lines and has about 40 sections.

I am hoping there is a way to include all line item descriptions in the detail section and pull only the data corresponding to each row/line item description if that makes any sense.

 

[ajetrumpet]

Bharlow,

I'm not sure what your setup is, but the first income statement I ever worked with in the same form, line by line, that your table was in, and we were sending them to auditors in Excel files. I really think Excel is better for this stuff. Just my 2c

 

[dkinley]

In light of further information, I would agree with Ajetrumpet. In situations similar to these, I've used the following type of set up.

I would place a directory with my application and call it \Templates. In this I would place Excel files and format them all nice and pretty so that if the 'blanks were filled in' all I would need to do is print or send.

I would then use a function to copy a template out of that directory and save it with a unique name (date based) in an \Output directory. A query would pull and calculate all of the data necessary and the function would then 'fill in the blanks' on the copied spreadsheet.

The basis for my function was a tweaked version of this one.

If you understand the logic of nested iterations, this function will select the sheet and specific to place the data in, but the section (queries, calculations, etc) better be set because modification can be arduous as you will tell the first time you set it up.

This approach will take some time to develop in prep but it was life-saver in the long run for me. I've used various forms of this function many times (in '03 and '07) and it is solid as a rock.

Hope that helps,
-dK

 

[Mike375]

I am inclined to agree with Adam and one reason being that Excel let's you send something that is "live"

Having said that, my suggestion would be to type up in Excel (or Word) a dummy version of what you want and then post the file as an attachment. I think that way you stand a much better change of getting an answer.

 

[bharlow]

Thanks for your recommendations.

Unfortunately, using Excel is not an option. I have written many P&L's, Balance sheets and Cash flow statements in excel and I know the benefits. This time I have been asked to create the P&L in Access for numerous reasons.

Has anyone ever done something like this in Access? It does not have to be a P&L. Any report similar to a detail financial statement. Any tips would be greatly appreciated.

 

[Mike375]

As I suggested, post up a dummy version of what it will look like. I know what a P&L looks like in Australia for a small business.

Can you post up your DB with your attempts at this stage.

I have done similar at different times but I have used linked queries in Word. When the formatting is correctly done it looks just as if you typed everything into Word.

 

[bharlow]

Attached please find a dummy version of the report. As you can tell, I am just getting started. My main problem is seperate formatting and spacing.

Thanks for any suggestions.

 

[Mike375]

What I have done in the past for that type of format is to introduce records that are null for the fields concerned.

The records that are null need an entry for a sort and if the record is full of calculated fileds then the blank record has an entry in a field that will produce Null as the result. This assumes that the illustration in Notepad represents one record set.

 

[Mike375]

Another possibility is to output it the way you have and them import to Access. I just did that with you Notepad file and all blank lines were there. attached

 

[ajetrumpet]

Could you read that text file Mike ? Wow...what a deal! LOL

 

[bharlow]

Thanks guys for the great response.

Two questions...

Mike375..If I go the null value route, which is what I was considering, is there a trick to adding a line for formating purposes after the null value driven space?

Pat...That's an interesting idea. The report has different fields for different groups. Have you seen this done for anything similar?

 

[Kiwiman]

Howzit

Does something like this help. Curtesy of KHAWAR

http://www.access-programmers.co.uk/forums/showthread.php?t=152519

Not looked into closely.

I have been trying to create some sort of P&L report for my wifes business, as we are currently capturing the Sales Invoices and Purchase Invocies.

I have come up with something like the attached to date.

Is there a restriction on uploading .snp files? Tried to upload it but said invalid file or something of the like...

 

[bharlow]

Kiwiman,

Your P&L is an excellent example of what I am trying to accomplish. I am building a 200 line P&l for a local company.

Can you put the report out here so I can see how you did this?

 

[Kiwiman]

Howzit

My db too large even when zipped. Back end very small (only a few lines of test data), but the reports I have in the front end seem to increase the size markedly - I have never been able to work this out.

Total zipped size 6594kb. You got an email add I can send it to?

 

[bharlow]

Kiwiman,

If you could send just the report and any code associated with the report that would be great. That's a huge file to zip down to only 6meg. When I get done I'll send my report for comparison purposes.

My email is bharlow@visionaryfs.com

Thanks,

 

[gemma-the-husky]

ive not read every thread, but you need to understand NL coding structures, and think in terms of generating a NL grouping order, and account code range that will produce a query sorted to meet your requirements, then a report with judicious grouping/subtotalling/detail suppression will give you your report.

you may need to mess with credits/debits as normally a profit is a minus (sales are true credits)

i did one like this, where the nom code is area/region/dept/cost centre etc, and it lets the client pick from these to get the sort order he wants. shows current month/year budget/actuals prior year/ variances percents, all from 1 query

 

[bharlow]

gemma the husky.... that sounds great but I am not sure I've ever heard of NL coding structure.

"shows current month/year budget/actuals prior year/ variances percents, all from 1 query "

It sounds like you've already accomplished exactly what I need to accomplish. Could you explain further and/or send an example query and report?

Thanks for your help

 

[gemma-the-husky]

i am not able to post this.

its just that you dont build reports by thinking about them as a line at a time - you have to think about selecting all the rows that make up your profit statement together

(ie a NL consists of P&L accounts, and BS accounts) so for a P&L account ignore the balance sheet accounts.

now to get the accounts sorted in a particular order for presentation, you need to be able to sort and group them in some way - this can be done either be examining the NL account number code, or by using some subgroup settings

eg
all accounts starting with a 1 may be sales
all accounts starting with a 2 may be cost of sales
all accounts starting with a 3 may be expenses
etc

so the total of all the accounts beginning with 3 is your total expenses


within the code, the third and forth characters perhaps may determine the production department
and the fifth and sixth characters may determin a cost category, etc

So you can summarise each department by grouping on characters 3-4. And obviously for each department you can get an expenses summary by subtotalling the expenses usings characters 5-6. But, if you have several departments, then instead you can get the total expenses, analysed by department, if you sort your accounts first by characters 5,6 and THEN by characters 3.4

instead of this (or as well as this!) you may have different or further lookups, not forming part of the account number, that identify certain accounts as sales, other accounts as expenses, which can then be further divided into rent/salaries/statinoery/motor expenses etc etc

-----
all substantial businesses would most likely have a highly structured account code of up to 12 or more characters perhaps that contains appropriate grouping information of this sort

so with such a structure you can get access to automatically give you subtotals on each grouping of characters at whatever level(s) you need - which is what you need to get a summarised P&L account.

if your PL accounts have no structure then its far far harder - and you are probably better exporting the list of balances to excel and rearranging it in excel