My Professional's Guide To Robust Shreadsheets book discusses a number of example worksheets. Some of them were mentioned by name while others are only shown as screen shots. Many of them were constructed to illustrate a point rather than to function as a stand-alone model. For this reason, most of these worksheets do not follow all of the concepts discussed in the book.
This page provides brief a location where you can download each file one-at-a-time. This allows you to download just the files you want rather than getting a massive .ZIP file that you have to fool with uncompressing. For each file that you wish to download, simply click on its name.
The ISP that hosts this site (Earthlink) has changed the way their FTP (file transfer protocol) works, making it harder to download these files. Rather than just clicking on the link to download, you must right-click on the link and save the file to disk. The exact wording of the menu option will depend on the browser you are using.
Some people claim that biorhythms have a scientific basis and can help you plot the times when you will be in peak form. Study after study has discredited this and I do not believe in or endorse biorhythms. However, the biorhythm worksheets I have used in this book do provide handy examples without my having to explain enough technical terms to use conventional worksheets as examples.
This section has those files that are not native Lotus or Excel files.
My first draft of Building Better Worksheets contained detailed instructions on writing a Lotus custom dialog box. It also showed an example of using a custom dialog box to enter all the data into the biorhythm worksheet. However, the book ended up being too long and I had to cut out this section. I did not delete the information. Rather, I have made it available here. DIALOG.DLG contains all the code used to create the sample custom dialog box. This custom dialog box is used in DIALOG.WK4 to get information from the user in order to create a biorhythm chart.
LINK.WK4 compares three methods of running a plant over four years. The methods are one-shift, two-shifts, 24-hours per day. It computes the nominal and discounted cash flows along with the internal rate of return. It then produces the components for a report.
The main purpose of LINK.WK4 is to show how you can link results in a Lotus worksheet to your word processor. You can then use the formatting and text manipulation power of your word processor to produce a nicer report than you could using Lotus alone. If you later update the worksheet, Lotus automatically updates the word processing document due to the way you construct the links.
In this example, I have constructed the links to a Microsoft Word for Windows 6.0 document called LINK.DOC. LINK.DOC expects LINK.WK4 to be in the D:\BOOKS\WORKSHEE\WORKSHEE subdirectory. If you plan to experiment with this document, you will need to change this to the subdirectory you use to store LINK.WK4.
You update the link address by selecting Options from the Tools menu in Word for Windows. You then switch to the View tab and check the block beside Field Codes. This will show the linking information in the document rather than the results of the link. Note that D:\BOOKS\WORKSHEE\WORKSHEE is displayed as something like {LINK 123Worksheet "D:\\BOOKS\\WORKSHEE\\WORKSHEE\\LINK.WK4" "C:C28..C:C28" \a \r \* MERGEFORMAT}. Just update the path information to reflect the subdirectory you are using. Be sure to keep the double-back slashes that Word uses. Once you have changed all the paths, use the View tab to turn off Field Codes. You can then update the display by selecting the entire document and pressing F9.
This section discusses the Lotus worksheet files.
My first draft of this book contained detailed instructions on altering or replacing the Lotus menu. It also showed BIORYTHM.WK4 as an example of using a custom menu to enter data into the biorhythm worksheet. This worksheet automatically modifies the Lotus menu by inserting its own menu option into the Lotus menu. This new menu option has four options: jump to the opening screen, jump to the output screen, jump to the input section, and use a dialog box to enter the data. However, the book ended up being too long and I had to cut out this section. However, I did not delete the information. Rather, I have made it available here.
Not only can you download ADD-MENU.WK4, you can read about altering Lotus menus on-line. To do so now, click here. This will take you to another page. This page uses 32K of graphics so it may load slowly on your system. Once you finish reading the material you can return to download other files.
This is a modification to the BIORYTHM.WK4 worksheet discussed below. Rather than automatically showing you the opening screen, it asks if you want to see it and responds accordingly.
This is a modification to the BIORYTHM.WK4 worksheet discussed below. It displays a custom menu that lets four users pick their birthday. This menu also has the option to stop the macro so someone else can enter their birthday and to just to the input or output screen. It mainly illustrates constructing your own menu.
You enter your birthday and it displays a chart showing your biorhythms for the next thirty days.
This worksheet shows a table of numbers with several different types of borders to show how a border can alter the appearance of a table for better or for worse.
This worksheet shows a table of numbers with several line graphs to show how a few strategically placed graphs can help you spot problems with the data in your worksheet.
This is a simple worksheet that computes the breakeven point for an investment. That is, the point at which the revenue from the investment exceeds its cost. The user enters five numbers:
It has three sheets, one for inputs, one for processing, and one for output.
The main use for BREAK-1.WK4 is not to compute breakeven values. Rather, this worksheet is used as a starting point to show how to develop a batch entry model.
This is a modification to the BREAK-1.WK4 worksheet. It is half way between the single investment BREAK-1.WK4 worksheet and the batch entry BREAK-3.WK4 worksheet. It has data stored in a data table on twenty investments. However, it does not yet have a macro to cycle through those twenty investments. Instead, it has an input where you select the investment you wish to view.
The model computes the breakeven year for twenty programs at once.
This worksheet compares the discounted cost of two cars over four years. This is a simplified report used as an illustration.
This is a simple worksheet I created to illustrate how good of a job Lotus does in turning tables of numbers into automatically created charts. It has two sheets. The first sheet contains the table of numbers. The second sheet contains the chart exactly as Lotus automatically created it.
The chart is well linked to the table of numbers. If you change any of the numbers, the chart also changes. It is also well linked to the text. If you change the title of the table or any of the headers, the chart also changes.
This is a modification to the BIORYTHM.WK4 worksheet discussed above. This worksheet uses a custom dialog box to allow the user to enter their birthday using the a dialog box. It also prompts the user for their name and uses that in the chart.
Not only can you download DIALOG.WK4, you can read about custom dialog on-line. To do so now, click here. This will take you to another page. This page uses 100K of graphics so it may load slowly on your system. Once you finish reading the material you can return to download other files.
This worksheet was used to illustrate how to use charts and double-checking totals to help avoid errors in worksheets.
This worksheet only uses two cells. The first cell is where the user enters the number of therms on a gas bill. This second cell calculates the resulting gas bill using a very long and complex formula.
This is a modification of the GAS-1.WK4 worksheet discussed above. It spreads the calculation of the gas bill out over several cells so the resulting formulae are ease to understand.
This is a modification of the GAS-2.WK4 worksheet discussed above. In addition to spreading the calculations of across multiple cells, it treats the parameter for each component of the gas bill as a user input. This allows for much easier modifications.
This is the input section of a large and complex model. This input section has the logic to test inputs and makes corrections to some invalid inputs. It also displays numerous warning and error message. The values in the formulae have been disguised since some of them are proprietary.
This is an earlier version of the BIORYTHM.WK4 worksheet discussed above. It was used to illustrate the left-to-right arrangement of 2-D worksheets. It lacks the error-checking of the BIORYTHM.WK4 worksheet.
This worksheet compares three methods of running a plant over four years. The methods are one-shift, two-shifts, 24-hours per day. It computes the nominal and discounted cash flows along with the internal rate of return. It then produces the components for a report.
However, its main purpose is to show how you can link results in a Lotus worksheet to your word processor. You can then use the formatting and text manipulation power of your word processor to produce a nicer report than you could using Lotus alone. If you later update the worksheet, Lotus automatically updates the word processing document due to the way you construct the links.
In this example, I have constructed the links to a Microsoft Word for Windows 6.0 document called LINK.DOC. LINK.DOC expects LINK.WK4 to be in the D:\BOOKS\WORKSHEE\WORKSHEE subdirectory. If you order the disk and plan to experiment with this document, you will need to change this to the subdirectory you use to store LINK.WK4.
You update the link address by selecting Options from the Tools menu in Word for Windows. You then switch to the View tab and check the block beside Field Codes. This will show the linking information in the document rather than the results of the link. Note that D:\BOOKS\WORKSHEE\WORKSHEE is displayed as something like {LINK 123Worksheet "D:\\BOOKS\\WORKSHEE\\WORKSHEE\\LINK.WK4" "C:C28..C:C28" \a \r \* MERGEFORMAT}. Just update the path information to reflect the subdirectory you are using. Be sure to keep the double-back slashes that Word uses. Once you have changed all the paths, use the View tab to turn off Field Codes. You can then update the display by selecting the entire document and pressing F9.
This displays the LMBCS characters for codes of 129-511.
This worksheet contains a macro that will list the names of all the available macro commands.
It is not uncommon to use an existing worksheet as a template for starting another worksheet. For example, you might have a template worksheet that you use each month to produce your status reports. One problem with this approach is forgetting and overwriting the template worksheet with a worksheet you started from the template.
The NEWNAME.WK4 worksheet solves this problem. Each time you load NEWNAME.WK4, it forces you to save it under a new name. As a result, you can never overwrite this template file. If you are thinking about using this approach, the macro in NEWNAME.WK4 needs two adjustments before it is ready for a production environment:
These modifications are left to interested readers.
My first draft of this book contained detailed instructions on altering or replacing the Lotus menu. It also showed OWN-MENU.WK4 as an example of replacing the Lotus menu with a custom menu. It replaces the Lotus menu with its own menu system. This custom menu system gives you options for working with the worksheet. It also has an option to restore the Lotus menu system.
Not only can you download OWN-MENUS.WK4, you can read about altering Lotus menus on-line. To do so now, click here. This will take you to another page. This page uses 32K of graphics so it may load slowly on your system. Once you finish reading the material you can return to download other files.
This worksheet contains a set of raw data and several different tables where this data is converted to percentages. It is used to illustrate problems associated with converting data to percentages.
This worksheet illustrates how you can format the reports to fill up the entire screen. Once you have done that, you can display the reports sequentially to form a slide show. You can then use this slide show as a presentation to management or clients. SLIDES.WK4 uses the same analysis as LINK.WK4.
One interesting aspect of this worksheet is how it uses macro buttons to scroll the slide show forwards and backwards. This gives the slide show a professional look. It appears as if the slides were designed in a presentation package like Freelance or PowerPoint rather than in Lotus. Anyone who uses Lotus to design a lot of presentations should closely study this worksheet.
This is an earlier version of the BIORYTHM.WK4 worksheet discussed above. It was used to illustrate the top-down arrangement of 2-D worksheets. It lacks the error-checking of the BIORYTHM.WK4 worksheet.
This is a modified version of BREAK-1.WK4. Rather than using batch entry to process multiple sets of data as BREAK-3.WK4 does, VERSION.WK4 uses the Version Manager to process them.
This is a modification of VERSION.WK4 to display more information. In addition to the five data points in each version in VERSION.WK4, this worksheet also includes a title. It has a new report that contains a chart in addition to a more detailed report. It includes the new title in the report and chart.
This section discusses the Excel worksheet files.
You enter your birthday and it displays a chart showing your biorhythms for the next thirty days.
This is a simple worksheet that computes the breakeven point for an investment. That is, the point at which the revenue from the investment exceeds its cost. The user enters five numbers:
It has three sheets, one for inputs, one for processing, and one for output.
The main use for BREAK-1.XLS is not to compute breakeven values. Rather, this worksheet is used as a starting point to show how to develop a batch entry model.
This is a modification to the BREAK-1.XLS worksheet. It is half way between the single investment BREAK-1.XLS worksheet and the batch entry BREAK-3.XLS worksheet. It has data stored in a data table on twenty investments. However, it does not yet have a macro to cycle through those twenty investments. Instead, it has an input where you select the investment you wish to view.
The model computes the breakeven year for twenty programs at once.
This is a simple worksheet I created to illustrate how good of a job Excel does in turning tables of numbers into automatically created charts. It has two sheets. The first sheet contains the table of numbers. The second sheet contains the chart exactly as Excel automatically created it.
The chart is well linked to the table of numbers. If you change any of the numbers, the chart also changes. It is also well linked to the text. If you change the title of the table or any of the headers, the chart also changes.
This is the input section of a large and complex model. This input section has the logic to test inputs and makes corrections to some invalid inputs. It also displays numerous warning and error message. The values in the formulae have been disguised since some of them are proprietary. This worksheet illustrates an approach you can use to checking errors in worksheets.
This worksheet compares three methods of running a plant over four years. The methods are one-shift, two-shifts, 24-hours per day. It computes the nominal and discounted cash flows along with the internal rate of return. It then produces the components for a report.
However, its main purpose is to show how you can link results in a Excel worksheet to your word processor. You can then use the formatting and text manipulation power of your word processor to produce a nicer report than you could using Excel alone. If you later update the worksheet, Excel automatically updates the word processing document due to the way you construct the links.
The example in the book and LINK.DOC uses Lotus and LINK.WK4 rather than Excel and LINK.XLS; however, it would not be very difficult to convert LINK.DOC. In addition, SLIDES.XLS links to LINK.XLS to obtain its data.
MACRO1.XLS is the worksheet containing all the sample Excel macros discussed in the book. The macros in MACRO1.XLS are:
The worksheet calculates mortgage payments after you enter the mortgage amount, interest, and number of monthly payments. It also uses the Scenario Manager to store information on three existing scenarios.
This worksheet illustrates how you can format the reports to fill up the entire screen. Once you have done that, you can display the reports sequentially to form a slide show. You can then use this slide show as a presentation to management or clients. SLIDES.XLS uses the same analysis as LINK.XLS.
One interesting aspect of this worksheet is how it uses macro buttons to scroll the slide show forwards and backwards. This gives the slide show a professional look. It appears as if the slides were designed in a presentation package like Freelance or PowerPoint rather than in Excel. Anyone who uses Excel to design a lot of presentations should closely study this worksheet.
This is a modified version of BREAK-1.XLS. Rather than using batch entry to process multiple sets of data as BREAK-3.XLS does, VERSION.XLS uses the Scenario Manager to process them.
This is a modification of VERSION.XLS to display more information. In addition to the five data points in each version in VERSION.XLS, this worksheet also includes a title. It has a new report that contains a chart in addition to a more detailed report. It includes the new title in the report and chart.
© 2005 by Ronny Richardson, All Rights Reserved