Appendix G. Spreadsheets -- 4 Exercises

The spreadsheet model in Appendix G matches the sales force model described in chapter 6. You may have tested the chapter 6 model using Stella (or one of the other stock-and-flow programs). The following exercises involve similar tests with the spreadsheet model. When you are done with both sets of tests, you will be in a good position to judge the relative merits of dynamic modeling with spread sheet software versus system dynamics software.

1. Build and Verify

Build the spreadsheet model shown in Figure G.1 and verify that you get the dynamic results in Figure G.2.

2. Stability Test

Conduct a stability test similar to the 4th exercise in chapter 6. That is, introduce a disturbance in the 15th year of the simulation to remove an additional 40% of the sales force. Run the model over 30 years and turn in a graph similar to Figure 6.12. Does the test reveal a stable equilibrium?

3. Build a Bigger Company

Use the spreadsheet model to learn if you can build a bigger company by devoting a larger fraction of company revenues to the sales department budget (similar to the 5th exercise in chapter 6). Conduct a sensitivity test with the fraction set at 45%, 50% and 55%. Turn in a time graph showing the company's total revneues from each of the three simulations.

4. Step Size Test

To test the numerical accuracy of the spreadsheet calculations, reduce the DT from 0.25 years to 0.125 years and double the numer of columns to cover the same time interval. Do you get essentially the same results?