Chapter 3: Spreadsheet Design
Chapter 3: Spreadsheet Design CSE 2111 - 0070
Popular in Modeling and Problem Solving with Spreadsheets and Databases
BIOH 112 - 01
verified elite notetaker
Popular in Computer Science and Engineering
This 12 page Class Notes was uploaded by Ashley Faulkner on Thursday October 1, 2015. The Class Notes belongs to CSE 2111 - 0070 at Ohio State University taught by Mark Jackson in Summer 2015. Since its upload, it has received 101 views. For similar materials see Modeling and Problem Solving with Spreadsheets and Databases in Computer Science and Engineering at Ohio State University.
Reviews for Chapter 3: Spreadsheet Design
Report this Material
What is Karma?
Karma is the currency of StudySoup.
Date Created: 10/01/15
Chapter 3 Spreadsheet Design DESIGNING amp USING WORKBOOKS WITH MUTIPLE WORKSHEETS Thus far the workbooks we ve used have contained only a single worksheet As the sets of data inputs and calculations become more complex the design of our workbooks will be critical to their ultimate usability and maintenance This section will explore how to design your workbooks to be ef cient and effective and how to execute workbook solutions requiring multiple worksheets DESIGNING EFFICIENTEFFECTIVE SPREADSHEETS This problem only required a simple single Excel worksheet Frequently problems are more complex and require more and varied inputs multiple calculations and even multiple solution cases What other techniques can be used to best solve complex problems using an Excel workbook This section will look at some of the tools that are available to us in modern day spreadsheets including the use of multiple worksheets 3D formulas and a technique that allows us to work backwards to determine what input value is required for a desired output value Before beginning any workbook planning a design is critical to implementing an effective solution Good planning will help avoid time consuming changes later There are three main reasons why a spreadsheet solution may require changes 0 It is difficult to read and use 0 It does not allow additional variables to be easily included 0 It does allow values to be easily changed The next few sections provide guidelines that can prevent a spreadsheet from having any of these three problems LIST DATA INPUTS There are several concepts that should be taken into consideration when designing effective spreadsheets One such concept that has already been discussed previously is the use of formulas that reference input values rather than use constants ie hard coded values Data should never appear more than once in a spreadsheet In previous chapters we have seen how this allows us to easily make changes and A e a 2 updates Fquot 3 have Costs In the previous chapter we created a spreadsheet like Base Prke m with seamm the one shown in Figure 1 to calculate monthly travel mmquot quotm39 quotyous 5m costs based on the service fee shown in cell B1 If the Hquotnuw 22575 5222 Service Fee changes to 10 we only need to change the MMquot 385050 50 value in cell B1 to 10 and all affected values will be Figurel automatically updated Page 1 Chapter 3 Spreadsheet Design The user does not need to know specifically which cells are affected by the changes This type of analysis in which alternative values are substituted for data inputs is known as a whatif analysis A well designed spreadsheet will allow the user to vary one or more values to determine how they will affect the solution VERTICAL VERSUS HORIZONTAL ORIENTATION A B C D E Figure 2 displays a spreadsheet that lists 32 per Wsrekar lh ur swear two different job opportunities with 1 aorzgd varying wages and hours The worksheet 2 Job 1 20 50 900 shows data organized with each category 3 J b 2 2 50 as a separate column and each job as a Figure 2 separate row B C Job 1 Job 2 Figure 3 displays the same spreadsheet 93900 except now the data is organized with each week 20 worked 50 category as a separate row and each Job as a separate column Which configuration is best That answer depends on the data being presented its readability and ease of maintenance In general if you have 4 categories and 200 jobs it would be best to use a configuration similar to the bottom worksheet with the columns listing the categories and the rows the jobs If you have 4 jobs and 200 categories it s best to put the jobs as the column headings as is the case with the top worksheet But this rule of thumb assumes common categories In some cases there are different input categories for each calculation making these formats difficult In such cases making separate tables is a better option Figure 3 What if data is shared between calculations but never varies from one calculation to another This is the case when listing common constants such as inches per foot or ounces per pound Often this type of data is placed in a separate input area where it can be updated in only one location For example if 50 work weeks per year is common to all jobs this value can be placed in only one cell If the number of weeks varies from job to job it should be listed as a separate input for each job Knowing if a value may vary from case to case will help a designer select a robust design that is exible enough to maintain Page 2 Chapter 3 Spreadsheet Design USING MULTIPLE WORKSHEETS INPUTS amp OUTPUTS So far we have looked at examples with inputs and outputs on the same sheet But what if the previous worksheet we created now had to accommodate six different jobs with varying wages hours and overtime opportunities There would be six different hour values six different numbers for hours per week six different numbers for weeks per year etc As we ve seen there is no one right way to organize this information Each layout will offer its own advantages and disadvantages It will partly depend on how the data ultimately needs to be presented and what information is likely to vary and which is not This scenario would best be designed with inputs and outputs on separate worksheets Often when there are many varying inputs several worksheets are used with each set of inputs on a separate worksheet and another worksheet for outputs As seen in Figure 4 two worksheets have been used for this simple example The worksheet Inputs lists the wage and hour information for each job offer Sheet Calc contains the calculations for weekly pay including overtime at time and a half 15 and double time 2 as well as annual wages This configuration is excellent for comparing categories of values as outputs are together and inputs are together It s easy to compare the hour the hours per week or even the total wages per year across the different job offers Even though some absolute cell referencing is required it is also convenient for copying formulas as similar formulas are located in adjacent cells A R l I I F 1 1 Job 81 Job 2 Job 3 Job 84 Job 5 39Job 86 3 H 900 00 750 950 1000 15 Inputs 7001 S 58 S 5 S S 4 Regular Hts per Week 40 35 40 35 15 12 1 Week Worked per Year 50 50 52 50 50 52 539 6y QIJ161WKQ LSL 5 0 10 5 0 0 3 Av OT HrsWk 2x 1 0 0 D 3 0 1 Multigllerrfpr 3151 l5 Multiplier for 2 2 39Job an Job 02 39Job 3 ob 04 100 as dob 06 new we 99 wgek s 30000 s 20000 I s 30000439 5 33200 5 15000 5 13000 Calc quot QIWOWIE I kgiSx 5 6750 g s t s 11150 i s 7175 5 s 5 0T WagesWeek 0 2X FSN i 39 1 1 5 Vote 0949999009 s 550 l s 20000 1 s 412 50 s 40375 5 21000 5 10000 a 1939 ijgg 99g 2221500 1 1400000 i 52135000 i 2018150 1050000 930000 Figure 4 Page 3 Chapter 3 Spreadsheet Design Figure 5 displays the Calc worksheet in formula view Notice there are no hard coded values in the formulas and the design minimizes repetitious typing of formulas A E C D 1 Multiplier far 15x 15 2 Mullliipl elr far 21 E 3 4 Job 1 lab 2 lab 3 5 Regular Wages per Week InputslBllnputslBE Inputsl C23 lnputsl C3 Inputsl 2lnputsl 3 5 HT WageafWeek 15x InputsB2Bl3lnputleS lnputleSP5 l lnput5lC5 lnputle1Bllnputle5 3quot 0T Wageafwveek Ex InputsB2Bl 9 lnputslB lnputsl C2B2lnput5lC lnputle2BllnputleE Total wages per Week 5um135 5umgzcaatj 5UMIID5DI 3 Total Wages per h39ealr ilnput5l 4 C3152 D 52 Figure 5 Remember when writing formulas that reference cells on other worksheets the cell address must be preceded by the sheet name and an exclamation point For example the formula in cell CalcB6 which calculates overtime wages paid at time and a half 15x references both the hour and average overtime hours per week values on the Inputs worksheet To reference these values the formula InputsB2B1InputsB5 precedes these references with the Input sheet reference The reference to the 15 OT multiplier is on the same worksheet as the cell where the formula is written and does not need a sheet name Note that common constants such as 15 and 20 are listed in a separate location from the other variable inputs These could have just as easily been placed on the worksheet Inputs or even on a separate worksheet The layout used here is excellent in cases where additional calculations are later required such as adding hours per week of overtime at 3 times the regular wage rate However it s not as easy to add new jobs Additional jobs will require changes be made to both of the worksheets and formulas copied into new column It is frequently the case that exibility with respect to one concern is sacrificed to make another more exible or easier to read use One more note regarding this input output layout This layout should not be used if the data needs to be presented in a case by case layout where all inputs and outputs from a speci c case need to be presented together ie J ob1 on one sheet job2 on a another sheet etc In such a situation a case by case layout may be preferable Designing such solutions will be discussed in a subsequent section Page 4 Chapter 3 Spreadsheet Design DISPLAYING MULTIPLE WORKSHEETS IN THE EXCEL WINDOW One tip when writing formulas with multiple worksheet references is to display these worksheets simultaneously on your screen This can be accomplished by opening new worksheet windows within the Excel window and sizing each as needed A diagram of a single maximized worksheet in the Excel window can be seen in Figure 6 Figure 7 is a diagram showing multiple sized worksheet windows within the Excel window The steps to get from the display in Figure 6 to the display in Figure 7 are follows nv 0 r a Excel Wlndow 8quot 39 39 39 U Restore Sizing i 39 39 39 quot i a A r I 39 5 button 39 I U I T quot A r 39 L Jun 391 J I h 3 ht U I lilo t39n l u If gm n3 m w s mpg i7 1mm 5 Jami ng 1 mm 5 mm Worksheet llusln39wuIJHM uu may urns 39s liqk nsmn ll H S 395 a Uni Wlndow up p39 wcpk 3 us a if mung Amng un 15 O 1 111m 7 jun RestoreSlang InfilW lewrm s29 00 Muslim 92 04 MA uquot mrn nsfmm sq mu button Figure 6 If the worksheet is maximized within the Excel window click on the Restore Worksheet Window Button in the upper right corner of the worksheet window Once the worksheet window is no longer maximized Excel allows the user to size the worksheet When the cursor hovers over any edge of the worksheet window it will turn into a sizing handle two sided arrow Clicking and holding when the sizing handle is displayed allows the user to drag the cursor up down to adjust the height of the window and left right to adjust the width of the window Size the window to show only the necessary headings and data leaVing ample room in the Excel window for other worksheets to be displayed To open a second window select the View Ribbon Tab and select the New Window button from the Windows group A second window will now be Visible within the Excel window To display the desired worksheet click on the sheet tab of the worksheet you wish to display and then size this window as needed Repeat the previous step until as many windows as needed or as possible to View are displayed In this case the Inputs worksheet window and the Calc worksheet window can be arranged sideby side as seen in Figure 7 Page 5 Chapter 3 Spreadsheet Design When writing formulas that references a cell on one of these other worksheets just click on the worksheet cell needed and it will be automatically inserted into the formula with the appropriate sheet name 9 39 4 Io a 39 I 39 a a 1 a 339 Q W 7 H I u o 4 1 t 0 g d U K 39 I o hul t J to tact 39 u o 7 Litaquot4 uncanny intqdiggtn sg m 39 a lung1 IIl 39 I quotIDA 1 1 I 39I ongnmpct ji 1 i nqu arm 5 a 4 M 39m at v um 1 no J 3n 4 21 1112 7 LJL lr tLin dw ii 2299 quot WWImquot iamWImn 39J I t039a39 39 I Z 39 f39 E 39 suyn 90th v I mu noun in mg m 4 51 L133 my mg 39391139z3923L039 91JL d panelpup Mi 1 S w 5 0 SJ 2 law OI In 5 I In t I l quot 39 39r39 quotn lJuu am 439 H v H quot 39139 r r c a t rg quot10 Ity lnquot n o 0 yr up a nut input In IL G Sizing handles appear in corner when the Figure 7 mouse hovers at the edge of the worksheet window Page 6 Chapter 3 Spreadsheet Design USING MULTIPLE WORKSHEETS CASE BY CASE METHOD An alternate workbook design 39 93 configuration which is better for 39 5 1 o o o o m th dlsplaylng an entlre case wlth all 1 4 3 inputs and outputs for that case can 39 j quotW my be seen in Figure 8 Here each job is quotL 39 M 39 1quot 3919 91w placed on a separate worksheet or j M95 1 r l l39cw39quot 139 9939bo 13 are 39 quot3 g l Sett1ng up layouts where each case has 3 39quotquot law 1 4 991 In 999 No W 1dent1cal 1nput categor1es and calculatlons m 9599 w 2mm quotN m u o o 39 I 911quot aquot Hum lqpmclm yuwm 1 1s extremely easy F1rst create the v 31quot Wk WW pumapa m quu 2 s 5 no 39 339 93939quotquot 39 39 Uz i quot worksheet for Case 1 1n th1s 1nstance 1 mum WWMWM 1 9i Job1 Then wr1te the approprlate 1 f 3 I 331 1 15 l x39 In j 7 formulas as needed Make sure not to 3531 mm Lei 39 C 39 i use sheet names to reference 1tems 3937 7 33333333quot i W contained on this Job 1 sheet Then once 233313 1 i a I lde u v u an the calculatlons are complete and the worksheet formatted appropriated copy the entire worksheet as many times as Figures needed and simply modify the input values One method of copying an entire worksheet is as follows 0 To select the worksheet click on the sheet selector button located just above the row number 1 and to the left of the column label A 0 Use the copy feature to copy the worksheet either click the copy icon in the Clipboard Group of the Home Ribbon or press CtrlC control key and the C key held down simultaneously 0 If necessary insert a new worksheet using the Insert Worksheet button to the right of the sheet tabs at the bottom of the worksheet o In the new worksheet use the paste features to paste the data the paste icon in the Clipboard Group of the Home Ribbon or CtrlV Once the worksheets are copied name each sheet appropriately J ob1 J ob2 etc and substitute the corresponding input values The calculations will automatically be updated on each worksheet as the formulas will reference the values on the corresponding sheet The major disadvantage of this method is if new variables are needed for example OT hours at 3x the hour pay rate each would then need to be added to each worksheet In an upcoming section a method for accomplishing this task will be discussed What if you still need each job to be displayed in separate tables but still wish to compare the total values across cases Instead of designing two sets of worksheets for each configuration inputsoutputs vs case by case one can set up a summary sheet from the case by case worksheets This is especially simple if the values for each element are always located in the same cell In this case the cost elements are the same in each case and reside in the same Page 7 LIP Chapter 3 Spreadsheet Design relative location the total wages is always in cell B19 on each worksheet In instances where each case has different elements each case could be setup as needed but the summarizing of the data would not be as trivial The next section will discuss just how to easily set up this type of summary using 3D formulas USING 3D FORMULAS TO CREATE SUMMARIES When inputs and outputs are placed in separate areas of the workbook it is easy to compare and aggregate data between different cases To similarly compare and aggregate data when organizing each case is on a different sheet in the workbook a summary worksheet can be setup using 3D cell referencing A B C F r i mm 1 Calculations Total Average lgu e 9 S a su ary 0f the SIX JOb OffEIZS 2 Regular wages per week S 160250 S 26708 where data categor1es to be aggregated are 1n 3 OT wagesweek at 15x s 25125 5 4188 column A The total values for each category 4 0T WaQeSWeek anquot 5 7800 5 133900 5 Total Wages per week S 193175 S 32196 are calculated 1n 0011111111 B and the aVerage 6 Total Wages per year 9777250 1629542 values for each category are in column C Figure 9 The simplest method to create such a summary is to first copy the titles from the output section of any individual case to a new worksheet If the category descriptions are listed down a column keep that configuration Similarly if the category descriptions are listed across rows copy them in the same configuration Figure 10 displays the summary categories in a vertical list To total the values for the wages per week category for all six jobs write the following formula in cell B2 Job1315Job2B15Job3B15Job4B15Job5B15Job6Bls Since the order of the categories matches those in each of the six case worksheets this formula can then be copied down the column to calculate the values for OT wages week at 15x OT wages week at 2x etc The cell references will change relatively but the sheet names will remain the same A trick to writing this formula in cases where there are many worksheets is to use a range of cells spanning multiple sheets SUMJob1B15Job6B15 This will only work if the sheets are adjacent to each other in the workbook Using this second method assuming these worksheet tabs are all adjacent to one another write the formula AVERAGEJob1 Job6B15 to obtain the average value of regular wages as follows To simultaneously select multiple adjacent worksheets complete the following steps 0 On the summary worksheet Figure 9 select cell C2 and begin writing the formula as usual AVERAGE 0 At the point where you are ready to enter the range click on the appropriate cell or cells in the 1st worksheet of the range in this case J ob1B15 0 Hold down the shift key and then using the mouse click on the tab of the last worksheet to be included in the range and then press Enter 0 Complete the formula by typing a close parenthesis Page 8 Chapter 3 Spreadsheet Design This formula AVERAGEJob1Job6B15 may also be typed out using the keyboard though errors can be introduced when doing so Later additional Jobs can be added to these totals by simply adding a worksheet somewhere between the worksheet tab range in the formulas But be careful if you add a worksheet somewhere among J ob1 through 6 that should not be included errors will occur What if a more detailed summary is desired as seen in Figure 10 This summary is more like the comparisons setup using the rst layout where data inputs are on one page and outputs are on another With the case by case method workbook layout this type of summary is also easy to set up Calculations Job1 Job2 Job3 Job4 Job5 Job6 Total Average gular wages per week 5 36000 5 28000 5 30000 5 33250 5 15000 5 18000 5 160250 5 26708 OT wagesweek at 15x E 6750 S 5 11250 E 7125 S S 5 25125 E 4188 OT wagesweek at 2x 5 1800 S S S E 6000 S E 7800 E 1300 Total Wages per week 3 44550 5 28000 5 41250 5 40375 5 21000 5 18000 5 193175 5 32196 Total Wages per year s 2227500 5 1400000 5 2145000 5 2018750 5 1050000 5 936000 5 9777250 5 1629542 Figure 10 Cells B1G1 each contain cell references to the values calculated on the individual worksheets Job1B15 in cell B1 Job2B15 in cell C1 etc These formulas are then copied down the column The totals and averages are easily obtained from this summary worksheet Why not just copy and paste the data from each job worksheet as values instead of referencing the cells References ensure that if any data input or calculation changes on an individual case the summary will be automatically updated Changing the Orientation of the Data What if you have listed your data in a vertical format but wish to present your summary in a horizontal format It can be tedious writing each formula out across a row when they cannot be copied down the column to match the data inputs A temporary solution is to create the vertical summary and then copy it use paste special and choose Values Only and the Transpose options This will copy the summary values and simultaneously change the orientation of the presentation It s temporary because you need to copy values and since they re values any changes to the worksheet will render the summary incorrect Page 9 Chapter 3 Spreadsheet Design WhatIf Analyses and Goal Seek You now have the ability to create fairly extensive worksheets using relative and absolute cell referencing and a host of arithmetic functions As previously discussed the greatest benefit of using a spreadsheet tool and cell referencing is the ability to easily incorporate changes and instantly know the effects of those changes This section will look at techniques that lets the user not only know how changes will affect the final values but allow the user to determine what input will be need to reach a speci ed final value WHATIF ANLYALSIS Consider the worksheet in Figure 1 This worksheet includes a list of data inputs cells A3B12 calculation section A14A18 and a final value in cell B19 The worksheet calculated the Total Wages per year from a speci c job based on a list of expected hours worked including hours at regular pay and hours at 15x and 2x regular pay hours week worked hoursweek at 15x hoursweek at 2x 1 2 3 4 5 6 7 8 for15x Each of the formulas in cells B15 through B19 contain cell for references to the data inputs above By constructing the worksheet in this format the user can easily perform WhatIf analyses by simply substituting one or more of the given input values 19 otaI For example if the dollar per hour pay rate were to increase to 950 per hour substituting this new value in cell B5 would be all that is necessary to arrive at a new Total Wages per year All of the intermediate calculations in cells B15B18 would be automatically updated as needed The user may substitute one or more data inputs as desired to perform a variety of WhatIf analyses This is especially important in business when trying to analyze the effects of changes on the viability of a project or the prosperity of a business Figure 1 THE GOAL SEEK TOOL In addition to being able to perform whatif scenarios on spreadsheets many spreadsheet applications provide tools that allow you to work backwards to determine what input value is required for a specific output value In Microsoft Excel this tool is called Goal Seek and can be accessed by clicking the WhatIf button in the Data Tools group on the Data Ribbon Using the worksheet in Figure 1 consider the question To earn 25000 in wages from the same numbertype of hours what base payrate in hour would be required This question can be answered using the Goal Seek tool Before going through the detailed steps of using the Goal Seek tool it is important to know its limitations Page 10 Chapter 3 Spreadsheet Design It is only possible to vary values in cells that do not already contain formulas So cell B16 OT wages per week cannot be varied since it already contains the formula B4B6B11 Only one value can be varied per analysis For example the number of hours and the payrate cannot be varied simultaneously to arrive at a specific outcome for total wages per year The mechanical steps to execute this Goal Seek are as follows ISeal Seek 1 On the Data R1bbon 1n the Data Tools group click on the g CE 31519 WhatIf button and select Goal Seek Th1s w111 d1splay Ta galue ESIIIIIIIII the Goal Seek d1alog box as seen 1n Figure 2 Each box must now be filled in with the proper data El Ehang39ng a Elll I er 1 e I L In the question To earn 25000 in wages from the same l l ante l numbertype of hours what base payrate in hour would be required the total wages is the value being set to Figure4 25000 This calculation resides in cell B19 which currently contains 22275 Cell B4 contains the hour the value being varied to arrive at this 2500 total wage 2 In the Set cell box place the cell reference of the value being set In this case the cell B19 This can be done by clicking directly on the cell or typing the reference into the text box Note that by default this reference is made absolute In the To value box type the new value being set in the set cell box In this case the value 25000 no comma s please In the By changing cell box place the cell reference of the value being varied the hour in cell B4 in this case Again this reference can be selected by clicking on the cell or typing and defaults to an absolute reference Once all of the inputs are speci ed click OK This will bring up the Goal Seek Status dialog box as seen in Figure 3 Excel uses trial and error to find the solution varing the cell to be changed in increments If the value has been found then the Target Value and Current Value of the set cell will be the same If not you can actually modify the starting change value used and the increments other options will be displayed In this example an answer has been found Note that the changed value is not displayed in the dialog box but can be viewed in the worksheet Hint at this point you can t change worksheets so start the goal seek on the worksheet page containing the change value and it will be easy to view at this point Geal Seek Status Gual Seeking with Cell EIIEI Feunel a selutiun EEIIIIIIIII 25 IIIEIIII DD Target value Current value Figure 3 To accept the changes click OK To return to the original values click Cancel Page 11 Chapter 3 Spreadsheet Design Once the changes are accepted the old values are deleted One trick to preserve these old values is to copy the worksheet and run the goal seek on the new worksheet When working with a complex workbook with calculationsvariables on multiple worksheets copy the workbook and perform any whatif analyses andor goal seeks in the copied workbook In this manner the original values will remain intact Excel does provide some more advanced features for these types of analyses Scenario Manager for doing WhatIf analysises and Solver for performing multivariable goalseeks You will complete a lab using Scenario manager but the other tools are not within the scope of this course Page 12