Integrating Calc Into Your Business

By: Rob Reilly
Thursday, February 5, 2004 09:34:41 AM EST
URL: http://www.linuxplanet.com/linuxplanet/tutorials/5230/1/

Calc Adds Up

Businesses everywhere need to use spreadsheets, at some point or another. Depending on the business and industry, spreadsheets can be anything from very simple lists used for sorting to kazillion cell monsters that add, multiply, and figure up gravitational forces throughout the entire universe. And we all want pretty charts so we can estimate budgets and show off our stellar company performance.

As Linux moves into small and medium sized companies, desktop spreadsheet jockeys often want to know how they will get their spreadsheets from their old proprietary platform (such as MS Excel) into a new application. They also wonder what capabilities are available in the new spreadsheet program. In this article, I'll show you both: importing an Excel spreadsheet into OpenOffice.org Calc is as easy as simply opening the file. I'll show some of the capabilities in a few minutes.

Calc is built into the OpenOffice.org productivity suite and is the premier Linux spreadsheet program. It has everything needed in a full featured spreadsheet application. With it, you can make lists, calculate groups of numbers and sort things. What makes it even more useful, from a small business perspective is that it is Open Source and freely available on the web.

Geeky Job Mirrors Business Productivity

Yes, I'm a geek and I use Calc for the grocery list. My list has food items, aisle numbers, and quantities. Before each weekly trip I sort alphabetically by item and mark what I want. When I'm done I sort by aisle and print the list. It's quick, easy, and simple. It also saves me, at least, 40 minutes a week, as opposed to trudging through the store with a handwritten list. Oddly, I've spotted lots of men diligently going over their printed spreadsheets, while women typically fly down the aisles with their handwritten lists. I don't know how they do it.

How does this silly and simplistic example relate to your business, you ask?

First of all it's a perfect application of technology to get a job done. The setup was minimal, maintenence is minimal, and, if you save your list every week, there is valuable data that could be extracted to evaluate buying habits. No, I'm not that far gone... yet. It is also uncomplicated and didn't cost much, because it's Open Source and is right there on my Linux laptop. It's the essence of getting things done in a small organization, while saving time (read: money).

OpenOffice.org Calc can help your business, the same way and more. Here are some ideas.

Calc... Go Figure

The layout and functions of Calc are very similar to other proprietary spreadsheet programs, like MS Excel. The program is remarkably easy to use and has a main screen that is used for entering and viewing your cells with tool bars across the top and left sides. Selections are generally done through drop-down menus.

Working with Calc is easy. Start up OpenOffice.org like you normally would. Then simply go to File|New|Spreadsheet. A new window will appear with a familiar looking screen will show up with cells everywhere. You're ready to start calculating.

A practical example might be to add up sales numbers for your three salesmen. You could also find each salesman's average per month and get a total revenue number for the year. Here's the list to enter. Start at cell B3 and work your way over and down. Or, just highlight the entries in this table (in your browser), then right click and copy them into the screen buffer. Then go over to Calc and paste them in. Left click B3, then right click Paste.

Next, go to C16 and click the Sum icon (looks like a big zig zaggy E, or the Greek sigma character). Cells C3 through C15 will have a window around them. Grab the bottom right corner of the window and move it up to C14. The windowed cells should now be C3 through C14. Type a return and your total (sum) should appear in C16. To carry the totals across the other two columns, highlight C16, right click and copy that cell, then left click and drag the cursor across cells D16 and E16. Right click and paste. The totals for those columns will magically appear. Go to B16, type in "Total/Salesman" and press Enter.

Now click on cell B17. Type in "Avg/Salesman" and press Enter. Move over to C17 and click the Autopilot: Functions button (just left of the SUM button that we just used). The autopilot function window will appear. Move down the list of functions and highlight "Average", then click "Next". In the Number 1 data entry box type "C3:C14". Then single click in the data entry box to see which cells were highlighted on the spreadsheet. Type "OK" to finish up the average calculation. And, there's your average for the first salesman. Right click and copy C17 over to D17 and E17, just like we did for the totals.

It would be nice to find out how much total revenue we made last year. Click on cell E19. Type "=C16+D16+E16" and then type a return. The total of the three columns will appear in E19. Go to D19, type in "Total Dough" and then enter. It's easy to make the spreadsheet more readable by highlighting row 15 and inserting a blank row. Not bad for that crew, almost 1.5 million. See, Calc works exactly the same as any other spreadsheet, that you've ever used.

This simple example could be applicable in many small businesses and could be the foundation for a basic sales tracking tool. And, entering numbers in Calc sure beats trying to use a calculator, especially if it doesn't even have a paper printout.

There are many other areas that you can explore in OpenOffice.org. Try adding whole numbers to dates. Or, enter some complex formulas.

Everybody Wants Charts

Putting a chart together is just as easy as entering figures and doing calculations.

First, highlight B16 to E18, then click Insert|Chart. Click Next on the Selection, Chart Type, and Variant windows. In the Display window, fill in the Chart Title data entry box with "Salesman Revenue Comparison." Check the X axis box and fill in the X axis data entry box with "Salesman". Check the Y axis box and fill in the Y axis data entry box with "Revenue (Dollars)." When you get all done, click the Create button.

You've just created a comparison chart with total revenues and averages for each salesman. The legend tells which salesman is which according to color. How easy was that? Left click on the graph, then grab the top of the graph box and drag it up with your numbers, if you like.

If you've ever used another mainstream spreadsheet, you should be able to get around in OpenOffice.org Calc. There are lots of wizards, templates, formatting and features to explore, with Calc. We've touched on just a few. Look for an in-depth tutorial on Calc in Solveig Haugland's book "OpenOffice 1.0 Resource Kit." Browse to http://www.getoffice.org for more information. The book covers all major topics including Writer, Calc, Draw, Impress and more. It's a must-have addition to your Linux library.

Rob Reilly is a freelance Technology Writer and Speaker. He's particularly interested in stories about Linux on the desktop/laptop, recycling with Linux and portable computing. He's currently developing seminars on Linux presentation technology, "road warrior" techniques and business web basics. Send him a note or visit his web site at http://home.earthlink.net/~robreilly.