Using Excel for Business Analysis. Fairhurst Danielle Stein

Чтение книги онлайн.

СКАЧАТЬ Manipulate large data sets quickly, even if they consist of millions of rows (Excel can’t do that!).

      ■ Construct complex what-if reporting systems with data modelling and data analysis expressions (DAX).

      ■ Link data from various sources quickly and easily.

      Power Pivot is one of the most exciting things to happen to Excel in a long time, and certainly worth some consideration when you are building an Excel solution. Although more appropriate for data analysis than pure dynamic financial models, Power Pivot is worth bearing in mind as a possible tool. If you find that your model has the following attributes, then you should consider using Power Pivot:

      ■ Your data contains many thousands of rows and your model is starting to slow down.

      ■ PivotTables or Tables are used extensively.

      ■ Data needs to be sourced from multiple locations.

      One of the great things about Power Pivot is that it is a free download that comes with the licence you have already if you’re using Excel 2010. Be careful, however, about which version you buy if you’re using Excel 2013, as Power Pivot is not included with every version (for some inexplicable reason). There are also a number of differences between the Excel 2010 and 2013 versions, and as this is an area of rapid change, I have no doubt that the availability of versions and features may have changed by the time this book goes to print, so be sure to research carefully before you purchase your license if you are specifically upgrading with the intention of using Power Pivot.

      The disadvantage of using Power Pivot is that although you don’t need to be a BI specialist to use it, learning how to use Power Pivot is not particularly straightforward even for advanced users. We offer a number of Power Pivot training courses at Plum Solutions through our partners, and there are many videos and online resources that can help you to get started if you decide that Power Pivot is the solution that you need.

      If you are trying to decide whether your Excel skills are advanced enough to consider tackling Power Pivot, here are some questions that will help you to determine whether you are ready to take on Power Pivot. You should:

      ■ Understand and have used Excel’s SUMIF function.

      ■ Have a working knowledge of filtering data in Excel (e.g., Auto or Advanced Filters).

      ■ Know how to deal with multiple criteria (e.g., SUMIFS, SUMPRODUCT, or DBASE functions).

      ■ Be able to import data from third-party databases and/or files (e.g., Access, SQL, MIS systems).

      ■ Regularly use, adapt, and modify PivotTables (see Chapter 8 for more on PivotTables).

      ■ Have created calculated fields in PivotTables.

      ■ Have created and/or modified an Excel Table (a structured reference table, not a data table) (see Chapter 8 for more on Excel Tables).

      ■ Have access to either Excel 2010 or Excel 2013 Professional Plus.

      Although still quite new, Microsoft seems to be devoting a lot of resources to developing the Power Pivot product, so it is likely to gain even more popularity in the near future. It’s worth investing some time in learning it: Being skillful in Power Pivot may become similar to having advanced Excel skills and will be a valuable addition to your CV, and benefit your career as an analyst.

       MS Access

      Access is probably the closest alternative to Excel, and is worth a mention. There is often some resistance to using Access, and it is certainly less popular than it was a decade or so ago. Prior to the release of Excel 2007, Excel users were restricted to only 65,000 rows, and many analysts and finance staff used Access as a way to get around this limit. With now over 1.1 million rows (and purportedly up to a billion rows if you install Power Pivot), Excel is able to handle a lot more data, so there is less need for the additional row capacity of Access. If you’ve been using Access over the years, you might have noticed that not very much has changed in Access between versions. It seems that Microsoft is investing more of its efforts into the new Power Pivot rather than Access, and therefore we can expect more models in the future to be built using Power Pivot.

      Advantages of Excel

      ■ Excel is included in most basic Microsoft packages (unlike Access, which often needs to be purchased separately) and therefore comes as standard on most PCs. Excel is much more flexible than Access and calculations are much easier to perform.

      ■ It is generally faster to build a solution in Excel than in Access.

      ■ Excel has a wider knowledge base among users, and many people find it to be more intuitive. This means it is quicker and easier to train staff in Excel.

      ■ It is very easy to create flexible reports and charts in Excel.

      ■ Excel can report, model, and contrast virtually any data, from any source, all in one file.

      ■ Excel easily performs calculations on more than one row of data at a time, which Access has difficulty with.

      Advantages of Access

      ■ Access can handle much larger amounts of data: Excel 2003 was limited to 65,536 rows and 256 columns, and later versions of Excel are limited to around 1.1 million rows (1,048,576 rows, to be precise) and 16,384 columns. Access’s capability is much larger, and it also has a greater memory storage capacity.

      ■ Data is stored only once in Access, making it work more efficiently.

      ■ Data can be entered into Access by more than one user at a time.

      ■ Access is a good at crunching and manipulating large volumes of data.

      ■ Due to Access’s lack of flexibility, it is more difficult for users to make errors.

      ■ Access has user forms, which provide guidance to users and are an easy way for users to enter data.

      In summary, Access is probably most commonly used for legacy software; databases that have been around for a long time. If it’s a brand-spanking-new solution that you need, consider Power Pivot instead.

       MS Project

      MS Project is specifically for creating project plans and associated component tasks, assigning resources to those tasks, tracking progress, managing budgets, and monitoring workloads. The user can also create critical path schedules and Gantt charts.

      Because the program handles costs, budgets, and baselines quite well, Project could be considered a viable alternative to a financial model, if the purpose of the model were simply to create an actual-versus-budget tracking report. In fact, as with most purpose-built software, if your aim is to track and monitor a project, Project is a superior option to Excel. Of course, creating a project plan and even a Gantt chart is certainly possible in Excel, although it will take longer and be far more prone to error than Project. There are many reasons, however, why users will opt to use Excel rather than Project for a project plan:

      ■ Project is not included in any of the Office suites and therefore needs to be purchased separately.

      ■ The plan may need to be accessed, updated, and monitored by different users, who may not be able to use Project due to lack of skills.

      ■ СКАЧАТЬ