Account Schedule

bennie_de_beer · 2006-09-12

Hi,

I am busy writing an Account Schedule. but don't know how to insert a formula in a column to calculate the row amount as a percentage of total sales.

Example:

Spares Sales 100,00 50%
Machine Sales 50,00 25%
Sundry Sales 50,00 25%
Total Sales 200,00 100%

I hope this is clear.

Thanks for your assistance.

johnson_alonso · 2006-09-12

Do you want to setup formula in totalling column in the account schedule will result value in percentage and not in general/standard when you want to overview the account schedule or not ?
If just insert a formula that will result general value (not %), you just can write e.g. 50/100*(R1+R2).
But if you want the result in % when you use account schedule overview, I think it is impossible since the column will directly link to COA G/L window.

Rgds,
Johnson

bennie_de_beer · 2006-09-12

Thanks for the reply.

What I want to achieve is a report with 2 columns. The first column contains the Net Amount (Current Period). The second column is a 'formula' column. The formula column is a calculation where the individual row is expressed as a percentage of the total row. The client wants to see the % of the individual rows of the "Total Sales".

The report will list all the individual sales accounts, with a row containing "total sales". The report then needs to show the individual rows percentage of the "total sales" row.

Example:
Row, Description, Net Current Pd, % of Sales
10, Spare Part Sales, $100.00, 25
20, Machine Sales, $200.00, 50
30, Other Sales, $100.00, 25
40, TOTAL SALES, $400.00,

So the formula should be something like:
(Net Current Pd Column/Net Current Pd Column Row 40)*100

I am not sure if this is possible with Account Schedules?

Your assistance is greatly appreciated.

Regards,

themave · 2006-09-12

Here is my account schedule that does this

Row No. Description Totaling Type Totaling Calculate Show New Page
Set % of Sales Set Base for Percent 390 Net Change Yes No
SALES Posting Accounts Net Change Yes No
310 Parts Sales Posting Accounts 310 Net Change Yes No
312 Core Sales Posting Accounts 312 Net Change Yes No
315 Job Sales Adjustment Posting Accounts 315 Net Change Yes No
320 Labor Sales Posting Accounts 320 Net Change Yes No
330 Freight Sales Posting Accounts 330 Net Change Yes No
335 Inter-Branch Transfers Posting Accounts 335 Net Change Yes No
Underline Net Change Yes No
390 TOTAL SALES Total Accounts 390 Net Change Yes No

The first row is
set % of Sales Set Base for Precent, it references row 390, which is my total sales row, which also happens to be account 390 in my chart of account.

Then in your column layout

you have

1 Current Pd Net Change
2 Formula 1%

hope this makes since, not sure how it will look, I cut and pasted into this form

bennie_de_beer · 2006-09-13

Thanks for the reply.

I am uncertain about a few things and would appreciate if you could help (the cut and paste is a little bit wonky/unclear):

I assume your account schedule has the following columns:
Row No.
Description
Totaling Type
Calculate ???
Show
New Page

My version doesn't have a "Calculate" column
or a Totaling Type of "Set Base for Percent"

So for the first row you would set:
Row No. - ""
Description - "set % of Sales"
Totaling Type - "Set Base for Percent" (Don't have this option in Australia version?)
Totaling - 390
Calculate - "Net Change" (Don't have this column in Australia version?)
Show - Yes
New Page - No

It will be great if you could cut and paste it into Excel and email it to me at bennied@orchidsystems.com.au.

Your help is greatly appreciated.

Thanks,
Bennie

annie1234 · 2008-07-17

Hi all,

I also have a bit problem in this matter.

I have the row formula setup choosing "Set Base for Percent", but the %sign is not shown in the report.
In the column layout, I pick "formula" in the COLUMN TYPE field but I don't know what to type in the FORMULA field.

Please advice. When I try % in the field, figures are not shown at all.

THX
Annie

Davedave · 2008-07-18

annie1234 · 2008-07-19

Hi Davedave,

Thx. I wonder what do I need to pay attention on the "ROW" set-up?

This link to show my column setup -

http://i33.tinypic.com/2vx52ip.jpg

This link to show the error message after the column setup

http://i35.tinypic.com/2luuu1c.jpg

Thx again,
Annie

Davedave · 2008-07-21

Hi Annie,

Glad to be of help.

For your row setup you need to specify one of your rows as 'Set Base for Percent'

Below are 2 links to a sample row setup and column setup. I am using the Cronus New Zealand database for testing.

http://img175.imageshack.us/img175/2697/testrowhv3.jpg

http://img521.imageshack.us/img521/9352/testcolumnxx1.jpg

annie1234 · 2008-07-22

Hi Davedave,

It is too great we now see the % sign showing up, too great !!!
Thx so much.

This is my Rows and Columns set-up
http://i34.tinypic.com/anj5ty.jpg

This is the report shows
http://i37.tinypic.com/2w6dy7s.jpg

Just wonder, can we do a set-up only show % sign in ROW no. "E1" .... as that is the only line I want to set the %.

Is it possible ?
Annie

Davedave · 2008-07-22

Hi Annie,

That I don't know. I am also trying to find out how to do that

Does anyone else know?

Thanks
David

Account Schedule

Comments

Categories