Spreadsheets in Finance and Forecasting

1
Spreadsheets in Finance and Forecasting

• Presentation 8
• Buttons, Boxes and Bars

2
Objectives

• After studying this weeks work, you should be
  able to
• Put controls into your worksheets, using option
  boxes, selection boxes and spinners
• Link the parts of your worksheets together so
  that data entry is recorded efficiently.

3
Menu
Introduction

• This presentation is split up into five discrete
  sections. Work through each section in order.
  When you have finished, move on to the follow up
  activity using the link below

Option Buttons
Check Boxes
Lists and Menus
Sliders Spinners
4
Introduction

• This section introduces the main ideas, and the
  spreadsheet with which you will be working

5
How this Presentation works

• The spreadsheet Questionnaire.xls will be used in
  this presentation to demonstrate how the data
  entry items work.
• You should use this both to carry out the task,
  and as a point of reference afterwards.

6
The Questionnaire Spreadsheet

• The page is set up as a questionnaire, but this
  is not the only function of these objects.
• They can make data entry highly flexible, and
  make life much easier for non-IT professionals

7
The QuestionnaireSpreadsheet

• You should now open up the spreadsheet and
  examine it.
• The next few slides will guide you through the
  important features

Action Point
8
Examining the Spreadsheet

• On the spreadsheet we have
• Option Buttons
• Check Boxes
• A List Box
• A Drop-Down menu
• A Scroll Bar
• Spend a couple of minutes looking at each one,
  and seeing what each one does

9
Where does the data go?

• Scroll down the screen.
• Underneath the turquoise data entry form, is a
  small table of the data collected from the
  questionnaire.
• As you check boxes, and select items, note how
  the entries in this box change

10
How does it all work?

• Scroll to the right of the turquoise data entry
  form
• The key to it all is the set of tables
• Here you can see, for most of the questions
• A Selection
• A Data Table

11
How are the tables used?

• A data table can be used in two ways
• It is a store of items which can be utilised by
  some of the objects (e.g. the lists of items in a
  menu is read from the table)
• It is used to interpret the selection made by the
  user

This means that s/he is in the age-range 35-45
In this case the user has selected item number 4
12
How are selections made?

• Each of the objects is linked to a particular
  cell, for example the drop down menu is linked to
  cell J17
• This means that the index number of the item
  chosen will be placed in cell J17

The user has selected 35-45, which is the 4th
item in the list.
13
Exploring the Spreadsheet

• You should now spend a few minutes just examining
  the different parts, and seeing how the elements
  fit together.
• If you View-Zoom-60, you can see the whole
  spreadsheet.
• Put it back to 100 when you have finished.

Return to Menu
14
Option Buttons
15
Adding an Option Button

• Click on View Toolbars and Forms
• The Forms Toolbar should appear.
• Click on the option button item
• A crosshair cursor appears add a new option
  button next to female using drag and drop

16
Making changes

• Now right click on the option button, and select
  Edit Text change the title to Dont Know
• You can align the buttons properly using the draw
  toolbar, but we will leave this for the moment.

17
Creating Links

• Right click on the option button, and select
  Format Control, and this box appears.
• As we have already got some buttons, the links
  have already been created, and you can see that
  the option box will write in cell j4
• If you were creating the first option box, you
  would need to create this link.

18
Adding to the table

• Click on the Dont Know option
• This now puts a 3 in cell J4
• Complete the table by writing in a third line
• 3 - Dont Know

19
Collecting the information

• Now go to the table underneath the questionnaire
• The data entry for gender is not correct, as we
  need to extend the table
• In cell C31 is the formula LOOKUP(J4,K4L5)
• We have added the line
• 3 - Dont Know
• To this table

How do you suppose that this formula needs to be
changed?
20
Collecting the information

• The original formula in cell C31 was
  LOOKUP(J4,K4L5)
• Since we have added an extra line, this needs to
  be changed to LOOKUP(J4,K4L6)

Return to Menu
21
Check Boxes
22
Adding a Check Box

• Click on the check box on the toolbar and add a
  new check box to Question 2
• Change the text to MSc
• Add a new line to the table in lines K7 to L11

23
Linking the Check Box

• Each Check box is linked to its own individual
  cell, which records TRUE if the box is checked,
  and FALSE otherwise.
• We need to link the new box to cell J11

24
Linking the Check Box

• Right click, and select Format Control write J11
  for the cell link

25
Linking the Check Box

• Test that the check box now writes TRUE or FALSE
  in cell J11 according to whether the box is
  checked.

26
Linking the Check Box

• Cell C32 has a very complex formula which
  concatenates all the qualifications data

• IF(J7 TRUE,L7 " / ","") IF(J8TRUE, L8 "
  / , "") IF(J9 TRUE, L9 " / , "")
  IF(J10 TRUE, L10 " / ,"")
• This formula will add an entry onto the line
  only if the cell linked to the option box is
  TRUE, otherwise it adds the null string,
• Append to this line the formula
• IF(J11 TRUE, L11 ,"")

27
Check Box Formula

• The appended formula in Cell 32 reads
• IF(J7 TRUE, L7 " / ","") IF(J8 TRUE, L8
  " / , "") IF(J9 TRUE, L9 " / , "")
  IF(J10 TRUE, L10 " / , "") IF(J11 TRUE,
  L11, "")

28
Linking the Check Box

• Checking the boxes should now put the correct
  information in cell C32

Return to Menu
29
Lists and Menus
30
Challenges

• Since you have already worked through most of the
  ideas, the topics in this section are presented
  as a series of challenges.
• You should read the challenge, then attempt to
  do it before looking at the solution.

31
List Boxes

• The List Box in Question 3 is linked to the list
  of items in cells L12L14 and writes a single
  option choice in cell J12
• The Challenge here is to Add another option
  called Software Engineering, which will extend
  the list to L15

32
List Box Challenge- part 1

• Add a new line to the table
• Link the list box to it

Activity Point
33
List Box Challenge- part 2

• Examine the formula in cell C33
• Modify it so that it links to the full table of
  data

Activity Point
34
Testing the List Box

• When Software Engineering is selected
• it should now appear here

How has the formula in cell 33 been modified to
makes this happen?
35
The Answer

• The formula is changed from
• LOOKUP(J12,K12L15)
• To
• LOOKUP(J12,K12L16)

36
The Combo Box

• A Combo Box is a drop down menu box, and works in
  the same way as the a list box.

• Examine the Combo Box, look at the drop down menu
  and explore how the linkages are created.

37
Combo Box Challenge
Activity Point

• Challenge
• Modify the Combo Box coding so that category 5
  reads 46-59,
• and a 6th category 60 is added.

38
Combo Box Solution
Modify cell L22
Add a new line to cells K23 and L23
Link the Combo box to cells L17L23
Change the formula in cell C34 to
LOOKUP(J17,K17L23)
39
Combo or List Box?

• Whether you use a Combo or a list box is often a
  matter of personal preference.
• However, if you have a large number of items in a
  list, then clearly a list box is better.

Return to Menu
40
Sliders and Spinners
41
The Slider Bar

• Right click on the slider bar and bring up the
  Format Control box.
• The bar is linked directly to cell D22
• The minimum and maximum values can be changed,
  and so can the increment.
• Experiment with these.

42
Adding a Spinner

• The Spinner is a simplified version of the
  slider bar.
• Write Question 6 as below
• Use the forms menu to drag and drop a spinner
  into position,
• Format cell D26 to No Colour, and Currency

43
Adding a Spinner

• Link the Spinner to cell D22
• Change the maximum to 2000, and the increments to
  100
• Add a new line to the Data Collection at the
  bottom of the questionnaire

Return to Menu
44
What to do now

• Formative Activity 9 takes you further into the
  process, by asking you to create buttons boxes
  and format them in a situation where calculations
  are done.
• The Excel spreadsheet Buttons and Boxes
  accompanies this activity

Return to Menu