This month we offer a chance for you to show off your spreadsheet talents and have your name published in Insight. By Liam Bastick, director with SumProduct.
Spreadsheet skills turn the tables on readers this month with two real life Excel puzzles for you to get stuck into. It's just for fun, but the best solutions will be published in next month’s spreadsheet skills article.
Feel free to use the attached Excel file to solve the challenges below. Answers should work in all versions of Excel and should be sent to Liam Bastick at the email address below by Tuesday 15 December.
Christmas challenge 1
This question has come up two or three times this year and I thought I would pass it on to seek alternative solutions.
Essentially, a data file has been created with two columns, dates and data, viz:
Challenge number 1
In the illustration (above), the challenge is to find a simple formula to put in cell G12 (yellow cell) which will return the most recent date at which the corresponding data was less than or equal to the value in the ‘target’ cell (cell G14).
It can be assumed that there will be at most one data point for each date, but the dates may not necessarily be in ascending order. The ideal formula should take into account what happens if no date meets the criterion.
Christmas challenge 2
This one provides perhaps greater scope for innovation: how do you model the total depreciation charge for a given period (yellow cells in illustration, below)? I envisage many of you have had to model this at some point in your accounting careers.
Challenge number 2
The Excel file (above) contains the assumptions to use (not displayed in the above graphic). These include assumptions for existing non-current assets and new capital expenditure.
The problem with this puzzle is that although there is a very straightforward, transparent method using a matrix, this approach can take up a significant amount of memory and file size. What particularly interests me are the ‘shortcut’ methods which circumvent this approach. I know of three common ones but I thought I would ask my learned colleagues for their solutions …
PowerPoint skills
Spreadsheet skills returns to its normal format next month, so please keep emailing your queries to Liam Bastick at the email address below.
Insight is considering another similar series aimed at boosting the skills of financial users of PowerPoint - for example, how best to use various financial data in a presentation - if there are sufficient questions. If you have any PowerPoint queries or topics you'd like to see discussed, please email tim.cooper@cimaglobal.com.
Merry Christmas!
If you have a query for the spreadsheet skills section, please feel free to drop Liam a line at liam.bastick@sumproduct.com or visit the SumProduct website where you can also subscribe to the new monthly newsletter providing more tricks and tips.