1. Spreadsheet Modeling Dr Brad Morantz

2. Background • Almost everyone has a spreadsheet at their desk • Few have mod/sim packages at their desk • Arena costs $18K • OpNet is $45K • Sometimes need to do Proof of Concept • Need to insure testing in corners of envelope of variable space

3. DASE? DOE? • DASE = Design & Analysis of Simulation Experiments • DOE = Design of Experiment • An experiment must be properly designed to produce realistic and quality results • An experiment must try out all ranges of potential values • Search the corners of the envelope • Brad’s Theorem: Every system has a point in variable space where the system goes ‘postal’

4. Advantages • Availability of software • Clear box • Can see what is going on inside • Can make changes easily • Can make changes and see what happens • Many plug-ins to enhance performance and presentation

5. DASE Advantages • Design • Easy to change input variables • Easy to monitor/see input values • Scenario Manager • Analysis • Easy to create links to analysis • Many analysis tools in spreadsheet • Graph/Chart in spread sheet

6. DASE Considerations • Input values must reach the “corners of the envelope” • Volume filling in variable space • Easy to implement test matrix • Values must be realistic • Can not have impossible combinations

7. First Exposure • I saw this first time teaching in the B school at GSU • Can quickly build spreadsheet model to make business decisions • I built a spreadsheet model to track my mortgage • Found 3 mistakes • Saved $1000’s

8. Business Model • Want to hold Rock Concert to make $ • Name band costs • $10K to appear • $1 a head over 5000 people • Local band is $1K • Attract more people • Concessions • 25% profit • Sell more on hot days • Etc, Etc, but can we make a profit? • DASE considerations!

9. Science Problem • Have a MP Computer • MIMD capable • Have lots of data arriving daily • How to optimize throughput • Built spreadsheet model • Allowed me to wipe out sections • Simulated down time • Results proof of concept • Bought me a Arena • Over doubled throughput • DASE considerations

10. Projectile • Input angle and muzzle velocity • Need to know how far it will go • Only realistic ranges for both variables • Can see all values as it travels • Easy to plot trajectory • DASE Considerations • Sample program

11. Guided Weapon • Have weapon • Given specifications • Calculate SSPD • Then have guided weapon • Calculate SSPD • Multiple guided weapon w/ unitary target • Calculate SSPDn • Sample program

12. Acronyms • SSPD Single Shot Probability of Damage • CEP circular error probable • REP range error probable • DEP deflection error probable • SR slant range • There are plenty more

13. References • Weaponeering, Conventional Weapon System Effectiveness, Morris R Driels • Numerous books on Spreadsheet modeling (all are on business applications) contact me if need info

14. Big Thanks • To Will Haas who loaned me the Weaponeering book and who taught me much about this. He was very patient and I appreciate it.