If you’ve been following along with this series, you know we’re in the trenches of OpenOffice.org and that I’m no longer a newbie. In fact, I’m feeling pretty comfortable in the world of OpenOffice. But, if you’re new to OpenOffice.org or just curious what it’s like to explore this compelling and arguably superior alternative to Microsoft Office, you might want to back up and get your bearings.
Having already explored the basic features of Writer and Impress, I’ve moved into the spreadsheet world with Calc. But, since most people are already familiar with the basics of cells and sums and splitting, I’m going to delve into the less-used, kind-of-scary features such as validating, DataPilot (OpenOffice.org’s answer to Pivot Tables), linking, scenarios, and conditional formatting. To show you how you can use these tools to help your everyday calculating, I’ve chosen a scenario that many of you OpenOffice.org champions might need to convince the people in charge that your organization would be much better migrating to open source. I’m going to show you how to use Calc to crunch basic cost analysis numbers for migrating to OpenOffice.org.
> Gathering the Data
If you’re the IT director for a larger organization, you might have separate IT managers over each department. As a first step to gathering the data for your OpenOffice.org migration plan, you might first want to create a spreadsheet form that the IT managers can use to report information on their separate departments. Once you gather this information, you’ll be able to link it to another spreadsheet that will help you determine the cost analysis of a migration.
First, you would decide what information you want to find out and create a simple form. For our example, we’ll create fields for the managers to indicate their department, the number of users in that department, their current office productivity suite, the current licensing costs for that office suite, and the estimated average downtime the users in their department report when they make an IT change. (See Figure 1.)
Although this form is very simple and should be easy for your managers to fill out, make sure that it is filled out exactly as you want it to be so you don’t end up reentering data later. One way to make sure that the managers fill it out with predetermined values is to create a drop-down list of values from which the managers must choose their answer. To do this, you use validation.
Let’s first validate what the managers will enter for their department name. First, select the cell that the managers will use to enter their department name, then click Data | Validity. In the Allow field on the Criteria tab, select List. Uncheck the Allow blank cells option and enter your list in the list box, pressing Enter to create each new entry. (See Figure 2.) Then click OK. In your spreadsheet form, a dropdown list arrow will now appear indicating that the managers should choose a value from that list.
I’m going to show you how to use Calc to crunch basic cost analysis numbers for migrating to OpenOffice.org.
Next, let’s say you want to make sure the manager’s finger doesn’t slip on the keyboard when he is typing in the value for the number of users and accidentally enter 2500 instead of 250. Although this scenario is unlikely, it illustrates how you would use validation to make sure a value is in a specific range.
Again, click the cell in which the manager would enter the number of users, then select Data | Validity and the Criteria tab again. This time in the Allow field, select Whole Numbers, in the Data field, select Less Than, and then enter the maximum number. Because in our hypothetical company, no department has more than 1000 users, I entered a value of 1000. This time, to make doubly sure the number of users are correct, I’m going to click the Error tab and enter a custom error message that will stop the manager’s action if he tries to enter a large number. In the Error tab, I’ll choose Stop as the action and then add a title and message to come up if the manager enters a value higher than 1000. (See Figure 3.)
One final way to add validity to a form is to create a help message that will explain to the managers what they should enter when they click the input cell. For instance, to explain to the managers how to estimate their department’s downtime, click the cell that will hold the average downtime number. Then select Data | Validity and this time click the Input Help tab. Check the Show input help when cell is selected box and then enter the custom help message. Now, when the manager clicks this cell, he will see how to calculate the average estimated downtime. (See Figure 4.)
One final way I can make sure the form is not only formatted correctly but also doesn’t change is to protect the cells I want to be constant. In Calc, you need to take a couple steps to protect certain cells on a sheet because all the cells in a sheet are protected by default.
Once I link to all the data I need in my cost analysis, I can begin to see what a current software upgrade might look like. I have the cost per user for each of my cost categories and the total cost for each category, as well, but the cost analysis is to compare costs of upgrading to the next version of the proprietary software my hypothetical organization is currently using.
First, press CTRL + A to select all the cells in the current sheet. Select Format | Cells and click the Cell Protection tab. Uncheck the Protected check box. Now, you can select the cells you want to protect and repeat the same steps to re-protect these cells. Because cells won’t be protected unless the sheet is protected, you must next protect the sheet. Select Tools | Protect Document | Sheet and enter a password. Now, when you save the document and send it to the managers, they will not be able to change the cells you protected without the password.
> One More Stat
Although the data you collect will tell you most of the stats you need to begin to figure the cost analysis to migrate to OpenOffice.org, one other stat you might want to collect is the average cost of a helpdesk call. In our hypothetical company, let’s say that as the IT director you have a spreadsheet that holds the raw data of all the helpdesk calls for one week. The spreadsheet shows the date, the department, the caller’s name, the duration of the call, and the cost of the call ($8 x the duration of the call). This data is organized into columns with each item of information in a column. Basically, it’s just raw data with only one calculation built in.
One way you can begin to interpret this data is to use a DataPilot (Calc’s equivalent to Excel Pivot Tables). To start a DataPilot table, you simply select the upper left corner of your data (there’s no need to select the entire data range) then select Data | DataPilot | Start. Make sure Current Selection is selected on the next dialog and then click OK. A dialog will then appear with drag and drop fields named after your column headings. (See Figure 5.) You can drag and drop the fields in four separate areas. If you drag and drop a field in the Page Fields area, you’ll have the option to filter the data according to that field. If you drop a field in the column area, that field will be dispersed in columns and if you drop a field in the row area, that field will be dispersed in rows. If you drop a field in the middle Data Fields area, you can analyze and calculate according to that field. By default, a sum calculation will come up. To change that calculation, just doubleclick the field and choose a formula from the dialog that appears.
For our hypothetical situation, I’m going to drag the Date field to the Page Fields area, the Department field to the Row area, the Caller’s Name field to the column area, and the Time Spent and Call Cost fields to the Data Fields area. I’ll also change the calculation in the Data fields area to Average instead of Sum. Although I don’t need to for this DataPilot table, for future tables I can also click the More button to change where the DataPilot table information will be shown and other options on how the data will be displayed. Once I click OK, I can clearly see the average cost for each person who called and each department, as well as the total average costs and total average time spent. (See Figure 6.)
> Creating the Cost Analysis
To create the cost analysis, you first need to create the bare bones of the factors you need to determine. For our hypothetical cost analysis, we need to know number of users, licensing costs per user, deployment labor costs, hardware costs, help desk costs, training costs and user disruption costs. Because many of these data points are in the forms from the departments and the help desk spread sheet, I can simply link to the data in these spreadsheets, so if that data changes, it will automatically change on my analysis spreadsheet.
If you have inserted all the forms into your current spreadsheet document, all you need to do to link the cells in each spreadsheet is select the cells while you are creating your formula. So, to create a formula that would calculate the total number of users on my cost analysis, I begin by entering an equals sign to indicate I want to create a formula. Then I manually scroll through the sheets in my spreadsheet document, selecting the cells that indicate the number of users in each department and then manually entering a plus sign in between each. My completed formula says:
=’Customer Service’.B2+Management.B2+Accounting.B2+Fulfillment.B2+Operations.B2+Quality Control’.B2+Sales.B2
If, however, the forms from the department managers aren’t all in one document, I can still link to the data, I just first must create a name range in each of the forms. To create a name range, I simply select the field that indicates the number of users and select Insert | Names | Define. Then, I can enter a name for the cell according to the department. (Note: Names must not contain spaces). Once these name ranges are defined, I can link to them in my current document and then use them to create a formula to calculate total number of users.
To link to external data, I select Insert | Link to External Data and click the button with the ellipse (...) to browse through my file system. After I select the document that holds the correct named range, I select the range from the available tables/ranges box. I then check the Update every box and select the increment at which I want the link to update, and then I click OK. (See Figure 7.) Now, the information will automatically update as I update the parent file and I can create my formula for the total number of users from this linked data.
I can repeat these steps as many times as necessary to compare all the different scenarios for my office suite migration. Once all the scenarios are in place, I can simply use the drop-down arrow to switch values and see the different calculations it creates. Obviously, from these calculations, I can see that the cost of migrating to OpenOffice.org is significantly cheaper.
Once I link to all the data I need in my cost analysis, I can begin to see what a current software upgrade might look like. I have the cost per user for each of my cost categories and the total cost for each category, as well, but the cost analysis is to compare costs of upgrading to the next version of the proprietary software my hypothetical organization is currently using. Although I can simply copy the same information and change the figures to reflect a choice to go to open source, a cleaner, easier way to compare the data is to use Calc’s scenario feature.
To create a scenario, I first select the values that I want to vary and make sure there is a blank cell above these values. I then select Tools | Scenarios. In the dialog that appears, I name the scenario Proprietary software because that’s what my current values represent. I choose a color to represent the scenario, make sure the Copy back check box is marked, and click OK. My same values appear, but they are surrounded by a blue border and a drop-down list next to the top title. (See Figure 8.)
Now, to create another scenario, I select the same values again and select Tools | Scenarios again, this time entering another name and color for the second scenario. When I get back to my document, I can then enter in new values that will be saved with the new scenario.
> Conditional Formatting
Just in case the numbers don’t speak for themselves, I can also use conditional formatting to truly highlight how cost-effective OpenOffice.org is. Conditional formatting is usually used to make a value a different format when it meets a certain condition, such as the text being green when the value is lower than $100,000.00.
To apply conditional formatting, you first must create styles. Just like creating styles in Writer, the easiest way to create styles in Calc is to first format a cell the way you want the style to be formatted. So, in my analysis spreadsheet, I’ll first type a random value in a blank cell and then select that cell and make any formatting changes. Since I want the number to be red if the value is over $100,000.00 and green if it’s under $100,000.00, I’ll first make this cell’s formatting red and select the cell again. Then, I’ll open the Styles and Formatting window by pressing F11 and click the New Style from Selection button. I’ll name the style “red” and then do the same with the “green” style. Once I have these styles set, I can erase the contents of the cells I used to make the style.
To apply the conditional formatting, I first select the cell that I want the conditional formatting to apply to and then select Format | Conditional Formatting. In the dialog that appears, I then can define my conditions. For the first condition, I’ll select “Cell value is greater than 100,000” and then select my “red” style from the Cell style dropdown list. For the second condition, I’ll select “Cell value is less than 100,000” and then select my “green” style from the Cell style dropdown list. (See Figure 9.) When these conditions are set, I can simply click OK and the value in my selected cell will either turn green or red, showing in color how much my organization can save by switching to the OpenOffice.org suite.
> Summing Up
Although all the values in this article were hypothetical, using OpenOffice.org Calc to prove how cost-effective a migration to the OpenOffice.org suite is might not be a bad way to show management how much money you can save them. And, using Calc’s lesser-used tools, like validation, DataPilot tables, linking, scenarios, and conditional formatting can show them in an even more impressive format.