Activity 4: Develop a spreadsheet template

In this activity, you will develop a spreadsheet for a client. First read the scenario, then there are five steps for you to complete in a tutorial (starting on page 3). After you have completed each tutorial step, check the Feedback given at the end of this document. There is also a finished spreadsheet example given for Activity 4 Feedback, on the Practice page.

Note that you will not be able to complete the tutorial unless you know how to develop macros. The activity also assumes that you have created spreadsheets before, using advanced functions.

Scenario

You are introduced to the following scenario in the Reading section of this Learning Pack. The tutorial will guide you in creating the template for the client.

A client of yours runs a small business making wooden picture frames. When customers ask him to make a frame, it takes him a few minutes to work out how much to quote them for the cost, and if he does it in a hurry, he frequently makes the wrong calculation. He’s asked you whether you can make the process easier for him.

Client data

You arrange to have a meeting with him in which you find out the following information:

·  The material he uses comes in three widths: Narrow, medium, and wide.

·  As well, each of these can be made in either a ‘plain’ or ‘ornate’ style.

·  All these options have different costs – see the table below.

·  The wood comes in three colours: black, brown and tan, which all cost the same.

·  There is an option to lacquer the wood, and this costs an extra 10%.

·  Most of the frames have a glass front, but sometimes the customer wants the front left blank – the glass is costed at $15 per square metre.

·  The frame backing is costed at $10 per square metre.

Cost per metre of different styles of wood

Plain / Ornate
Narrow / $ 8.00 / $ 12.00
Medium / $ 10.00 / $ 14.00
Wide / $ 12.00 / $ 16.00

Specifications

Given the calculations involved, it seems clear that you should be able to create a spreadsheet template for your client to use. Here are some of the characteristics of such a template:

·  All the above client data will be organised and stored in the spreadsheet.

·  There will be provision for entering details for a particular customer (what sort of frame they want).

·  The spreadsheet will contain calculations based on the client’s costings and the customer data entered.

·  The spreadsheet will be made user friendly to allow fast, accurate input via drop-down lists, check boxes, and so on.

·  The spreadsheet will be protected from accidental alteration and deletion by the user.

·  A printable quotation needs to be designed and created within the spreadsheet, so that when customer details are entered, the quote can be printed. This feature needs to be quickly and easily accessed.

·  The spreadsheet will be saved as a template for re-use.

Tutorial: Steps in building the template

Step 1: General design

You’ll first need to think carefully about what is required, and how you will design your spreadsheet. After some thought, it should become clear that there will be a few different sections of this spreadsheet:

·  a ‘fixed data’ area where the client’s figures and costings will be stored.

·  an area devoted to taking input from the user – the customer data for a particular frame – this may be best on a front end menu, which could provide access to a button to print the quote

·  an area for storing the user input data

·  an area devoted to the required calculations

·  an area where you can create the layout and formatting for the printed quote.

The calculations aren’t really difficult, but you may find it a challenge to formally create a suitable spreadsheet with a good design. There is no single best way of doing this, and you may need to change your design a few times before you are happy.

However, it is likely that you’ll need to at least create some complex formulas, using ‘If’ and ‘Lookup’ functions as well as absolute references. If you are not sure about these, you’ll need to do some further research. Building user-friendly features into the spreadsheet will also add another level of complexity.

Activity 1a

Have a go at designing the spreadsheet. Sketch on paper the different areas required, and what they will contain.

Step 2: Enter fixed data and formulas

When you begin a complex task like this, you’ll start with the basic calculations. You won’t even consider the ‘User choices’ until you have these calculations worked out.

Activity 1b

Using your own design, or the above figure, begin building the spreadsheet. At this stage, just create the left and middle sections. You’ll have to:

·  Enter the client’s fixed data values into a suitable area of the spreadsheet.

·  Enter some ‘test’ user values.

·  Create the formulas using appropriate functions, absolute values and names.

Step 3: Creating the front end elements

Once you have a working spreadsheet, you then start building the interface – This has been started in the ‘User choices’ section. Notice the prompts, drop downs, and check boxes.

Activity 3c

Create the User Choices section of your spreadsheet, using the Forms Toolbar to create drop down lists and check boxes.

You’ll have to specify the properties of the controls. For example, for the drop downs, you’ll have to provide the Input Range and Cell Link:

·  The Input Range is the list of choices in the drop down list. These choices should already be stored in the fixed data area. For example the drop down list for border thickness uses the Input Range containing the cells ‘Narrow, Medium and Wide’ in the Border Sizes table.

·  The Cell Link is the cell that will contain the data that the user has selected in the drop down. Notice that the data in this case is ‘2’ (the second option) and not ‘Medium’

·  The prompts are simply cell text entries. You’ll do more on your front end shortly.

Step 4: Create the printed quotation

On a different sheet, design a simple quotation. This will include:

·  the framer’s logo, contact details, mission statement, or whatever he requests

·  style elements for the framer’s business

·  the date

·  relevant information that you’d find on a printed quotation

·  links to cells containing information the customer should be provided with.

Activity 1d

Design and create a printed quotation on a different sheet. You can make up the details for the framer, and just use a piece of clip art for the logo.

Compare it with the one in the Feedback.

Step 5: Finalising the front-end menu

Activity 1e

Next, you should:

·  Apply protection to the cells containing fixed data, calculations, labels and so on, and unlock any cells where a user needs to enter data.

·  Remove from display areas that you don’t want a user to access. You can hide rows, columns, sheet tabs, menus, toolbars and so on. Apply any of these features that you have learned about. (A lot of these can be accessed under Tools… Options… View.)

·  Format the front-end section appropriately for the user.

·  Add a command button that will print the quotation. (You’ll first need to create a macro for this.)

·  Remove any customer data you’ve included for testing.

·  Turn on workbook or worksheet protection.

·  Finally, you should make sure that when the file is opened, the front end menu is displayed.

·  Save the file as a template.

·  Test the template by creating some quotes.

Note: Some of these tasks require you to create macros.

Feedback

Feedback 1a

If you are completely stuck, the figure below provides an example of how the different spreadsheet areas could be stored. Remember, there are many ways you could do this.

Here’s an explanation of the above areas.

·  The two boxes at the left of the figure contain the client’s fixed values. The Border sizes table will be used as a lookup table for calculations (vlookup)

·  The section on the right provides the basis for the front end menu. It’s in a user-friendly format with prompts, drop down lists, and check boxes. Some customer (user) values have been entered to help check the calculations.

·  The choices made (by the user) are fed into the section in the middle titled ‘User values’. For example, if ‘medium’ is selected for border thickness, the value ‘2’ appears in the User values area. This has been done by the ‘Cell link’ property in the drop down list.

·  The calculations in the bottom centre draw on both the client’s fixed values and the user values.

Feedback 1b

How did you go with the calculations? Did you get stuck?

The graphic below provides one solution for the underlying calculations for the quotation. If you have trouble with the calculations, try consulting the online Excel Help.

Note:

·  The entries in the User Values table depend on the customer’s selections.

·  Notice the use of cell names in the formulas at the bottom. This should make it easier for you to follow the formulas used.

·  Don’t be confused by the cell references for Frame length and width. They are simply referring to the cells where a user actually types the values in. For now, you could just type in some test values, here.

·  The Vlookup function is looking up values based on the border thickness table.

·  Once you calculate the cost of the border parts, you can then add the extra costs.

·  After building the formulas, make sure to test them using lots of different values.

Feedback 1d

Did you come up with something like the following?

When you’ve finished the design of the quotation, insert cell links to the cells containing the relevant information for the quote. For example, in the cell to the right of Frame Type in the above figure, it might be something like:

=vlookup(thickness,border_sizes_table,2)

(If the border thickness is 2, this will put ‘medium’ in the quotation report for frame type.)

Feedback 1e

When the framer creates a new spreadsheet based on the template, they should be presented with a screen something like the following. This is only very simple, but should fulfil the requirements.

If you know how to create and edit more complex macros, you should be able to make further refinements.

A completed copy of a sample worked file is available for download.

Develop a spreadsheet template XXX

2005