Home » Ch 10 – decision support with spreadsheet software
Category Archives: Ch 10 – decision support with spreadsheet software
Some useful Excel links collected from the Internet
Image source: http://webcourses101.com/online-excel-training/
In my second-year undergraduate Management Accounting course, we spend a few weeks on learning the ins and outs of Excel. However, learning and improving on Excel does not stop with a mere test – it can only just be the beginning. Below is a list of interesting links to free readings (and even an online course) for those who want more than just adding and subtracting in Excel:
- Excel with Wayne – 21-day free online course (very useful, highly recommended) LINK
- How to make prettier, more readable spreadsheets LINK
- How to create progress bars in Excel with conditional formatting LINK
- Excel’s versatile CONVERT function LINK
- How to craft the best Excel spreadsheet models LINK
- Factoring in the time value of money with Excel LINK
- How to use Excel in seasonal forecasting LINK
- How to boost Excel efficiency with Power Query LINK
- And finally a WSJ-article discouraging the use of Excel (or put it better: know when Excel works and when it has reached its limitations) LINK
Whilst not all of these may be relevant to your work, they a) may be in the future, and/or b) definitely deepen your understanding of Excel. And according to employers, Excel is a “skill” (if one was to reduce it to “one” skill) that accounting graduates still show deficiencies in.
Good practices when you design your Excel workbook
Micros
oft Excel is the standard and go-to application when using spreadsheets – there is hardly any spreadsheet user who has not worked with Excel at one point in their professional career before. Now that Microsoft has spread out beyond the PC onto tablets, smartphones, and has become cloud-based, its distribution is ubiquitous.
As such, it has become even more important to create professional spreadsheets that can be understood and collaborated on in a seamless manner – there, Google Docs has clearly shown the way. In this blog post by J. Carlton Collins, he details 15 highly useful and – may I state – necessary rules for designing meaningful Excel spreadsheets that start with adequate documentation and a table of contents, well-organised spreadsheets, named ranges/cells, and quality management to avoid errors and misconceptions. These steps are not dealing with style per se, but with good practice that will enhance your reputation as spreadsheet expert.
Do you know what chart type to use in Excel?
In our book, we discuss the history and use of spreadsheets in accounting, chief amongst them Microsoft Excel. Management accountants in particular rely on the graphical depictions of data and information that Excel offers. However, all too often my Excel students in year 2 of their A&F programmes seem to be unsure what kind of chart type is best to illustrate what kind of information. In this blog post by Ryan Dube, he does not only explain when to use the most common chart types, but also provides hands-on examples on how they are to be created. It is highly recommended not only to have a good read, but also apply what he shows in Excel.
Spreadsheet news – There are 6 new functions in Excel 2016!
With the fairly new cloud-delivery model, many subscribers of Office 2016 will have received news and updates of their selected products over the last couple of months. One that captured my immediate attention announced 6 new, quite useful functions in Excel 2016. They are:
- TEXTJOIN: joins text strings in a simpler way than the CONCATENATE-function; very useful for address or email lists;
- CONCAT: this replaces the old CONCATENATE function, and works in a similar fashion than its predecessor;
- IFS: this is a great new function, as it helps avoid the need for nested IF-statements that can become quite lenghty;
- SWITCH: provides a similar use to IFS, but works to a different logic. An expression is evaluated against a list of values, and specifies return values specific to each value;
- MAXIFS/MINIFS: the newest additions to the family of COUNTIFS, AVERAGEIFS, and SUMIFS functions; these work similar to MIN and MAX, but can be tied to conditions to be met.
This Office blog post here provides more detail on the new functions, as well as further links to the individual functions.
At the same time, this is a very good example how SaaS models can help update software on an ongoing basis without the need to roll out fully new versions. Of course, this requires a stronger attention by accountants to new developments in their area of expertise.
Guesstimate – An uncertain spreadsheet
Users of spreadsheets are often working under the assumption that facts and figures employed are well known and certain. This might be an issue when it comes to planning – so how to incorporate risk and uncertainty in a spreadsheet analysis? Ozzie Gooen has seemingly developed an answer to this – Guesstimate features upper and lower boundaries for values used within specific analyses. It works with confidence intervals and probability distributions that allow its user to work with uncertain “facts, going beyond mere best and worst case scenarios.
VisiCalc – the father of all digital spreadsheets
Our textbook details the history of spreadsheets in general, and their digital version in particular. In particular, VisiCalc has pioneered the spreading of spreadsheets on personal computers, and its pivotal role cannot be underestimated. In this article on the Guardian website, John Naughton delves a deeper into the importance of VisiCalc, especially as the main forerunner of the ubiquitous Microsoft Excel software in businesses and at home.
PowerPivot – more analytical power in Excel
In the last blog post, we have introduced PivotTables as a powerful analytical tool in Excel to manipulate large amounts of data into decision-useful reports. Microsoft introduced an enhanced PivotTable tool called PowerPivot that is available as an add-in for Excel 2010 and 2013. The PowerPivot add-in is now able to process even larger amounts of data, from internal and external sources, to create dashboards that even work online, moving PivotTables to the cloud. This website provides an introduction and working examples of the PowerPivot add-in.
PivotTables explained – CIMA series on spreadsheet skills
The standard user of Microsoft Excel is often staying at a very basic level, inputting data in cells, performing basic operations like sums and averages, or just adding two or more cells using the +sign. However, Excel is a very powerful analytical tool that boasts functionalities to slice and dice large data tables according to the specific information requirements that managers and management accountants might have.
In the spreadsheet skills series by CIMA, there is a very comprehensive explanation on PivotTables for the first-time user. Although this particular post is from 2009 (and therefore does not include additional functions introduced with Excel 2010 and 2013), it provides a good introduction to what PivotTables are all about.
Learning Excel 2013 online – free resources
I have posted a few weeks ago about the new Office 2013. In this post, I would like to focus a more on Excel 2013, a great piece of software in my view. Here is a link to a great website that lists free Excel tutorials and reference websites for the 2013 version.
Microsoft Office 365 – Office 2013 discovers the cloud!
The latest version of Microsoft Office, Office 2013, has an entirely cloud-based life called Office 365, indicating that a subscription to it (and not a one-off payment for the software suite) is valid for one full year.
The good news for all students and university teachers is: Microsoft offers a subscription model for university use, aptly named Office 365 University, with a four-years-for-the-price-of-one subscription, one terabyte of online storage, and the benefit to be able to access and work on your Word document or Excel spreadsheet on a tablet on the go (as I do). I found it incredibly useful to have a software that I used for the last two decades in the cloud now.
More information on Office 365’s features can be found in this Business Insider blog by Julie Bort.
Business information systems for accounting students 