I am developing my financial statements using account schedules. One of the schedules is the Income Statement. This schedule with have four columns. The first and third are PTD and YTD numbers. The second and fourth are just percentages of Total Revenue. I am having difficulty with the percentages. I followed the Cronus example but nothing shows up in the percent columns. My chart of accounts include a Begin Total and End Total for Sales Revenues. I think I am missing the End Total Sales Revenue part of the formula. The first and third lines of my Column Layout include: MTD and PTD, G/L Entries and Net Amount. The second and fourth lines include: Column Type - Formula and Formula - %PTD, %YTD. I also agree with another posting that this area is a good area to develop reports. Almost all my reports are written in this area for month end financials - with the exception of the problem I am having! Any help would be appreciated…
Hi Skolnick When you select the overview do you see the figures? If you do here, but not in the print, it is because you have not set hte applicable options on the print run. What formula have entered into the formula column?
When I select the Overview, four columns show. -The first column is PTD figures that appear correctly. -The second column is the percent column (that should have results a percent of PTD Total Sales) where nothing appears. The column is blank. -The third column is YTD where the figures appear correctly. -The fourth column is the YTD percentages where the column is blank. On the Column Layout, The Formula Column in row 2 has %PTD and row 4 has %YTD.
Took another look at the formula and noticed the % was in front rather than in back. The schedule is a little further along now. An error message came back when I tried the Overview: Set Base for % Totaling Type on Line Before It Account Scedule Line Row 2: Line 21000, Totaling=420200 Account Schedule Column: Column #=,Line No.=2000 I looked in the Help but could not find any information on % Totaling Type. Thanks.
Hi Skolnick So if your PTD column is A and your YTD column is C and your PTD percentage is as a percentage of the YTD, then the formula will be A/C*100? In the column layout there is a show field - is this set to Always? If the percentages are based upon budgets you can have the rows in here and never show them but base perventage calculations upon them. Unsure if this helps [:D]
Hmmm, what I am looking for is a PTD Income Statement that divides the Total Sales (one line item) into every account for a percent of Total Sales. Total Sales, by definition, will be 100% and Cost of Goods Sold will be a percentage of Total Sales and so on for other cost accounts. I also want to do this for YTD. The sample report is in Cronus, but I am missing something. The formula needs to point to the Base which is the Total Sales line amount, but I don’t know how to do this. Thanks.
Hi Skolnick You are referencing different rows in a column layout - not sure if that is possible, but this is not really my area [;)]
quote:
I also want to do this for YTD. The sample report is in Cronus, but I am missing something.
Which one?
Thanks, Steve for you input. I figured it out. As the error message stated, I needed to set a base percent. Once I did that, all the percentages for PTD and YTD popped right in! The last report I need to develop has to compare PTD current year vs prior year as well as YTD current year vs prior year. I will play around with this a bit and hopefully not ask too many questions!
PTD current year vs prior year and YTD current year vs prior year with variances all done! It was VERY easy and took a minute or two… I really like the Account Schedules feature!
Hi Skolnick So what was your final formula for PYD%?
Hi Skolnick If I’m reading correct : you can use your column layout to calculate a percentage between two rows ??? Like this, row 1 : Total 10000 2 : GL Account A 2500 25% 3 : GL Account B 7500 75% I’ve never found how to do this ? Can you help me ? Thanks in advance, Nico
A Base Percent needs to be defined in the Accounts Schedule. The Set Base for Percent should be the top account located in the Totaling Type Column. Next, choose the row number for the account to use as the base. (In my schedule, I want Total Sales to be used. I assigned a number in the Row Column.) Place the row number in the Totaling column. When you complete these two steps in the Accounts Schedule, go to Column Layout. The first row should be defined in Column Number: PTD. The second row Column Type is: formula. The formula is: PTD%. Place the PTD% in the Formula column. You can also, on the same column layout, enter YTD for rows three and four. Also, you can place variences by dollars as stated earlier in this thread by placing a column formula (A-B in the Formula column for example where A = Current Year and B = Prior Year). That’s all there is to it. Two steps in the Account Schedule and two steps in the Column Layout. Very simple, very easy.
Hi Skolnick I am obviously missing something here [:D]. Probably not being an accountant is not helping. Could you explain this from the following concept: I have a sales account and a totalling account that totals all sales. In your example you want to display the sales as a percentage of total sales in a column format? So how do we set your “Base for Percent” - isn’t this total sales? But your formula is your total sales? Then in your column layout you are defining the first row as column number “1” with a PTD description and showign net changes, and then your second row is a formula - how are we referencing the row on the account schedule in this column? [xx(] The forumla can only reference a row from the column layout cant it? An idiots guide would be appreciated [:D] Didn’t I start by helping you on this?
Steven you are not the only one who’s missing something. I’m trying to following Skolnick’s procedure, but I’m not getting any percentages in my account schedule. Seems for Skolnick very easy, Nico
Hi Nico I dont suppose the “set base for percent” is actually a field and a US localisation? [;)]
Steven, I don’t know, but I have feeling that’s not possible in standard W1 version (or in Belgian version). This would be a very nice feature. Nico
Apparently there is confusion of what I asked to do originally and the solution I came up with to report what I need. I created a little sample of the percent of sales to all other accounts. Please take a look at it; maybe what I am doing is not clear to what others want. Sales: Horses 1,500 - 75% Saddles 500 - 25% Total Sales 2,000 - 100% Cost of Sales: Horses 1,000 - 50% Saddles 100 - 5% Total COGS 1,100 - 55% Gross Margin 900 45% ALL numbers are computed from one number, TOTAL SALES. Hopefully this will clear up confusion of what I created. The version I am running is 3.60
“Set Base for Percent” is a choice in the “Totaling Type” column in “Account Schedules”. There are numerous choices for the Totaling Type including: Posting Accounts, Total Accounts, Formula, Underline and Double Underline as well as my favorite Set Base for Percentage.
Hi Skolnick, “There are numerous choices for the Totaling Type including: Posting Accounts, Total Accounts, Formula, Underline and Double Underline as well as my favorite Set Base for Percentage” → That’s the reason why we missing something. In standard version we have only choice between “Posting Accounts, Total Accounts en Formula”. → Is this a modification changed by your NSC ? our by US NTR ? → If possible (and legal), it would be nice to receive codeunit 8 (I suppose then changes are made in this codeunit. Thanks in advance Nico
I checked Cronus, and all the choices are listed there. It’s Cronus US, by the way… As for sending out Codeunits, I don’t know if that is legal. I’ll have to ask and see if that is allowed. However, Cronus is on a sample disk. If you can get a hold of Cronus US as a demo, the codeunit will be contained on the sample database.