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.
0
Comments
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
sea-navision-community-subscribe@yahoogroups.com
detail in:
http://sea-navision-community.blogspot.com
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,
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
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
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
E.g.
Column No. | Column Header | Column Type | Formula
A | Net Change | Net Change |
B | Percentage Of | Formula | A%
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
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
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
That I don't know. I am also trying to find out how to do that
Does anyone else know?
Thanks
David