Woodworking history can be traced back thousands of years, with gifted craftsmen found in virtually all cultures across the centuries and around the globe.  Though separated by time and space, modern woodworkers share two common interests with these ancestors:  working with wood and the tools that make this possible.  While modern times have brought new hand and power tools for our use, the technology era has brought us one more option: the computer.

The addition of computers to the woodshop brings the well known capabilities of bookkeeping, word processing, project design and controlling CNC machinery to name a few. However, there is one often overlooked use: the power of spreadsheets to help plan, calculate and organize in the shop.

What’s a spreadsheet?

A spreadsheet is nothing more then a series of columns and rows into which data is entered to be mathematically processed. Outside of the computer world this work is done on ledger sheets, typically used by bookkeepers and accountants. Very early on in software development it was realized that a computer could do the math required in ledgers much faster. This led to the development of the first software spreadsheet, a program called VisiCalc (for Visible Calculator). By 1983 Lotus 123 software had appeared.  It revolutionized the accounting industry with a more advanced set of calculation features and had a good deal to do with the early spread of personal computers throughout the business world.

In a simple spreadsheet example, the left hand column contains “wooden widgets”, grouped by color. The columns to the right contain sales data for each color group. The weekly dollar totals for each color are calculated by formulas that multiply the price each times the quantity sold. Then an addition formula inserted in the bottom cell of each column automatically generates the weekly totals.

Spreadsheets can do more than add and subtract though. Almost any mathematical task can be applied to a cell, including algebra and calculus. For example, some of the spreadsheets discussed below take advantage of the Sine and Cosine tables to make their calculations. Some commonly created spreadsheets handle payroll, accounting, inventory tracking, job cost analysis, cut lists and price quotations.

The most widely used spreadsheet programs today are Microsoft Excel and Lotus 123. Other programs operate similarly and most are compatible with one another. Spreadsheet programs don’t cost a lot, and many are available as shareware and freeware.

Spreadsheets in the office

Now that you understand the basic concept of spreadsheets, the question becomes how to use them in your shop. As discussed above, accounting is high on the list even though you can purchase programs designed specifically for that task. However, if you operate a small shop, or are just starting out, you can save some money by doing this with spreadsheets.

Beyond accounting the options are extensive. My business bookkeeping is done in one of those specialized programs, but I prefer to use Microsoft Excel to do all quoting and invoicing. Years ago I set up spreadsheet “template forms” for these tasks. They incorporate my customer database and pricing for all regularly made products. When doing a quote I can quickly select an existing customer or enter a new one, and then refer to secondary worksheets to determine pricing.  All totals are automatically calculated at the bottom right of the form, including shipping charges and sales tax. This all takes place through the use of a “macro”, a tool that runs multiple commands at the press of a single button.

Macros are one of the most useful features of spreadsheets. They give you the ability to record a series of commands and then run them repeatedly without having to reselect each command. On my quote form the “Process and Print Quote” button runs a macro that permanently sets the date and then asks me to enter reference details, shipping information and payment terms. Next it queries for applicable custom tooling costs, shipping charges and Connecticut state sales tax, and then displays the amount of my standard 25 percent deposit.  Finally, it prints the form and also sends it to my fax software. Throughout this process each of the automatically calculated user input cells can be accepted as presented, or altered if needed.

The beauty of macros in Microsoft Excel is that they use Visual Basic, a “plain English” set of programming commands. These commands are logically tied to the functions they provide. What is even nicer though is that you can create macros even before you get a handle on the programming language. This is done with the “macro recorder”, a tool that allows you to select a series of tasks that are automatically recorded to the hard drive. Once created they can be run again whenever needed.

Another area in which the shop office can use spreadsheets is in planning business development. Many people use the “charts” capabilities of spreadsheets to examine business activity. Take the wooden widgets spreadsheet, for example. If you wanted to know the busiest weeks over a five-year period you could visually examine 60 columns of data and record the strongest weeks. Or you could set up a chart to give you a computer-generated graphic evaluation of activity. These graphics can be bar, pie or other styles, and they’ll quickly show you the desired relationships.

You can also analyze data with “filtering”. Let’s say you want to know what color wooden widgets sold for more than $1,000 in any week during the five-year period. Filtering can be set up to temporarily hide all sales data that doesn’t meet that minimum $1,000 standard. You are left looking at the sales that meet your criteria, clearly showing the information you need to help plan future production.

Similar to filtering, but not temporarily hiding data, is “sorting”, which rearranges multiple columns and rows of data either numerically or alphabetically. For example, if you had several hundred hardware items listed in a column, with associated costs and in stock quantities in adjacent columns, you might have a hard time finding the specific item you need. The obvious solution is to alphabetize the list, which is what sorting will do. By selecting all related data, and choosing “sort” from the “data” menu, you can arrange the names column alphabetically. The other columns will follow suit, keeping the correct quantities and costs correctly aligned.

Spreadsheets for shop work

Spreadsheets are useful tools outside of the business office as well. They can do a wide range of tasks including part size calculations and the generation of cut lists. They have the ability to condense many hours of math calculations into minutes of data entry.

A good example of this is the parts layout form we use for making our curved mouldings. We input moulding data such as radius, profile size, chord length and wood species. The spreadsheet then automatically calculates the part sizes needed to make that moulding. It also gives highlighted cautionary warnings when certain conditions exist, such as a specific moulding profile needing special attention or data calculations indicating a moulding is too large for our 6’ x 10’ CNC table. When the “Print Form” button is pressed it sends the form to the printer and generates identification labels that will be applied to the backs of the completed mouldings.

We regularly use a similar spreadsheet form to produce radius, chord length, arc length and arc height information when known values are entered. It functions because derivations of the algebraic formula for calculating radius have been entered in the appropriate cells. Instead of doing the algebra to find out the radius of an arc when width and height are known, you can enter that data into the correct cells and the radius is instantly shown. Subsequently entering the calculated radius displays the arc length.

Yet another powerful use for spreadsheets is a form that will yield rail, stile and panel sizes for cabinet doors. This can be designed so that entering a finished door size generates cut lists of all needed parts, including extra length and width for machining cope and bead cuts and appropriate panel sizing to allow for seasonal movement. Instead of spending an hour with a calculator, you can spend a couple minutes at the keyboard to get the same result.

Conclusion

Spreadsheets are an extremely versatile tool in the workshop. What I have discussed here just begins to touch on what they can do to increase productivity. Forms can be created that will do many of the organizational and time consuming calculations that confront you daily. Beyond the above examples is a wide range of uses. Options go from the simple entry of individual cabinet sizes to ensure that a total run fits along a wall, to the complex integration with CAD/CAM software to generate cabinet parts and CNC code directly from a completed cabinet list.

How far you go in incorporating spreadsheets into your work will be limited only by your interest in the subject. However, if you do invest the time and effort to learn you will certainly see a return on that investment.

B.H. Davis is the owner of B.H. Davis Co., which makes solid wood curved mouldings in Grosvenordale, Conn.