CREATING A SPREADSHEET MODEL

Now that we are competent in formula entering and formatting, we no longer need images of already created models. We can, instead, create our own models from scratch. We should note the following issues:

1)Since now it is up to us to design the models, we should consider designing them in a meaningful and easy-to-use manner. For example, variables should be entered where they belong, not scattered around. All relevant data and calculations should be displayed, while those which may not mean anything to the user (but are necessary for the model) should be hidden.

2)You will enter the variables. You do not need to enter all variables. Actually, it is a better idea to leave some entries empty, because in this way you can see if the model behaves the way it should when some entries are not made. Since you are given examples in the question, you might want to enter values of examples and see if results are the same as in the example.

3)In our formulas, we should never use constants unless they are real constants. Instead, we should use variables, because they are both easier to change (vary) and are easier to see.

4)Having a model that works formula-wise is necessary but not sufficient. We should make use of formatting tools that are available, to make our model look good and easier to understand and use. A checklist of such tools would include:

a)Naming worksheets with appropriate and meaningful names

b)Providing titles for our tables and making them noticeable.

c)Gridlines are useful during creation of models, but when our work is complete, we no longer need them. Removing them, and at the same time bordering tables might be a good idea.

d)Number alignment in spreadsheet programs by default is right. Do not change it unless you have a good reason. Default alignment for alphanumeric values is left. Sometimes we may want to change this to make, for example, titles look better. We can use center across selection for titles that span more than one column. If the title of a column, where values are short, is long, we may want to wrap the title so that the column is not unnecessarily wide.

e)Number formatting: All numbers in a column should have the same number of digits to look good. Large numbers (with 4 or more digits) should have 1000 separator so that they can be read more easily. Percentages, obviously, should have percentage format.

------

Please note that there are two different settings for "," (comma) and "." (period) on the PCs of our lab:

  1. On some machines, decimal symbol (the character that separates the whole number part of a number from its fractional part) is a period (dot), and the thousands separator is a comma.
  2. On other machines, the above settings are exactly the opposite.

As indicated in spreadsheet_tips.doc, default (automatic) alignment of numeric values is right. Therefore, unless you have left-aligned numbers, you should not have the following

incorrect
1.1234

but instead like the following.

correct
1.1234

These settings affect function parameter/argument notation, too. For example, Excel help states syntax of IF function as

IF(logical_test,value_if_true,value_if_false)

where list separator is “,” (comma). On PCs where decimal point is a comma, list separator is “;” (semi-colon).