Quoting With 20 20 Technology Software
While it's a pretty good program, I've found that 2020 Technology software for kitchen design leaves a bit to be desired. Having it spit out an itemized list, with itemized retail prices, is not possible without some finagling. Here's one way to get from a design in 2020 Technologies software to a list, with prices, that you can hand to a customer.
As you probably know, 2020 will spit out an itemized list. This is obviously great for using as a guide, especially when you want to make sure you don't miss anything while placing the order with a cabinet manufacturer. But the reports that come out of 2020 show list prices. See this 2020 Technologies Software quote if you don't already know what I'm talking about. There's a grand total at the end, and that's the point when a discount against list price is applied, giving you a new, retail, grand total.
Great you say, but what if the customer then wants to pick things apart at their leisure (not in your showroom) and get the cost down a bit? Unless you go over how to get from list to retail with them (yes, I know it's only one multiplication step against list price) these list prices mean nothing. So why don't you give them a list with retail prices on the individual items? Here's how. Not that the images here are small, but link to the full size versions if you want to click them.
Start by firing up a report. I've found the easiest to convert is Quote. Once you're into the quote, you need to export it. One of the options is to dump out to an application, and one of the choices of applications is Microsoft's Excel. I personally don't use Excel; I'm an opensource guy when I can be. 2020 Technologies software is about the only proprietary piece of software that I use on any kind of a regular basis (having switched to Linux years ago, I get along quite well with opensource software). I don't even have access (no pun intended) to a copy of MS Office; instead I use OpenOffice. The rest of this little walkthrough involves using OpenOffice Calc.
You can export to either a file or Excel application. I choose to send it right to Excel. It fires up LibreOffce Calc though, as I refuse to buy MS Office. Calc (both LibreOffice and OpenOffice) work fine. Now you've got a spreadsheet version of the report many folks print off for their customers.
Step one is to get rid of the first several rows of data. I usually delete everything down to the supplier name, which is I believe around row 23 (above the red arrow and #1 in the following image). I also hold the Ctrl key and pick the Wall doors and Tall doors line. Once everything that's going is highlighted, right click on one of the highlighted row numbers and select Delete Rows in the menu that pops up. After that, rename cell A27 (but it's A2 now) from Base doors to just Doors.
This is what I end up with after a bit more finagling. I cut the Doors line and stuck it up next to the manufaturer name, then delete some extra blanmk rows between that top line and the Item, Qty, User Code line. The next step is to get the list prices on the same lines as the line number, quantity, SKU, and description. Why 2020 doesn't put the prices on the same line to begin with is beyond me…
I start my highlight at the first price (cell G5 in the next image), then drag down until the last item list price before the grand total. Then I cut that out (Ctrl + x) and scroll back up to the first line. Click on the box where the price OUGHT to be (G4) and paste (Ctrl + v) your data. It should be lined up now.
There are empty columns, and skinny colums. Delete the two empty columns (D and E) and make the columns with SKUs and Descriptions (which should now be C and D) columns wider, so that the whole SKUs and descriptions fit into single cells without running over into adjacent cells. Now, you might have noticed at this point that there are many blank rows in your spreadsheet, in between each row of data. There are usually one or two blank lines between one item and the next. Let's get rid of them, shall we?
Click the upper left box to highlight the whole spreadsheet. Go to the Data menu, then Filter, and click on Standard Filter. In the first Field Name, make sure it says Column A. The condition should be =. The Value should be empty. Hit ok.
Now go right click on one of the row numbers, and when the menu pops up click on Delete Rows. You've just deleted all those blank rows. Go back into the Data menu, then to Filters, and then click Remove Filter. Your rows with data should reappear. This is on a Windows XP box using OpenOffice 3.2.1. When I did the same maneuver on a Linux box running 3.2.0, the procedure was a little different. Instead of clicking Remove Filter, I had to have every row highlighted again, get back into Standard Filter, and make the Value for column A be "not empty." Mileage may vary depending on your operating system and version of OpenOffice.
Now, to give retail prices. Your first list price should be somewhere in the vicinity of E2. Go to F2 and start typing. Type, exactly what's between the quotes: "=sum(E2*x*y)" where x is the multiplier given to you by your cabinet manufacturer, and y is what you mark the cabinets up. If you're multipler is .5 and your markup is 70%, then the formula would be "=sum(E2*.5*1.7)".
That should be your retail price for whatever item is on that line. Highlight that cell, and hit Ctrl+c to copy it. Go down to F3 and hit Ctrl+v to paste it. This should give you retail for the next item. I do this four or five times for a big kitchen, then I'll highlight all five of those rows and copy them. Afterward I'll paste them down the sheet (going five rows at a time now) until I get to the bottom.
While these prices are retail for the item on that line, this formula is still not taking into account upcharges like finish or wood species. To figure that you'd have to (in this case) do something like E2*1.15*.5*1.7, if the upcharge in question cost 15%. People I've quoted for so far have been happy with the regular retail, then upcharges at the end. The number I get next to the grand list total cell should be what 2020 gave me for a grand retail total.
Once I'm sure of that, I erase all of the rows below the retail cell with my formula in it. Up at the top, I have a row with column names in it (Line#, Qty, SKU, etc) and that's about it. You can either print this off for the customer, or email it. I wouldn't recommend emailing the spreadsheet out, since it's got your formulas in it. Export to pdf and email that instead. If you're using Openoffice, there's a button by default up top that exports the file to pdf. If you're using Excel, you'll have to get some other tool. I'm partial to PDFFORGE's pdfcreator which is an opensource pdf printer that will run in Windows. It is freely downloadable and acts like a printer when you install it. Select PDFcreator as a printer when you print a document, and it will create a pdf file for you instead of actually printing something.
Grab THIS SPREADSHEET if you want, as well as THIS PDF. They are both what I end up with when I've finished hacking up a 2020 technologies software kitchen quote. ADDENDUM: Following is an email I received from someone in support. I hope I don't get them in trouble…
Hey Craig, Great article. I learned some handy stuff that I will pass along to other clients. I have also saved the link and will pass it along if you don't mind. You are correct in your assumption that you cannot modify/manipulate the reports from 2020. They are just templates and while it has been mentioned to the programmers since version 8.0 that many of our clients have requested the ability to play with the reports it has not yet been addressed. Again, great article.