P& L in Excel - how do I suppress current month?
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?
- Just because it's Friday 204 5
- Playstation 4 - Allowable expense or not? 3,333 66
- Inheritance Tax - Simple Question 114 4
- Non-resident employer & employee 99 4
- Capital Allowances 129 4
- equipment rental 215 10
- Acquisition by company 90 5
- Non sequitur 519 7
- Client using boyfriend's personal account 1,455 23
- R&D on products not sold 93 3
- Distance between Postcodes in Excel 867 13
- Tech Champion of the Year 228 5
- Under Cash Accounting rules how do I account for a credit note issued outside the Corp Tax period? 192 4
- Quirky thread of the year: Community Awards 626 9
- Transfer of land to family members 80 2
- The complete uselessness of the British banking system 572 11
- The process of issuing dividend? 188 6
- Not registered for VAT. Germany wants proof! 212 5
- Pattern for CTA 319 7
- Incorrect Accounts. Is it worth taking action against the old accountant? 1,404 8
- Desperately seeking Susan, Sam or anyone with any other name 1,360
- Darwinpay Partnership Assessment 436
- Alphabet shares 237
- Agent fees to sell business 230
- ACCA continuity of practice arrangement needed 226
- Charge out rates in London 175
- How to reclaim VAT for a non-EC UK visitor 167
- which entity 147
- UK tax regime 'attractive' to investors, says KPMG 142
- confusion about 140