1 / 14

Spreadsheet Modeling

Spreadsheet Modeling. Dr Brad Morantz. 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. DASE? DOE?.

afagan
Download Presentation

Spreadsheet Modeling

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

More Related