P& L in Excel - how do I suppress current month?

P& L in Excel - how do I suppress current month?

Didn't find your answer?

I have set up an Excel spreadsheet to produce monthly management accounts importing the data directly from Sage. I pull the monthly account movements into a sheet and then pull all the data from there into the various schedules as separate sheets.

On the P&L and BS I want to suppress the printing of months later than the reporting month. The need arises as transactions are often being input for the current month when we are reporting for the previous month. A relatively elegant way of achieving this is by using conditional formatting, where the condition checks whether the month is greater than the reporting month, if it is then it makes the font white, and so is not visible or printed.

However, I have a problem with the year to date totals on the P&L which are just the sum of the monthly figures. Even if a month's figures are not visible, using conditional formatting, they are still included in the total.

The only other solution I have thought of is to use IF statements in every calculation to test if month is greater than reporting month then "" else calculation. However this makes the calculations very convoluted and it is then very difficult to fault find.

Does anyone have a more efficient solution?
Neville Ford

Replies (5)

Please login or register to join the discussion.

avatar
By Neville Ford
31st Jan 2006 12:56

Thank you
I have finally got around to looking at this again, so thank you Mike & Lynne for your elegant solutions. I tried Mike's first and it works, I will investigate Lynne's later.

Richard, the YTD columns after each month is an idea we used to use, but it can be quite messy to maintain. Thanks any way, perhaps you have learnt something new as well.

Thanks (0)
avatar
By ACDWebb
31st Jan 2006 14:31

Twisting both Lynne and Mike's methods further
you could use a dynamic range formula that might be less prone to someone editing an array formula without realising that you need to press Ctrl + Shift + Enter to quit.

So, if you want the year to date for figures spreading across the page starting from B5 you could use:

=SUM(OFFSET($B5,0,0,1,$A$1))

Where A1 contains the number of columns/months across the page you want the SUM to spread.

This is saying:
Starting from B5 add the contents of a range 1 row high (ie row 5 in this case) and n columns wide based on the number in $A$1

You could also select a ComboBox from the Forms menu bar where the Input range was a range containing the months in your year and the Cell Link was A1 (in this instance). You can then select the month that you want from the drop down and the SUM above will adjust as necessary

Thanks (0)
avatar
By Richard Willis
20th Jan 2006 10:50

Use sub totals and 'VLOOKUP'
Hi Neville

The simplest way I can think of quickly is to insert a YTD column after each month in your master sheet. Name the whole range including column numbers inserted at the top (1 - 24), then in your presentation sheet you can have two cells at the top which have either M or Y in one, and the month No. in the other.

By using 'VLOOKUP' in conjunction with these, you can make the presentation sheet display any month typed in at the top, M or YTD, or both, instantly!

There must be other ways; this is just the quickest and simplest that came to mind.

P.S. For simplicity, so that m# works 1 - 12, you will need to multiply the input M/No by 2 and take 1 for Month only in the VLOOKUP, e.g. M6 Month = column 11!

Thanks (0)
avatar
By lynnecart
20th Jan 2006 12:26

Good example of an application of "array" formulae
I find array formulae are very powerful - hitting "Ctrl+Shift+Enter" instead of just "Enter" has opened up a whole new world! ;-)

Enter months in row 2:

A2 = eg. Jan-05 (date formatted)
B2 = eg. Feb-05
etc through to Dec-05

... and YTD at the end (in cell M2)

Demo result, eg. turnover, in row 3:

A3 = 100
B3 = 200
etc

Cell A1 is your control cell - for illustration, put Apr-05 there to indicate that you want April YTD.

Your cell M3 (YTD dependent on the month in A1) formula is

=SUM(IF(A2:L2<=A1,A3:L3))

...if you only hit "Enter" you get a #VALUE! error but if you hit "Ctrl+Shift+Enter" it will work.

Thanks (0)
avatar
By mikerees
20th Jan 2006 14:13

Alternative solution using Index and SUM Index
Neville

This is an example of how I approach the Y-T-D totals.

12 months in cells E1:O1
Column P is blank in this example
Monthly sales totals E4:O4

Period selector (1-12) in B1

To generate total sales for period n,
set B1 to period n.
Use the formula -
=INDEX(E4:P4,$B$1)

To generate total sales for say periods 1-6, put 6 in B1

Use the formula -

=SUM(E4:INDEX(E4:P4,$B$1))


Mike

Thanks (0)