Computer Science & Engineering 2111

1. Computer Science & Engineering 2111 Lecture 1 Spreadsheet Design CSE 2111 Lecture 1-Spreadsheet Design

2. Location, Location, Location Spreadsheet design is the most important factor • GIGO Proper formulas and formatting are also very important Spreadsheet Organization • Inputs and Outputs on the same worksheet • Inputs and Outputs on separate worksheets CSE 2111 Lecture 1-Spreadsheet Design

3. Design Tips • Inputs and outputs should be clearly denoted • Inputs and outputs should be set up in such a way as to facilitate creating formulas that can be copied instead of having to be rewritten over and over • Formulas should primarily use cell references instead of “hard coding” the values (Typing a value instead of a cell reference) CSE 2111 Lecture 1-Spreadsheet Design

4. Inputs and Outputs on Same Worksheet Outputs Inputs CSE 2111 Lecture 1-Spreadsheet Design

5. Notice the Design facilitates writing a formula in cell C4 and copying it over to cell E4 and down to cell E6 CSE 2111 Lecture 1-Spreadsheet Design

6. Inputs and Outputs on Separate Worksheets Inputs Worksheet Name: Budget Assumptions Outputs Worksheet Name: College Budget CSE 2111 Lecture 1-Spreadsheet Design

7. Worksheet Name: College Budget CSE 2111 Lecture 1-Spreadsheet Design

8. Referencing Multiple Worksheets • Special Characters or spaces in spreadsheet name, the whole name must be surrounded by ‘ ‘. • Type in formula, or point and click • Point and click-Make sure to anchor formula • All cell references will have sheet name, but that is ok. CSE 2111 Lecture 1-Spreadsheet Design

9. My Outputs My Inputs OtherInputs My Outputs CSE 2111 Lecture 1-Spreadsheet Design

10. Decision Support Systems (DSS) Programs used to help managers solve complex business problems • A model representing a quantitative business problem • Problem can range from a desired product mix to sales forecasts to sales analysis to risk analysis • Used to examine financial outcomes using “what if” analysis • Examples of DSS programs • SAP™, Microsoft Dynamics™, and PeopleSoft™ • Excel CSE 2111 Lecture 1-Spreadsheet Design

11. College Town Thrift Shop • Owner of a Thrift shop that caters to college students • Thinking of expanding your business by buying an adjacent store front. • You will need to finance the purchase with a business loan • Bank requires a projection of your profit and cash flows for the next two years before it will loan you the money to expand CSE 2111 Lecture 1-Spreadsheet Design

12. College Town Thrift Shop Forecast Factors: • 2013 Sales • Cost of Goods Sold • Estimate of the Economy • Recession and High Inflation Outlook • Recession and Low Inflation Outlook • Economic Boom and High Inflation Outlook • Economic Boom and Low Inflation Outlook • Business loan payment CSE 2111 Lecture 1-Spreadsheet Design

13. Organization of a DSS Model using an Income and Cash Flow Statement Framework Constants Section • Holds values that are needed for the spreadsheet calculations InputsSection • Will be typed in by the user to play basic “what if” analysis Summary of Key Results Section • Summarizes important information Calculations Section • Used for intermediate calculations Income and Cash Flow Statements Section • Financial or accounting “body” of the spreadsheet CSE 2111 Lecture 1-Spreadsheet Design

14. Steps • Create a skeleton and add any constants • Fill in the easy formulas • Fill in the hard formulas CSE 2111 Lecture 1-Spreadsheet Design

15. Skeleton of DSS using an Income and Cash Flow Statements framework CSE 2111 Lecture 1-Spreadsheet Design

16. Constants Section Values given to you and usually do not change, but can if needed. For example, you might decide to borrow more than $100,000. CSE 2111 Lecture 1-Spreadsheet Design

17. Inputs Section Allows you to play basic “what if” analysis on the factors you have identified earlier CSE 2111 Lecture 1-Spreadsheet Design

18. Summary of Key Results Section Summary sections are usually placed near the top of a spreadsheet to allow managers to see a quick “bottom line summary (Copied from the Income and Cash Flow Statements section at the bottom of the sheet.) Summary sections can make it easier to select cells for charting CSE 2111 Lecture 1-Spreadsheet Design

19. Calculations Section (Expansion and No Expansion) Used to compute intermediate values which makes the calculations in the Income and Cash Flow Statements less complicated. CSE 2111 Lecture 1-Spreadsheet Design

20. Completed Spreadsheet(Using R/H as input) CSE 2111 Lecture 1-Spreadsheet Design

21. Evaluating the outcomes for your DSS Model • Recession and High Inflation Outlook • Recession and Low Inflation Outlook • Economic Boom and High Inflation Outlook • Economic Boom and Low Inflation Outlook You could create 3 more copies of your spreadsheet to evaluate the four different scenarios, or you could use Scenario Manager. CSE 2111 Lecture 1-Spreadsheet Design

22. Scenario Manager Used to run the model for all the requested outcomes and presents a tabular summary of the results. Presents a tabular summary of the results that is useful for reports, presentations, etc. CSE 2111 Lecture 1-Spreadsheet Design