03 December 2012

Weekly Dynamic: Excel Financial Statement Formatting

Despite tools to create financial statements for Dynamics GP like  FRx, Management Reporter, deFacto and many others, some folks want to just use Excel reports to create financial statements. One of the challenges with this approach is that if you show debit balances as universally positive and credit balances as universally negative it looks really unprofessional. The math works but having revenue as a negative and expense as a positive usually freaks end users out.

Yes you can write a bunch of formulas to flip the signs, and redo the calcs but let me give you another option. Change the formatting. You can create a custom number format to switch the signs and only apply it to certain sections of the financial statements.

This format _(* (#,##0);_(* #,##0_);_(* "-"??_);_(@_) flips the sign and puts brackets around positive numbers. If you apply it to the Liabilities and Equity sections of the balance sheet, the numbers all change to positive but the underlying calculations don't change. The balance sheet still balances and all that has changed is the formatting.You can apply this formatting to the whole Income Statement and it comes out perfect.

The nice thing about this is that if you ever want to see the raw data from GP, just reset the formatting. You don't have to worry about making a mistake in formula to swap the signs on debits and credits.