1.1 CXL Company Code

   Start a new sheet or Download the template below:

Template for Training Sections 1, 2 and 3

has everything but the formula to make your life easy.

Download

First, we are going to start with the basics. Pressing the Consolidated XL button on the Excel Home tool bar will show the Consolidated XL Task Panel on the right of the sheet. In this Panel can see our list of connected companies. Each one has a 6-letter code, which can be changed in the adjacent options menu:

CXL Company Code shown beside each company

This CXL Company code is then used as a parameter with each CXL function to get data from the FAST (Financial Accounting Summarization Table) summarizes all financial information by financial period and week. As a result of this FAST database, Consolidated XL returns data very quickly by Week, Financial Accounting Periods/Quarter, Calendar Months/Quarter.

1.2 Basic Parameters

   In B2 Let’s add a label "Company:", right justify and bold the Cell.
   In C2 let’s enter one of the demo company codes "ZXERO01"

It is best to stick with the example company first, and you can change to your own company when you finish the course.

So we have:

Company Code

   Now highlight cell C2 and name the cell "CXLComp" and press Return to name the cell. How to define a range

Name Cell B2 "CXLComp"

1.3 Get Company Name

   Let’s get company name by entering a formula in cell D2 =CXL.GetCompanyName(CXLComp)
   You can copy and paste this formula to save time. Use Ctrl+C to Copy, and Ctrl +V to Paste.

   Reference Guide CXL.GetCompanyName( CompCode )

Get Company Name

Now we know which company we are getting data for, and if we change the code latter it is updated and validated.

1.4 Get Company Status

   Now Let’s get company status by entering a formula in cell D3 =CXL.GetCompanyStatus(CXLComp)

   Reference Guide CXL.GetCompanyStatus( CompCode )

Get Company Sync Status and Current Period

1.5 Get GL Name

   In Column B, from row 10, we will start by typing in a few GL Account numbers. 200, 230, 260, #REVENUE.
   In column C10 we will use =CXL.GLName(CXLComp,B10) to get then name account 200.

   Reference Guide CXL.GLName( CompCode, GlCode )

   Now copy down sheet, by dragging the drag handle.

Get GL Names of the GL Account List

The "#REVENUE" code will get the total for all GL's under the REVENUE heading (Xero has SALES and REVENUE). See Xero, QuickBooks or Sage for these heading lists. We can even get the Net Profit by using #CalcNetProfit.

1.6 Get Granular Period GL Value

   Let’s put a series of period numbers 1-12 in cell E7 onwards, across the sheet.
   In row 6 repeat the current year given in the D3, we got earlier
   In cell E10 Let’s add =CXL.GLVal(CXLComp,$B10,E$6,E$7) to get the period Turnover.

The "$" in the parameters are called "Absolute References" Click here to see a video about these

   Reference Guide CXL.GLVal( CompCode, GlCodeRange, Year, Period, [CostCentre], [Department], [Options] )

Get GL Value for period 1/2023

   Now drag the formula down and across:

Get GL Value for period 1-12 2023

Whole Years Turnover for periods 1-12

Well done we have our first HOT LINKED report! Sales for a whole year by Periods

1.7 Get Granular Year Total

   Now Let’s add two more periods columns, 0 and 199 in columns Q and R
   And fill in the year with same values
   Drag the CXL.GLVal() formula across

We now see Total for the year in the 199 column, and in the period 0 column the YTD (based on current Company Period) for the year, if we had other year columns then they would YTD for those years.

If we use period 104 we get total turnover for periods 1-4. 306 will give periods 3-6 for specified Year.

1.8 Period Labels

Now Let’s add some magic! Period headings which automatically change.

   In B5 enter "Age:", Right Justify and Bold.
   Name cell C5 "CXLOptions" like we did with company code before in section 1.2.
   In cell E9 add =CXL.GetPeriodName(CXLComp,E$6,E$7,"",CXLOptions)
   Now copy the formula over from column E to column R.
   Bold and Right Justify the row.

Now we should see the period headings:

The fourth optional parameter sets the style of the period text.  If Set to "MM/YY" you get month name, and year.  Please refer to the following Reference Guide:

   Reference Guide CXL.GetPeriodName( CompCode, Year, Period, [Style], [Options] )

1.9 Period aging

Next we need to rewire the =CXL.GLVal(CXLComp,$B10,E$6,E$7) function to include the CXLOptions we just added.

   Alter E10 to read =CXL.GLVal(CXLComp,$B10,E$6,E$7,"","",CXLOptions)

The two blank "","" parameters are for Cost Centres (AKA Tracking code, Classes and Locations) which we will discuss later.

   Drag the new formula down and across to make all formula include the CXLOptions cell

1.10 Options Parameter - The MAGIC

   Change C5 to "AQ" in the options cell

We should see all the values and the headings change to give the values by Accounting Quarter.

Periods over 4 now give an error because Accounting Quarter only has 4 periods (in section 1.13 below we discuss how to fix this). You may need to alter the period number accordingly.

   Change C5 to "CQ" for Calendar Quarter, "W" for Week, "C" Calendar Periods etc.

   See Reference Guide CXL.GLVal( CompCode, GlCodeRange, Year, Period, [CostCentre], [Department], [Options] ) for all parameters

   Now change to "W", we get the value for the first 12 weeks of the Year.

   We can also use "toUSD", "toEUR" will convert all Home Currencies to a particular Currency

The options parameter is very powerful. Multiple value can used separated by ",". So "W,toUSD" will give accounts convert to USD by "Weeks". We have a function which will take multiple cells, as parameters, and join them for you called CXL.CombineOptions().

The Options parameter ignores any text after the first word so can have a description after the first word to make it more user friendly.

   Reference Guide =CXL.CombineOptions( OptionsParmList )

"A" gives by Accounting Period which is the default if not given.

1.11 Get Granular Cost Centre GL Value

   Now add another label "Rep:" in B8 + name "CXLRep"
   Name C8 "CXLRep" which is empty right now
   Next edit the formula in cell E10 to read =CXL.GLVal(CXLComp,$B10,E$6,E$7,"",CXLRep,CXLOptions)
   And copy down and across, to wire all formulas to the Rep code in C8
   If we now type "Sam" in the Rep C8 the values are all filtered by Rep. Other values: "Sam", "Tim", "Jane" and "Peter"
   "No Analysis" gives the value for the any amount booked with No rep code. And "None" or Blank gives all.

In this example, derived from Xero Accounting the Rep is stored in Tracking Code 2. AKA Classes and Locations in QuickBooks and Cost Centres and Departments in Sage Accounting.

Well done we have our second HOT LINKED report! Sales for a whole year by Periods filtered by Rep

1.12 Get Current Balance from Balance Sheet Accounts

You may want to get the current balance of a GL value on the balance sheet. You can use the function as in the above examples, but it can be simplified get the Current Balance using just =CXL.GLVal(CXLComp,"090") which is the Bank Current Account in demo company.

In the next section we will hot link a whole section of the balance sheet with one function

1.13 Relative Periods

In the CXL Wizards, where the user enters a Year and Period and displays multiple periods in columns, functions are used to drive the Period and Year for the column below. As you can see in the screenshot below:

"P&L / Income Statement" and "Sales report" wizards populate Row 4 with -11 to 0, and CXL.GetRelativeYear() and CXL.GetRelativePeriod() calculates a Period and Year based on the current options (A,C,AQ,CQ,W...). Thus, if the Period Aging is changed to Quarters or Weeks, the Periods and Years are calculated accordingly. And we don't have the problem we had in section 1.10 above with Quarters because it handles the years too.

Column G, in the example above, calculates Relative Period -8 (back 8 periods in G4), creating a column period of 11/2022 from the last period of 7/2023 (set in C16 and C17).

Cell B4 uses the =CXL.Combine() to combine the options from various cells C13,C14,C15 (Aging Method, Cash/Accrual and Currency)

   Reference Guide =CXL.CombineOptions( OptionsParmList )

   Ref. Guide CXL.GetRelativeYear( CompCode, InputYear, X, [Options] ) and CXL.GetRelativePeriod( CompCode, InputPeriod, X, [Options] )

The Spill function discussed next calculates its own Periods based on optional parameters [FromYear] and [FromPeriod], so you always express the last Year/Period to get, and it does the rest.