Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Monday, 28 April 2014

Dynamic print range(s) in Excel

I have a spreadsheet where I always want to print 2 columns of interest on the same page. The thing is those columns are different columns for nearly every print run. I can write a formula which calculates which columns they'll be. So, I set about writing a formula for the Sheet1!Print_Area.

Sheet1!Print_Area =offset($A$1,0,MonthNo+2,counta($A:$A),2) 

If MonthNo is 1 Columns C and D will print for as many rows as there are entries in column A.

If MonthNo is 2 Columns D and E will print for as many rows as there are entries in column A. 

And so on...

Using calculations for Print_Area provides the basis for some interesting possibilities:

If I identify a need to print everything on one page in certain circumstances, but on two pages (that need to be carefully controlled) in other circumstances, I can set up a number of calculated names and "cascade" these to create a sophisticated multi-range print range.

Sheet1!OnePage =offset($A$1,0,3,counta($A:$A),MonthNo)
Sheet1!Pg1 =offset($A$1,0,3,counta($A:$A),6)
Sheet1!Pg2 =offset($A$1,0,6+3,counta($A:$A)+1,MonthNo)
Sheet1!TwoPages =Sheet1!Pg1,Sheet1!Pg2
Sheet1!Print_Area =if(MonthNo>6,Sheet1!TwoPages,Sheet1!OnePage)

A word of warning: if the areas that make up TwoPages fit within a simple rectangular area, with no cells within that rectangle unselected, Excel will replace the calculation for Print_Area with the absolute cell references of the upper left and lower right cells when MonthNo > 6. I've even seen Excel crash in these circumstances. However, to avoid this possibility, I find it convenient to include an extra row in one of the ranges. (That is why the calculated number of rows for Pg2 is counta($A:$A)+1, one more than the counta($A:$A) for Pg1) 

Tuesday, 8 April 2014

Sage Line 50 / Sage Instant opening bank balances

There seem to be a lot of questions about recording opening TBs in Sage on various forums. I'm not sure if I've ever seen any comment on the most convenient way to record the opening bank balance (say account 1200), so this short post describes a good way to approach it. 

Assume the closing TB at 31 March 2014 shows a debit of £95 in account 1200 and assume the closing bank reconciliation as at 31 March 2014 looks as follows:

Bank Statement balance   100
Less: Outstanding payments
Cheque 100001    (10)
Add: Outstanding lodgements
Ldg 999999       5
Cash Book balance     95

The bank statement balance represents items that are already fully reconciled, the outstanding cheque and the outstanding lodgement represent unreconciled items.

I've found the best way to set the bank (1200) account up is to enter the bank statement balance of £100 initially by clicking on the OB icon to the right of the Current Balance field in the Balance section of the Account Details tab of the Bank Record for the 1200 account. The date should be changed to 31/03/2014 and £100 entered as a receipt (it would be entered as a payment if the bank statement showed an overdrawn position). After saving, this will show account 1200 as Dr 100 and account 9998 (Suspense) as Cr 100.

Next, in bank payments, enter a payment on 31/03/2014 with the following attributes: Ref - 100001; N/C - 9998; Details - Outstanding cheque; Net - 10; T/C - T9. Save the payment. Then, in bank receipts, enter a receipt on 31/03/2014 with the following attributes: Ref - 999999; N/C - 9998; Details - Outstanding lodgement; Net - 5; T/C - T9. Save the receipt. 

This will leave account 1200 with a balance of Dr 95 at 31/03/2014 which agrees with the TB as at that date, and a balance of Cr 95 in account 9998. When the rest of the TB is entered account 9998 will reduce to zero.

The bank reconciliation should be started by entering the last reconciled bank statement balance as 100 on the 31/03/2014. The bank reconciliation report will then reflect the table above.

I hope this helps.

P.S. If you have payments on account that need to show up on Debtor / Creditor accounts you'll need to alter the methodology to incorporate a customer receipt or a supplier payment instead of a bank receipt or a bank payment. These will form part of the take-on of open items to establish the opening Debtors and Creditors ledger control account balances. The reconciled status of each receipt / payment recorded should be accurately reflected and appropriate adjustments made to the opening bank balance methodology recorded above.  Please feel free to ask any questions you may have regarding your own particular circumstances.