Retail Therapy With Microsoft Office

Wed 23rd May 2012

Are you swamped by data, needing to create lots of reports in a short time, with no one else to help you? Then you might want to develop your Excel skills in using features such as Pivot Tables, functions and VBA to help you.

Does this situation sound familiar? You've taken up a new post analyzing financial data in a larger locally based retail organisation, and you discover there are vast amounts of tabular data being gathered from all parts of the business, and all this data lands on your desk. Your manager wants you to use Excel to regularly produce a great variety of reports, some of which will be used in PowerPoint presentations. You are frequently asked to create different reports for different management groups, often at short notice and of course there's no one else to help you.

Traditionally, given time, you could use sort, group and subtotal this data in different ways depending on what report is wanted, then copy and paste data to summary worksheets and then create the required reports. But you don't have time to so this.

Fortunately the solution lies in using Excel's fantastic pivot table features which allow you to create customised groupings and subtotals from your vast lists of data. So whether your manager wants a report summarizing sales of a particular product line by different stores outlets, or summarizing fixed and variable costs per outlet over time, you can create these in Pivot Tables.

And if the report criteria need changing, for example a manager wants an analysis by sales staff for example rather than by location, then you can quickly change the Pivot Table criteria to produce the new report. And as you become familiar with the required reports, you can add further details within the Pivot Table, allowing the managers to drill down into the data and see exactly where it comes from.

If you need to display Pivot table data meeting particular criteria you can of course sort, filter and apply custom filter criteria. You can also use functions such as VLOOKUP and IF to extract data from the original data or from your pivot tables and show it in another part of the worksheet. You can also create charts from your analysed data, either charting an entire Pivot table, or using the Range Name feature highlight specific cells within your Pivot tables or within the original data to create Charts based on custom sets of data. In this way you can create charts from only certain parts of a complex analysis.

As you develop your Excel reports, you may want to automate some processes. For example suppose you receive a particular sales worksheet every week with the latest sales data. Every week you copy this data and paste it into a master spreadsheet where you accumulate all the weekly sales data. You then create one or more Pivot Tables to analyse on-going performance details. Because this is a repeating process you can automate it using Excel's VBA (Visual Basic for Applications). To do this in Excel you first create a command button. Then you use VBA to create actions linked to this button. So when the button is clicked, the data in the latest weekly workbook is automatically imported to your master spreadsheet and added at the end of the current data, so the overall data set is increased. Using VBA to automate processes like this can free up your time and allow you focus on your analysis work. You can use VBA to automate lots of other Excel processes too. For example you can create another button and some more VBA to enable Excel to automatically creates a Pivot Table to your specifications, and perhaps print your reports, or export your work to PowerPoint.

Preparing PowerPoint presentations for management meetings can be a key job analyst job requirement. You can manually or automatically copy and paste analysis work from Excel to PowerPoint, and if you need to do this on a regular basis, say with different data each time, you can link the Excel data to your PowerPoint presentations such that updates to your Excel data analysis automatically update in PowerPoint.

You may already be working with other sources of data such as from Social Media, from special in-store promotions, call centre resolutions or web site enquiries, so the amount of available data is greatly increasing. To help you create Pivot Tables from very large data sets, Microsoft offer a free PowerPivot add-on for Excel 2007 and 2010. This add-on stores the additional data in a separate database within your Excel file and lets you create Pivot analyses very quickly compared to pivoting using very large data sets within your worksheets. And if your organisation uses Sharepoint, then you can enable these large data sets to be shared with other users.

So greatly increasing amounts of data can be effectively managed using Excel features such as Pivot Tables and Charts, functions such as VLOOKUPS to create complex analyses. And you can use VBA to automate many of your processes, reducing analysis time, reducing data entry errors and improve decision making. A really effective way to gain these skills or enhance your existing skills is to attend a training course, and then see how you can greatly transform your work patterns.

Author is a freelance copywriter. For more information on microsoft office training, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-2077-retail-therapy-with-microsoft-office.html