• 100 likes • 232 Views
Spreadsheets and Business. Why the popularity of spreadsheets?. “End user Computing” Long delays for IS department to do analysis and reports Ease-to-use, powerful software No need to wait for that overworked professional programmer Now - anyone can write software to do business analyses.
E N D
Why the popularity of spreadsheets? • “End user Computing” • Long delays for IS department to do analysis and reports • Ease-to-use, powerful software • No need to wait for that overworked professional programmer • Now - anyone can write software to do business analyses
There’s the Rub……. • Critical business analyses is being done by amateurs • Think about …. How dependent business is on spreadsheets and How little validitychecking is actually done! James A. Cummings, Inc >>>
Criteria for good spreadsheet design • Accuracy • testing • Data entry controls to limit errors • Clarity • Easily understood by • creator • other users • Flexibility • Easily adapted • Remains valid
Conceptual Model for Worksheet Layout • Input - Process - Output • Clearly separate Input from Output • Clearly specify Assumptions • To be more specific …………………...
Desirable to have separate areas for: • Identification Area • Input Area • Assumptions / Definitions / Formulas • Work Area • Output Area • Macros (may not have them all in every sheet.) AMORT_TABLE.XLS >>
Tips • Break lengthy formulas into parts • Easily hidden later • Use Functions Rather than Formulas • Use Labels, Shading, Lines, Color • anything to enhance readability • But don’t confuse • Form over Function • Aesthetics over Accuracy Cummings (2) >>>
Tips • Clearly specify units of measure • No Numbers In Formulas • All placed in separate cells and referenced • Annotate Often • Document (i.e. Write It Down) • Assumptions • Formulas No_5 template >>>>>
Tips • Get familiar with the Auditing Toolbar • precedents • dependents • Trace Error Auditing Toolbar >>>>>
Testing • Accuracy of Worksheet • test with simple and predictable data • Full range of values • Independent Verification • Another set of eyes • A known test problem • Ad Hoc Testing - Whenever using a spreadsheet • The ‘eyeball’ test