Develop & Use Complex Spreadsheets
WHAT IS A PROCEDURE?
The word procedure may mean different things to different people. In general, it is a guiding or governing principle.
A procedure is a written description of the way things are done for a particular task. It can take the form of a series of interrelated steps that are taken to help implement the task. (Your Spreadsheets Learners Guide is a form of a written procedure, written procedures you use to complete complex tasks)
There are many benefits to having written procedures. It will be of benefit to students/staff, because the document will describe the way in which tasks are done. Managers/organisations also benefit, as they can be sure that the process is carried out in the same way and to the same standard at all times.
A procedure help assure the quality and consistency of the task. If things go wrong and an error is made, the procedure provides a way of finding out whether there is a problem with the system rather than assuming that it was caused by human error.
The simplest way to write a procedure is to describe in detail the journey of a process through the task from the time it is received to the end point. A step-by-step approach!
WHAT SHOULD GO IN A WRITTEN PROCEDURE?
A list of step-by-step instructions for carrying out the task will form part of the procedure but a full, formal procedure should also include the following:
1 Purpose (objectives) - what the procedure is trying to achieve;
2 Scope - what will (or will not) be covered by the procedure;
3 Process - an accurate and sequential description of how the task is carried out;
4 Review - how the procedure will be reviewed;
5 Risks - what risks (if any) are posed or describe circumstances that can make the process more risky than usual.
6 Supporting documents (as appendices).
Refer to Week 1 topic - Automate and Standardise Spreadsheet Operations - Macros: here is a procedure for creating a Macro to setup and print a worksheet, it covers:
1 The Purpose of the macro is described.
2 The Scope of the macro is covered, automate frequent tasks, simplify a complex spreadsheet, etc.
3 The Process of creating the Macro is listed in stepform.
4 It Reviews the macro by asking you to complete subsequent macros as you work through the
Learner's Guide.
5 The Risks posed with creating a macro is that any mistakes you make while creating it will be recorded in the macro itself.
6 The Supporting Document will be the final print of your spreadsheet and the suggested
solutions in the Learner's Guide.
Exercise 40
Design procedures that will create a Macro to turn on formulas, print and save a spreadsheet/worksheet document.
SECURE A WORKBOOK WITH A PASSWORD
To allow only authorised users to view or modify your data, you can help secure your entire workbook file with a password.
1 Click on the File ribbon, click on Info and then click Protect Workbook. Click on Encrypt with Password.
2 A Password box appears, type a password, and then click OK.
3 In the Reenter password box, type the password again, and then click OK.
4 To save the password, save the file.
5 Close the file. Re open to check that it does ask you for apassword.
OR
1 Click on the File Tab, click Save As.
2 Click on Tools button at the bottom left of the Save As dialogue box, click General Options.
3 Do either or both of the following:
If you want users to enter a password before they can view the workbook, type a password in the Password to open box, and then click OK.
If you want users to enter a password before they can save changes to the workbook, type a password in the Password to modify box.
4 Click OK.
5 When prompted, retype your passwords to confirm them.
6 Click Save.
7 If prompted, click Yes to replace the existing workbook.
LOCK ONLY A FEW CELLS ON A WORKSHEET
In a spreadsheet that is used by a number of people it is a good idea to protect cells with formulas so that they cannot be interfered with by other users.
1 Select the cells that you want to be available to all users (for example, select cells without formulas).
2 Click on the Format option (Home Ribbon, Cells group) and click on Format Cells. Click the Protection tab, and then clear the Locked check box. This unlocks the selected cells on the worksheet. OK
3 On the Review tab click Protect Sheet (Changes group).
4 Select the option Select Unlocked Cells and in Password to unprotect sheet, key in a password that you will remember (for the purposes of these exercises use the password - password)
5 Confirm the password and then click OK.
Exercise 41 (If a password is required use - password)
1 Open the file Better Bread Bakey.xls and select just the cells you want to lock - the cells containing formulas for the four weeks.
2 Save the file as Exercise 41.
Exercise 42 (If a password is required use - password)
1 Open the file Better Bread Bakey.xls
2 Lock the entire workbook so that it cannot be viewed without using apassword.
3 Save the file as Exercise 42.
Working with a Multi-Page Workbook
Exercise 43
1 Open the file called Riverina Draft.
2 Copy the worksheet to a new workbook and save the new document as Riverina Training. HINT: Look for the Paste Options button that appears when you use Paste. Click on the arrow next to it and choose Keep Source Column Widths. This will retain the column widths of the original document.
3 Insert suitable formulas to calculate totals, gross profits and operating income for each year (see shaded areas).
eg Total Gross Revenue
Total Cost of Goods Sold
To calculate Gross Profit - subtract Cost of Goods Sold Total from Gross Revenue Total
Total Expenses
To calculate Operating Income - subtract Expenses Total from Gross Profit
4 Format the worksheet appropriately (using display features eg. borders, shading, etc)
5 Separate the Summary and each year onto individual sheets i.e. Sheet 1 will become Summary, Sheet 2 will become 2010, Sheet 3 - 2011, etc. name appropriately, making this a multi-page workbook.
6 On Sheet 1 - Summary, insert formulas which will link to the relevant cells on the appropriate worksheet for each year.
7 Add your name (left), file path (right) and the date (centre) in the footer of each sheet and resave your file.
8 Can you select the cells contained in the Summary Area and Print these cellsonly?
CONSOLIDATION/PRACTISE ASSESSMENT TASKS
Use these tasks to consolidate your knowledge and to prepare you for your final assessment.
Consolidation Task 1
1 Create a separate workbook for each branch of Sherbrook Consolidated listing each sales person and the amount. Place a suitably formatted heading at the top of each spreadsheet to read Sherbrook Consolidated.
2 Format the spreadsheets appropriately using border, shading and appropriate alignment of cells. Save each spreadsheet by the city name.
3 Add to the bottom of each spreadsheet the TOTAL SALES, AVERAGE, MAXIMUMand
MINIMUM generated by the branch.
4 Add a cell under the heading that reads Target, and in the cell next to it $3,000 so the target can be changed. Name the cell $3,000 as Target.
5 In the cell under the word Target enter the word Bonus and in the cell next to it 10%.
6 In column C, calculate the bonus of each salesperson from each branch. The salesperson will receive a 10% bonus if he/she generates more than $3,000. If the amount is less than $3,000 they do not receive a bonus. Ensure the 10% bonus is specified as an absolute cell reference.
7 Add a row under TOTAL SALES to read TOTAL BONUSES and in column C add a formula to calculate the total bonuses.
8 Create a column chart (at the bottom of each spreadsheet) for each branch, which identifies the sales person, the sales and bonus achieved. On the Y-axis, scale the axis so the maximum is 4,800 and the major unit is 200.
9 Create a separate workbook and produce a summary that links the results from each branch.
10 Create a relevant chart to show the bonuses for each branch.
11 In a pie chart explode the branch that has had the mostsales.
12 Management has decided to give each salesperson an increase in their bonus rate to 15%. Alter all branch spreadsheets to reflect the new increase.
13 Update the Summary spreadsheet to show the changes made in the branchspreadsheet.
14 Include the filename as a header and your name and date as a footer.
15 Brisbane management wants to create a template from their Branch spreadsheet. Alter the Brisbane spreadsheet so that the month (eg July) can be added, a new target and sales figures for each person. Save the new spreadsheet as Brisbane Template.
16 Using the Brisbane Template add the following figures for July and save with a new name. Ensure the Target for July is$3,200.
1 Save the spreadsheet as a template called Burbank Quarterly Sales.
2 Create a macroto enter formula to calculate the Total Sales and run the macro.
3 Calculate the Total,Average, Minimum and Maximum Sales for each month.
4 Enter formulae to calculate what 10% discount (absolute reference) would have been on Total Sales.
5 Format the spreadsheet: a Bold labels.
b Centre and enlarge the heading Burbank Toy Emporium.
c Centre and enlarge heading Quarterly Sales Report in a slightly smaller font to the main heading.
d Centre headings within columns. e Insert a blank row at row 3.
6 Add Burbank Quarterly Sales as a header and your name and date as afooter.
7 Sort departments into alphabetical order.
8 Print preview the spreadsheet.
9 Close the spreadsheet template.
PRODUCE TWO SPREADSHEETS THAT ARE LINKED
1 Open the template Burbank Quarterly Sales and add the following data and save thechanges.
2 Add Burbank Quarterly Sales as a header, your name and date as afooter.
3 Create the following spreadsheet that is to be linked to Burbank Quarterly Sales and saveas
Burbank Sales Budget.
1 Sort the departments into alphabetical order.
2 Enter the formulae for Total Budgeted Sales.
3 The Actual Sales column is to be linked with the Total Sales column from the spreadsheet Quarterly Sales.
4 Enter appropriate formula for Over/Under Budget.
5 Add Burbank Sales Budget as a header and your name and date as a footer.
PRODUCE A GRAPH/CHART
Open the spreadsheet file Burbank Quarterly Sales and produce a line graph/chart for the three months: October, November and December, with:
1 Chart Title
2 X Axis Title
3 Y Axis Title
4 Save the file as Burbank Quarterly Sales Chart.
Check the suggested solutions on page 12
Consolidation Task 3
1 Create the following spreadsheet and rename the sheet Invoice.
2 Format the spreadsheet appropriately, using borders, shading and appropriate alignment of cells.
3 Save the workbook as SHADY TREES.
4 Add a column to read Discount % and another column to read Total.
5 Insert date using the Function.
6 On Sheet 2, enter the information from the table below.
7 Name (Define) the cell range Discount, rename Sheet 2as Discount.
8 On the Invoice sheet, calculate the discount of each item by inserting a Lookup formula that returns the percentage discount from the Discount sheet.
9 Insert a formula for the total column.
10 Leaving a blank row, insert the words Invoice Total in Column A and the figure in Column E with total lines.
11 Copy sheet 1 (Invoice) to sheet 3 and turn onformulas.
12 Create a Macro that will add a header to read Shady Advanced Trees and a footer with your name and the date on each sheet.
Consolidation Task 4
1 Create a worksheet using the following information. Save the workbook as Westwood Photography.
2 Format the spreadsheet appropriately, using borders, shading, appropriate alignment of cells.
3 Sort the data into order of States/Territory in ascending order.
4 In column F add a column heading called STATE TOTAL then add each State/Territory's totals.
5 Leave a blank row then in column A add the word TOTALS and add each quarter's totals. Add
the State Total column too.
6 Under the data, create a pie chart to show the total sales for each State/Territory with percentages displayed. Explode the segment for the State with the greatestsales.
7 Add the Task name as a header and your name and the date as a footer.
8 Create a chart as a separate worksheet, named Sales Chart. Specify each quarter as the Legend, States/Territory as X-axis and Sales as the V-axis.
9 Create a pie chart as a separate sheet, called Pie Chart to show the total sales for each quarter.
Attachment:- Charges.rar