Alchemex Management Pack

From Sage Intelligence Knowledgebase
Jump to: navigation, search

Question

In Accpac, I run a 13 period year, used for audit adjustment entries.

The Alchemex Management Packs do not include the 13th period for reporting purposes.

How do I overcome this as now my TB does not agree to my Alchemex reports because of the audit adjustments.

Answer

The 13th period is catered for in the Alchemex container and is pulled into Sheet1 of the Management Pack.

However,it is not catered for on the Lookup Worksheet.

The general method employed to add the 13th period to your layout, would be to add it to the Lookup Worksheet after period 12 and adjust relevant formulas to pick this up.

The TB Synch add-in would also need to be amended to pick up the extra 3 columns on the lookup sheet i.e. actual13 budget13 prior13.

However, personally I would not change anything in the TBSync2 add-in as it runs off named ranges anyway and should pan out 100% using the following method.

My method would be to add the additional 13th period columns to the "Lookup" worksheet in your workbook as follows:

In "Lookup" sheet select columns BB,BC,BD by clicking on these column headers so all three columns are "greyed", now copy (Ctr-C) and then select column BE and then right click and select "Insert Copied Cells"

You will now have your new Period 13 columns inserted into the lookup sheet under Columns BE,BF,BG

Change Cells BE1:BG1 to each read as Period13 (Cosmetic) Leave Cells BE4:BG4 as is (changing to 13 will complicate matters so just leave as is)

Of course now you have to change your YTD formulas in Range BK6:BM6 to include the newly added Period 13 into it's YTD calculation.

Thus the formula in cell BK6 will need to change from

=SUM(T6,U6,X6,AA6,AD6,AG6,AJ6,AM6,AP6,AS6,AV6,AY6,BB6) to

=SUM(T6,U6,X6,AA6,AD6,AG6,AJ6,AM6,AP6,AS6,AV6,AY6,BB6,BE6) and the same for Cell BL6 and BM6.

All you need do now is include the Period 13 columns to each of the EXISTING Management Pack Income Statement and Balance Sheets and ensuring that those new columns reference your newly created 13th period Columns in the "Lookup" workbook.

Link this modified template back using the Alchemex Report Manager and you now have a Management Pack that caters for 13 Periods. (To link back, you need to be a Report Manager or Administrator)

Right, lets review.

To see Accpac Period 13 Actuals using a SQL expression we would type

CASE WHEN [GLAFS].[FSCSDSG]='A' AND [GLAFS].[CURNTYPE] = 'F' AND [GLAFS].[FSCSYR]='@GLYEAR@' THEN [GLAFS].[NETPERD13] ELSE 0 end

The above actually says where Type = Actuals and Currency Type = F and Fiscal Year = Current Fiscal Year return the content of the corresponding cell in column NETPERD 13 - IF NOT then return a 0

The bit we are interested in for your excercise is NETPERD 13 in the Table GLAFS as it contains the values for Period 13

(there is alos a NETPERD 14 and 15)

I am not aware of a Field in Accpac SQL tables called ADJ thus wonder if it not just a Text reference for one of the NETPERD columns or do you have more specific details?

What I would suggest is checking the GLAFS table using Alchemex Direct SQL Query or MS SQL Server Management Studio and checking which NETPERD column (13 - 15) contains your ADJ figures.

Try a test query like:

SELECT * FROM [dbo].[GLAFS] WHERE [ACCTID] = '1000'

Replacing 1000 with whatever account number you have ADJ Period 13 details for and checking the content for a matching amount.

Leave the Date Lookup as is until you get the correct data comming through on Sheet1 and then check the rest from there.