Using Spreadsheets in a Woodworking Business
Business owners describe how spreadsheets can help a woodworking shop get things done. April 6, 2007
Question
Can anyone tell me what they are using spreadsheets like Excel and Lotus123 for in their shops? I use it extensively for quoting, invoicing and parts layouts. Some of my spreadsheet templates are years old and have evolved into very complex forms that do a lot of my daily work for me.
I know that contributor I has always been a big proponent of this type of computer assistance. I think he has gone more to data base type programs now, though, with his changeover to more efficient manufacturing.
Also, if I understand Cadcode correctly, that CNC processing program is Excel based. If anyone can give me more info on that it would be great.
What type of things are others using spreadsheets for on a regular basis? Bookkeeping, charting progress, and growth or parts layout and design, like I do? The plan is to come up with an article on the subject, so a wide variety of uses would be great to find!
Forum Responses
(Business and Management Forum)
From contributor B:
I put all my room dimensions into a spreadsheet. I then use it to help arrive at the dimensions for my cabinets, fillers, etc, before I start putting them together in eCabinets. It makes things a lot easier.
I also have a sheet with commonly used hardware and supplies and their prices. I just punch in the quantities for the items I will use and I instantly know the cost of materials for the job. I have cells for the estimated hours for each process and estimated overhead. This is most of my estimating process.
From contributor S:
Since I got Excel a month ago, I have used it to create an easy to use program to calculate and print price tags at our retail furniture location. This is really a nice time saving quality feature for our sales staff. I have also used it to create several calculating forms for here at the shop, as well as our retail outlet. Currently I’m developing a calculator that can take the overall sales figure and estimate materials to order for the next production run, like lacquer, solid board feet, plywood, glue, nails, staples.
I have to say, if you’re going to write an article about the subject, you should write it focused more toward the perspective of someone who knows little about spreadsheets and how they work. To me, for instance, a year ago. I knew little about spreadsheets, therefore I had little idea of what benefit they had to assist me. Now that I know it’s really a universal program for any kind of common calculating (for some reason I always thought they were just for bookkeeping), the options are unlimited. I wish I knew about them a long time ago.
From contributor T:
I've been to see a lot of different companies, large and small, and have seen quite a few varieties of spreadsheet usage.
I have seen people use Excel to write letters, as they like the columnar format of the page. Oddly enough, Word does a much better job of this.
I've seen people use it for preparing quotations, probably a great use of a spreadsheet.
I've seen people use Excel as their customer database, even though there are huge amounts of database products that offer more functionality. Eventually, they do switch over to some form of database structure.
I've seen people try to use Excel as an order entry and tracking system. They spend huge amounts of time and money trying to get it to work. Guess what? Excel is not designed to do this, and they eventually buy some form of order entry and tracking software.
I am not too sure of the nature of your article, but I would hope that you could help many of these folks avoid the pitfalls of using Excel for the wrong application. It seems like many people are able to understand Excel easily, then they take this knowledge and try to make Excel do everything for them. This is a mistake, unless you plan to stay very small and not grow.
From contributor D:
Excel is great for certain things. I use it every day in my full-time job as a software analyst for reporting. Macros and formulas help a lot in automating certain tasks. Something I just saw was that Google has a spreadsheet program now as well. Looks pretty cool, and free. All online and you can share between users if you want to.
From contributor I:
Contributor T, while I agree with you that database programs are a lot more robust than spreadsheets, for a lot of the guys on this forum, MS Excel is definitely the way to go. The biggest advantage is bang for the buck. While it can't do everything a database can, what it can do is get you up and running quick. If you don't have the time to learn programming and you need something to make cut-lists or do job costing, start with Excel.
A simple tutorial about how to write a macro, or the difference between an absolute reference and a relative reference, will automate a lot of office work. A couple of Saturdays and you are on your way with Excel. Database is a career.
From contributor T:
I get my input from customers. They tell me that they jump into Excel for all the reasons you mention. As time goes on and the company grows, it becomes a nightmare to deal with. They tell me that if they had known better, they would never have gotten themselves into this mess.
There are a number of database products out there that are quite simple to deal with. You don't have to become a programmer. Some have macro functions, much like Excel. It really depends on whether you plan to stay small and not grow. If this is the case, and it is for some, then Excel for everything may make sense.
From contributor N:
We currently use Excel for:
-cutlist generation (we are currently generating cutlists manually)
-financial projections and what-if scenarios
-purchase orders
-bids
-product cost calculations
Some of these could be done better with a database, but Excel is awfully easy to get something up and running quickly. We'd like to transition to databases on some of this stuff in the near future.
From contributor P:
We started out with Lotus also. Have to agree with the database thing. But not from scratch, i.e. QuickBooks is a database.
Now the other extreme is erp software. This is probably too much for most. Where is the happy medium?
Another aspect of this is that the data generation ideally would come from the design software. If you have to pull a contributor I, then look at Filemaker, not Access.
Regarding Excel, we use it for estimating, graphs for business control, mailing lists, cash flow, calculating sales tax, workers comp, etc. Sometimes it is handy for adding up all the parts in a drawing, e.g. stile+rail+stile=x.
From contributor B:
Contributor I, I agree with you on the usability of a spreadsheet. Databases are much more powerful applications, but they require experience to define the data elements, build all the forms and reports and then write code to store, display and manipulate the data. Are you using databases for the production aspects of the business? If so, can you describe them and how they help? They certainly make sense for accounting and customer management, but I am interested in learning how they can help a small cabinet shop elsewhere.
From contributor I:
Here is an example of something that is easier to do with a database. Our current project is going to use several corbels of differing sizes. At one area of the kitchen, these corbels need to be less than 7 inches in width to fit under the built in hood fan.
This particular customer is quite interested in what her options are. Since we are doing this research anyway, we shall archive a photo of the possible corbels along with the width, height, and depth of each corbel. There will be additional fields for such information as material composition and price.
The next time we need to work a corbel into the product, we can find a thumbnail view of all resin corbels that cost less than $100 and are wider than 5 inches but less than 7 inches. This kind of reporting could probably be accomplished with Excel, but would probably not be as user friendly to get the data out.
Contributor P is right about doing the differences in database programs. We use Filemaker for our platform because it is pretty simple to learn how to run. It's interesting to note that Filemaker has an option to save your file as an Excel worksheet. With just a couple of keystrokes, the database program becomes Microsoft Excel.
If you are starting out, I would recommend Excel. As your needs change, you can move to database. It's a simple process to import all the data you generated in Excel into the database program.
From contributor A:
I'm no fan of MS products, although I use them extensively at work. If you're on a budget, you might want to look at Open Office. I use it at home and it does all that MS Office does, and it's free. Be aware that not everything translates (formatting particularly) well between OO and MS. This could be a concern if you need to go from one product to the other. Also, check the licensing. There may be restrictions on commercial use. That said, I use pretty simple Excel spreadsheets for my CAD management needs.
From contributor I:
Maybe you should include some lessons on the charting capabilities of Excel in your article. Contributor P touched on this a little bit when he said they use it as graphs for business control. Pie charts can give you breakdowns of what the scope of work looks like on an upcoming project. Bar charts will give you variance reports, i.e. show you how you actually did vs. how you thought you were going to.
Looking at this data graphically is sometimes a lot more useful than just crunching numbers. It's really easy to generate these kinds of charts in MS Excel. It's a lot harder to do things like this in Filemaker.
From contributor N:
A lot of these financial graphs are much, much easier to generate if one has a good accounting software package in place. We can look at job profitability charts and numerous other graphs with one click in QuickBooks. Manually entering the data into Excel would be very time consuming.
From contributor I:
The role of production management is not so much measuring profitability as it is improving profitability. Charting estimated labor hours before you start the job gives you some advantages in scheduling your manpower. This is probably more useful in batch production than it is with larger batch size.
We try to build no more than a couple of cabinets at a time. Our batch size per cabinet is related to how big the cabinet is when we move it off the bench, onto the truck and into the jobsite. In some cases this cabinet might be 50% drawer boxes, 20% face frame and 40% doors.
By representing this in advance on a pie chart, you can affect the sizes of each slice by how you allocate your manpower each day. In order to implement a pull system of manufacturing, you have to know not only how long something takes to manufacture, but how long it takes for the other parts as well. This is how you ensure that you don't end up waiting for a door or ending up with a pile of doors that need to be stored.
Charting is a great way to see these processes expressed with a common denominator. The charting that Quickbooks offers is great for the bookkeeping part of the day. I think that Excel's charting is better for the production management part of the day.
From contributor N:
Contributor I, I agree in most part, although I think it depends on how you're using your accounting software. We use Quickbooks more like a manufacturing software package, in that we track time by job, and within the jobs by component. So I can look up a job and see how much time and/or money was spent on, say, drawer boxes (with a fair amount of precision - our time and cost tracking isn't perfect). Quickbooks (to my knowledge) doesn't let us one-click to see charts on component time, so I'll manually transfer the data into Excel to see this data.
Excel seems *more* useful for us in projections or future analysis. Although now that I think about it, it would be pretty easy to make a copy of our QB company file, and input a bunch of dummy jobs, then the analysis tools are all there.
From contributor P:
QB has a limited number of graphs, and I don't find them useful. I like to track sales per week, value of product produced per week, sales calls per week. In the past I had a lot more graphs and a lot more people to keep track of. I like to use what Excel calls a line graph. The idea is that this keeps you painfully aware of your current status (weekly). I don't pay much attention to the dips and peaks, but the trends are key.
If you drive a car, you have a few gauges you pay attention to. If you are flying an airplane, a few more. So why not for a business? It is very easy to become complacent or introverted into the flavor of the month business concept. The graphs keep you honest and alert.
From contributor S:
I agree 100% with you, contributor P. Cars need more gauges, like airplanes. Some don't even have tachometers or shift lights. How many liters of fuel do I currently have? What's my brake bias set at? With more information I can drive better. Same with a business.
From contributor C:
We primarily use Filemaker, but use Excel to test formulas, for quick ad-hoc calculations, and for quick comparisons. Earlier in my career, Excel was the only program I used for estimating, but I switched to Filemaker when it became relational.
From contributor E:
All days are in working days:
Bookings with line graphs at 5-day, 10-day, and 20-day averages.
Production with line graphs for 5/10/20-day averages for assembly and finish sand and QC (invoiced), with same for revenue also, and revenue per production unit.
Pay scale - graph the most valuable employee to the least valuable. Y-axis is rate of pay. If all your pay was perfect, it should be a smooth line, but it isn't, so I know how to give raises equitably.
Pricing model for our products.
Incentive program.
Lumber order history, with month-end inventories, ROP calculations, pricing by different vendors for each truckload.
Daily production sheets, using information imported from MS Project.
That's just what I can remember off the top of my head.
From the original questioner:
Well, thank you all for the extensive help. I sat down and put the first draft of this thing together over the weekend and all your individual uses were very helpful in getting it going. I'll do a rewrite as needed after reading through all your posts one more time. I really didn't know where I would go with this, but it quickly became obvious that it was going to be doing an "introduction to spreadsheets" sort of article. As such, and with limited publication space, I was held to the most basic range of uses. I'm sure I could have taken far more of your examples if space had allowed, though.
