Whitepaper: Extreme Budgeting - Pushing Excel to its Limits... and beyond.

EXTREME BUDGETING: 2005 Pushing Excel to Its Limits... And Beyond 289 Turnpike Road, Westborough, MA 01581 Tel: 508-870-0300, Fax: 508-366-2278 Table of Contents Introduction 3 Excel Power Features for Budgeting 3 List Boxes and Validation 3 3 The INDEX and MATCH Functions 7 The Paste Special Feature 13 The TRANSPOSE Function 17 The Auto Filter Feature 19 When Power Features Aren’t Enough 23 The Benefits of Budgeting with TM1® 25 Inherent Support for Collaboration 25 Rapid Implementation and Low Cost of Ownership 25 Scalability and Real-Time Responsiveness 26 Separation of Data and Calculations 26 Enhanced Security 27 Conclusion 27 ©2005 Applix, Inc. All rights reserved. No part of this document may be reproduced in any form, by Photostat, microfilm, xerography, or any other means or incorporated into any information retrieval system, electronic or mechanical, without the written permission of the copyright owner. Inquiries regarding permission or use of material contained in this document should be addressed to: Applix, Inc. 289 Turnpike Road Westborough, MA 01581 1-800-8APPLIX E-mail:info@applix.com Applix and TM1 are registered trademarks of Applix, Inc. Screen shots of Microsoft Excel used by permission from Microsoft Corporation. All other trademarks and company names mentioned are the property of their respective owners. Part no: TM1-LIT-BUDGET-WP Version 11 Introduction List Boxes and Validation One obvious source of erroneous data in The budgeting process has evolved greatly the budgeting process is the keying of over the last 27 years, not just because data into a spreadsheet. In the past, our businesses have evolved, but also some users have tried to obviate this need because in 1979 Dan Bricklin and Bob by writing custom code in Visual Basic Frankston created VisiCalc – the world’s (VBA) to produce drop-down lists that the first spreadsheet. Since that time, doing person doing data entry can use to select budgeting has become synonymous with data rather than typing it in. using a spreadsheet. Today, it is unthink- able to attempt budgeting without using a In recent versions of Excel though, there spreadsheet. is a much simpler way to achieve the same result without knowing anything In this guide, “Extreme Budgeting: Pushing about VBA or coding. The following Excel to Its Limits… and Beyond,” Applix example shows how you can use data offers several tips and tricks to squeeze that already exists in a spreadsheet as a every last bit of power and functionality list of values that appears in a drop-down out of Excel to make your budgeting easi- list for automatic entry into other cells. er, quicker, and more accurate. We also explain the limits of Excel in coping with today’s planning and budgeting require- ments, and provide an overview of Applix TM1, an analytical platform that works in conjunction with Excel to dramatically expand its capabilities and overcome its limitations. Excel Power Features for Budgeting Excel is a powerful and complex tool, and most users – even serious users – are rarely exposed to more than 25% of its functionality. There is much to be said for knowing only what you have to, but there are those rare occasions when you learn something new that can help you do your job better or more easily, and you quickly wonder how you survived so long without knowing it. The following tips and tricks fall into that category. They cover some of Excel’s often-overlooked power features that can make the budgeting process easier, quick- er, and more accurate. 3 1. To populate the cells in the “Employee for Project” column with employee names that appear in the “Employees” column to the left, first select the range of cells for which you want the drop-down list to appear: 2. Next, choose the Validation option from the Data menu. 4 3. From the Allow list, choose List. 4. In the Source field, click on the icon of the spreadsheet to specify a range of cells from which to generate the values that will appear in the list, and then select the desired range of cells. 5 5. Hit the ENTER key, and then click on the “OK” button. Now, clicking on the cells underneath the “Employee for Project” heading will immediately produce a drop-down list of all the valid names from which to choose. The example above is simplistic for the purposes of illustrating the feature, but using drop-down lists is a great way to avoid introducing errors into the budgeting process. 6 The INDEX and MATCH Functions Often, for the purposes of data consistency and integrity, you may want to find a value in a spreadsheet and then reference an associated value. For instance, in the example above, you might want to be able to specify an employee in the “Employee for Project” column and then automatically have her or his correct associated salary entered from the “Salary” column to the left. One method is to use Excel’s LOOKUP functions such as VLOOKUP and HLOOKUP. However, VLOOKUP and HLOOKUP lack flexibility in terms of sorting in a particular order or using multiple lookup values. An alternative is to use Excel’s INDEX and MATCH functions together. To automatically enter the correct salary value for an employee selected in the example above, simply follow these steps: 1. Click in the first cell in which you want to have values entered automatically – G3 in Often, for the purposes of data this case. consistency and integrity, you may want to find a value in a spreadsheet and then reference an associated value. 7 2. Next, hit the = sign and type “INDEX(” and then select the range of cells you wish to have indexed and available for matching against – B3:D10 in this case. Then hit the F4 key to make the reference absolute instead of relative since the range remains the same regardless of the cell data is being automatically entered into. 8 3. Next, type a comma and then the word “MATCH” followed by an open parenthesis. Then click in the cell that you want to key off of to establish a match – F3 in this case since you want the salary automatically entered according to which employee name you select in F3, F4, F5, etc. Leave this value as a relative reference since you'll want the match to be relative to whatever name appears in column F. 9 4. Continue by typing a comma and then selecting the range against which the employ- ee name in column F should be matched– B3:B10 in this case. Press the F4 key to make the reference absolute rather than relative. 10 5. Complete the formula by typing a comma, followed by the number 0, a closing parenthesis, another comma, the number 2, and a final closing parenthesis. Hit the ENTER key. 11 6. Copy the formula in G3 into the rest of the cells in column G, as desired, and then, whenever an employee name is entered in column F, the corresponding salary is looked up in column C, and copied to the correct cell in column G, as shown below. 12 The Paste Special Feature The Paste Special feature is a great way to execute a transformation as you copy and paste data, without having to depend on formulae.