Categories
Internal Comms

How to Automate Financial Reports

[et_pb_section bb_built=”1″ admin_label=”section”][et_pb_row admin_label=”row”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]

Financial Reporting can be a tedious and time consuming process for a financial manager. It generally takes a few days to create Month end excel reports and to simply make sure numbers balance. Excel now has additional tools (Power Query and Power Pivot) which allows a Financial Manager to create Income Statements, Balance sheets, Cash Flows and the like in less time and with much less effort and with greater accuracy by eliminating the human error freeing up much needed time to focus on, well, everything in their already busy lives.

This guide demonstrates how to create an Income Statement using data sourced from other excel workbooks. There are many alternative data source options such as Excel, CSV or text file extracts from Pastel Partner, databases for Syspro or Sage Evolution to name a few. Using Power Query we can extract data from any of these sources. Using Power Pivot we will create additional measures to use in our workbook. Power Query is a free Excel add on which can be downloaded from the Microsoft website. There are some system requirements which can be reviewed here. Once Power Query is installed you will notice a new Power Query tab in excel.

Power Query

How to Import source data

  1. On the Power Query tab, choose a data source from the options in the Get External Data portion of the Ribbon or from an existing table in your excel workbook. Browse to the folder that contains the raw source data and import by clicking OK.

How to Unpivot columns

  1. Select the Actual, LY and Budget columns. Go to the Transform tab of Power Query. Select Unpivot Columns.

2. The Actual, LY and Budget columns are now pivoted vertically in Attribute and Value columns.

 

How to split a column

  1. Split the Attribute column by a space Delimiter.

The Attribute column is now split into 2 separate columns


How to rename a column

  1. Right click on column Attribute.1 and rename it Measure. Right click on column Attribute.2and rename it Period.

 

    

How to rename a query

  1. Rename the query to any preferred name.

How to Close & Load

  1. On the Home tab select Close & Load to save changes. On the Load To pop up screen, select Only Create Connection and Add this data to the Data Model.

Power Pivot

How to user Power Pivot

  1. Navigate to the Power Pivot tab and select Manage in the ribbon.

How to create a measure using DAX (Data Analysis Expressions)

  1. In the Calculation Area, create the required measures.

How to create a Pivot Table

  1. Navigate to the Insert Tab and select PivotTable. On the Create PivotTable screen select Use an external data source and Choose Connection. On the Existing Connections screen, navigate to the Tables tab and select Table in Workbook Data Model. Click Open.

2. Drag the Month and Measure fields to the COLUMNS area of the PivotTable Fields. Drag the AccountType column to the ROWS area of the PivotTable Fields. Drag the Calc measure the VALUES area of the PivotTable Fields.

 

How to add a Slicer

  1. Right click on the Month column of the Period table and select Add as Slicer.

 

How to Refresh Data

  1. Navigate to the Data tab and select Refresh All from the drop down list. This will refresh all source data automatically.

[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]