JCAEDE v1n1 - Integrating Software Applications to Generate Interior Design Proposals


Volume 1, Number 1
Fall 1995


Integrating Software Applications to Generate Interior Design Proposals

Susan M. Winchip
Department of Family & Consumer Sciences
Illinois State University

Abstract

Interior designers are realizing the value of using computers and off-the-shelf software applications such as CADD (computer-aided drafting and design), word processing, and spread sheets for proposal generation. Many of these applications are currently used in a stand-alone manner. To reduce manual work and errors interior designers need software applications that are integrated. This project illustrates how existing software applications can be integrated in order to greatly reduce or eliminate entry by hand references to non-electronic data, manual data extracts from drawings, and the redundant transcription of data required to generate interior design proposals.

Interior designers are realizing the value of using computers and off-the-shelf software applications such as CADD (computer-aided drafting and design), word processing and spread sheets for proposal generation. However, many of these applications are currently used in a stand-alone manner. In this mode, the development of CADD drawings require the manual transcription of geometric features from supplier catalogs. When a drawing is complete, product lists must be manually extracted from drawings. The product lists are then used to develop specifications and pricing proposals by entering by hand cross- referencing product descriptions and prices from vendor catalogs and entering them into spreadsheet and word processing applications.

This stand-alone use of computers is tedious, since it requires numerous transcriptions of the same data, and is implicitly error prone. Changes to the office layout require that many of these activities that are entered by hand be repeated each time a change is made. Further, these manual means of achieving integration do not add value to the design processes, are not productive, and are tedious for students and designers.

Some suppliers are taking steps to aid the designer in the generation of design proposals. Herman Miller provides two and three dimensional graphic symbol libraries for their office furnishing products integrated with AutoCAD. Designers can select products from pop-up menus based on product group and dimensional characteristics. When products are added to an office layout drawing, non-graphic attributes are maintained in the AutoCAD data base indicating the product number. Another software utility provided by Herman Miller allows product lists to be extracted from selected areas of the layout or for an entire drawing. This feature makes it easier to identify the products and quantities that will need to appear in specifications and pricing proposals.

While this approach facilitates the development of drawings and the subsequent generation of product lists, it only addresses the first phase of the proposal development process. The generation of specifications and pricing proposals still requires that the designer manually cross-reference the product descriptions and prices in vendor catalog, and enter them into spreadsheet and work processing applications.

The purpose of this project was to illustrate how interior designers and students can integrate existing software applications in order to greatly reduce or eliminate entry by hand references to non-electronic data, manual data extracts from drawings, and the redundant transcription of data required to generate office design proposals. This was accomplished by passing data in an electronic form from one software package to the next. The project included the integration of three off-the-shelf software applications (AutoCAD, Quattro, and WordStar) with the Herman Miller graphic product libraries and utility software.

Procedures

Since software installation and usage procedures can vary greatly depending on the specific software packages selected, the version of each package, and the configuration of the computer hardware, it would be impractical to provide exacting step-by-step procedures. A sophisticated computer user could setup a similar system by referring to the following procedures in conjunction with the installation and usage procedures provided with the individual software packages. Less experienced users would be well advised to solicit the assistance of computer support personnel. In consultation with a computer software specialist, this educator completed the project by following the procedures listed below:

  1. The following software programs were selected, procured, and installed: AutoCAD, Herman Miller symbol library for AutoCAD, a spreadsheet, and a word processing package. In this case Quattro was used for the spreadsheet and WordStar as the word processor since they were available and already installed on the computer. Microsoft EXCEL and Microsoft Word would work as well since they are highly integrated packages. AutoCAD was required since the Herman Miller symbol library and "take- off" program were developed for AutoCAD.
  2. The product specifications database was developed using the spreadsheet based on Herman Miller product literature and price lists. This information was typed into the spreadsheet since it was not available from Herman Miller as a computer database. Each row in this database contained three fields; the Herman Miller product number, a brief product description, and the unit price. The product number was used as the index field.
  3. A proposal template containing text common to nearly all proposals was developed using the word processing package and then saved on the disk. The template could be loaded from the disk and modified to save re-typing the same information.
  4. The office layout was developed in AutoCAD using the Herman Miller symbol library. When a piece of furniture was selected from the symbol library and added to the office layout, the associated Herman Miller product number was also stored in the drawing database as text on a different layer of the drawing. The completed office layout was then plotted for later presentation to the client.
  5. Once the office layout was complete, a special "Take-Off" program supplied by Herman Miller was run to extract each of the product numbers that appeared in the drawing. It then counted the quantity of times each product number appeared in the drawing database. The output of this operation was a file that was easily read into the spreadsheet program. Each record in the file contained two fields: a Herman Miller product number, and the number of times that a product number appeared in the drawing.
  6. The next operation involved "merging" or "joining" the take-off file containing the product numbers and quantity, with the product specification database using the spreadsheet program. The merger of these two databases resulted in a new spreadsheet where each row contained five fields: (a) the product number from the take-off file, (b) the associated description from the specification database, (c) the quantity from the take-off file, (d) the unit price from the specification database, and (e) a calculated extended cost as the product of the quantity and the unit price. This spreadsheet was saved as a text report so that it could be included in the proposal to the client.
  7. The proposal template was then loaded into the word processing package, and the "merged" spreadsheet report was included as a table in the proposal. The text of the proposal was then modified and personalized for the specific client.
  8. The proposal was printed and packaged with the plotted drawing for presentation to the client.

Documentation of Solution

The system that was developed by this project aids in the preparation of design proposals. It allows an individual to select graphic depictions of furniture items from pop- up menus in AutoCAD. A list of product numbers and quantities is extracted from the drawing file. This list is merged with a product specifications and pricing database to generate an accurate report indicating product numbers, product descriptions, quantities, unit prices, total prices, and total project cost. The designer then merges this report with a template proposal and makes the necessary customer-specific changes. The integration of data flows between these software applications provides the following benefits over the stand-alone use of the same systems:

  • Data transcription errors are eliminated since data is transferred in electronic form.
  • Accurate and complete accounting of all products included in the drawing.
  • Proposals and specifications can easily be updated when changes are made to the office layout.
  • Proposals and specifications can easily be updated when changes are made to product specifications and prices.
  • Overall time savings and quality improvements due to removal of tedious manual processes.

The remainder of this section describes the technical aspects of the solution. It includes a system data flow diagram that graphically depicts the flow of information between the various software applications. It also provides narrative descriptions for each of the process steps and describes how each step acts on the associated input and output data. In consultation with a computer software specialist these procedures should take approximately three hours.

System Data Flow Model

Figure 1 illustrates the major processes, associated software packages, and data files that were integrated during the course of this project. Software processes are represented as circles. Data files passed between software applications are identified between parallel lines. Arrows indicate the direction of the data flows between software applications.

Figure 1. System data flow model.

Develop Drawing

In this process, an office layout is created using AutoCAD in accordance with the needs of the customer. Graphic depiction's of office furnishing products are selected from the Herman Miller Product Library. This library includes AutoCAD graphic depiction's for office furnishings as well as associated non-graphic product number attributes. When the designer selects a furniture product from a menu and places it in the drawing both the graphic depiction and the non-graphic product number are stored in the AutoCAD drawing file. The product number is stored as a non-graphic attribute and hence does not appear on the drawing.

The result of this process is the CADD Drawing File that can be recalled and modified at a later date and is plotted on paper for presentation to the customer. The drawing file also serves as the input for the Attributes Take-Off process. A plot of the drawing is included as part of the Customer Proposal Package.

Attributes Take-Off

In this process the list of all of the product numbers and the quantity of each is extracted from the CADD Drawing File using the "Take-Off" utility supplied by Herman Miller. This utility reads the drawing file, extracts the associated non-graphic product numbers, groups the list by product number, determines the quantity of each product, and creates the Product/Quantity List suitable for input into a spreadsheet program. Each row in this file contains a product number and the quantity of the product in the drawing. Figure 3 illustrates a printout of the take-off file generated by the Herman Miller software utility as it appears when imported into a Quattro spreadsheet.

Figure 3. Herman Miller "Take-off" File including product/quantity list. 

 

PRODUCT			QUANTITY
_______________________________________________________
A1120.6730E	A	2
A1120.6730N	A	2
A1120.6736E	A	3
A1220.67F	A	3
A1250.67H	A	2
A2310.2436	B	1
A2310.2460	B	1
A2332.2436	B	1
A3351.1330	C	2
A3351.1336	C	1
A3410.1630	D	2
A3410.2036	D	1
A3610.30	A	2
_______________________________________________________

Generate Project Specification and Pricing Report

In this process, a Project Spec/Pricing report is generated based on the merger of the Product/Quantity List and the Product/Price/Spec List. The Quattro procedures developed to generate this report were designed to be general-purpose in nature and hence would work for any office design providing that the selected products appear in the Product/Price/Spec List.

The Product/Price/Spec List was entered by hand into a Quattro worksheet including product number, product description, and product unit price based on a recent Herman Miller catalog. This list contains all of the items referenced in the drawing as well as some items that were not used in the drawing. Since this was manually created, it is not a complete price list for all Herman Miller products. Figure 4 demonstrates a printout of the Product/Price/Spec List portion of the worksheet.

Figure 4. Product/Price/Specification list. 


Product No.	Product Description	Price	
_______________________________________________________
A1120.6730E	67 x 30 Panel W/PWR	574
A1120.673N	67 x 30 Panel W/O PWR	469
A1120.673E	67 x 36 Panel W/PWR	636
A1220.67F	Fabric Connector Kit	199
A1250.67H	Panel End Cap Kit	41
A2310.2436	Hanging Work Surface	271
A2310.2460	Hanging Work Surface	637
A2332.2436	Corner Work Surface	441
A3351.1330	Flipper Door Unit	407
A3351.1336	Flipper Door Unit	431
A3410.1630	Tackboard		187
A3410.2036	Tackboard		267
A3610.30	Tool Bar		63
A3510.2448	Marker Board		275
A0423  DT	Paper Organizer		102
G7411		Message Holder		113
G5210.30DA	Lateral File		719
M1100		Scooter Stand		332
G7630.08	Telephone Tray		75
_______________________________________________________

The same worksheet also contains the necessary Quattro procedures for generating the Project Spec/Pricing report. The template worksheet is first loaded into Quattro, and the Product/Quantity List is then included in a designated area in the spreadsheet. Based on a merger of the Product/Quantity List generated by the Herman Miller "Take-Off" program with the Product/Price/Spec List that was entered previously, a Project Spec/Pricing report is automatically generated by Quattro.

Each row in the Project Spec/Pricing report worksheet includes the Product Number, Product Description, Quantity, Unit Price, and Total Price for each product included in the drawing. These values are determined as follows:

  • The Product Number is transferred from the Product/Quantity List
  • The Product Description is transferred from the Product/Price/Spec List via a VLOOKUP of the product number
  • The Quantity required is transferred from the Product/Quantity List
  • The Unit Price is transferred from the Product/Price/Spec List via a VLOOKUP of the product number
  • The Total Price is calculated by multiplying the quantity by the unit price
  • The Project Total is based on the SUM of the total price column
The Product Spec/Pricing report is printed to a file for subsequent inclusion in the WordStar Proposal template. Figure 5 illustrates an example printout of the Project Spec/Pricing report that is generated as a result of the merger of the Product/Quantity List and the Product/Price/Spec List.

Figure 5. Worksheet including product number, product description, quantity, unit price, and total price. 


Product No.	Product Description	Quantity	Unit Price	Total 
Price
_________________________________________________________________________________
A1120.6730E	67 x 30 Panel W/PWR	2		574.00	      1,148.00
A1120.6730N	67 x 30 Panel W/O PWR	2		469.00	        938.00
A1120.6736E	67 x 36 Panel W/PWR	3		636.00	      1,908.00
A1120.67F	Fabric Connector Kit	3		199.00		597.00
A1250.67H	Panel End Cap Kit	2		 41.00		 82.00
A2310.2436	Hanging Work Surface	1		271.00	  	271.00
A2310.2460	Hanging Work Surface	1		637.00	  	637.00
A2332.2436	Corner Work Surface	1		441.00		441.00
A3351.1330	Flipper Door Unit	2		407.00		814.00
A3351.1336	Flipper Door Unit	1		431.00	  	431.00
A3410.1630	Tackboard		2		187.00		374.00
A3410.2036	Tackboard		1		267.00		267.00
A3610.30	Tool Bar		2		 63.00		126.00
_________________________________________________________________________________
		Project Total					      8,034.00

Product Proposal

In this process the Proposal Template is loaded into WordStar and customer-specific modifications are made to the text as required. The Project Spec/Pricing worksheet file created by Quattro is then merged with the proposal template to indicate each specific Product Number, Product Description, Quantity, Unit Price, Total Price, and Project Cost. This process results in the generation of the Project Proposal that will be delivered to the customer. A copy of the finished proposal is illustrated in Figure 6.

Figure 6. Customer proposal including proposal, quantity, price, and total project cost. 


Marshall Industries
Paul Marshall, President
XYZ East 23rd Street
New York, NY

Dear Mr. Marshall:

This letter will serve as our agreement in outlining the products to be specified by Design 
Unlimited, Inc. in connection with the furnishing of new offices at XYZ East 23rd Street.

In this connection Design Unlimited, Inc. agrees to provide the following products:

Product No.	Product Description	Quantity	Unit Price	Total 
Price

A1120.6730E	67 x 30 Panel W/PWR	2		574.00	      1,148.00
A1120.6730N	67 x 30 Panel W/O PWR	2		469.00	        938.00
A1120.6736E	67 x 36 Panel W/PWR	3		636.00	      1,908.00
A1120.67F	Fabric Connector Kit	3		199.00		597.00
A1250.67H	Panel End Cap Kit	2		 41.00		 82.00
A2310.2436	Hanging Work Surface	1		271.00	  	271.00
A2310.2460	Hanging Work Surface	1		637.00	  	637.00
A2332.2436	Corner Work Surface	1		441.00		441.00
A3351.1330	Flipper Door Unit	2		407.00		814.00
A3351.1336	Flipper Door Unit	1		431.00	  	431.00
A3410.1630	Tackboard		2		187.00		374.00
A3410.2036	Tackboard		1		267.00		267.00
A3610.30	Tool Bar		2		 63.00		126.00
_________________________________________________________________________________
		Project Total					      8,034.00

If the above meets with your approval, kindly sign and return together with your deposit in 
the amount of $3,000.00

Very truly yours,



Design Unlimited, Inc.

ACCEPTED ____________________________  DATE ___________________

Conclusion

To reduce manual work and errors interior designers need software applications that are integrated. This project was utilized with students and received favorable comments. The students were pleased to have a system that efficiently coordinated the many tasks required to complete their commercial projects. This project seeks to further knowledge of the integration process by providing: (a) a graphic data flow diagram and narrative description of all aspects of the system including the major process steps, software packages, file formats, software procedures, and data entities required to support the integrated system; and (b) an example of an office layout proposal including a CADD drawing and proposal including pricing and specifications. These results will help interior designers and students to effectively and efficiently utilize computers in the design process.

References

Autodesk (1992). AutoCAD render reference manual: Release 12. Peterborough, NH: Author.

Herman Miller (1992). Herman Miller graphic product libraries and utility software. Zeeland, MI: Author.

Quattro Pro (1992). Quattro pro version 4.0 user's guide. Scotts Valley, CA: Borland International.

WordStar Professional (1988). WordStar professional release 5. San Rafael, CA: MicroPro International Corporation.