This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: COSC1520.03 The Glade Computer Use: Fundamentals Laboratory Handbook Chapter 2: Using Multiple Worksheets in a Model Objectives This chapter is intended to expand on the features of Excel introduced in the demonstration models of Chapter 1. As the title implies, a general objective of the chapter is for you to understand why multiple worksheets are used in a model and how these can be related to each other by the use of formulas which use data from different worksheets. In addition some principles of how to design a model are introduced. Here is a list of what the lab covers: • naming data ranges • creating intersheet formulas • using more of the functions available in Excel • creating more charts and graphs Laboratory Preparation Read the whole of this chapter carefully. Introduction This chapter consists of a number of different exercises that demonstrate the use of multiple worksheets in a spreadsheet model. The first few exercises lead you through most of the details of building the models – but as you progress fewer and fewer instructions are provided. It is expected that you will not simply follow the instructions by rote, but rather think carefully about what you are doing and why you are doing it so that you can apply the same principles and techniques yourself. The exercises continue to develop the use of cell range and formula naming as a means of clarifying the data on a spreadsheet; the use of more of the functions that are provided by Excel; and the use of charts to present the data. The main objective, however, is to understand how and why intersheet formulas are used. Exercise 1- A Sales Taxes Model Open the file Exercise1 in Support Files (Chapter 2). The model consists of three worksheets – examine the Comments worksheet and the other two now. The Sales_Summary worksheet contains some “raw” data and some headings where data is to be calculated. The Tax_Rates worksheet contains just the two values for GST and PST rates – values that rarely change. Your task in this exercise is to complete the model – i.e. to create formulas so that the Sales_Summary worksheet looks like Figure 2.1. 2-1 The Glade COSC1520.03 Computer Use: Fundamentals Laboratory Handbook Figure 2.1 – the completed Sales_Summary worksheet Naming Data Ranges The Cost column is just the price each multiplied by the quantity for each item listed. One simple way to create the formula for the cost is to follow the steps shown next. Observe the formula bar as you do this in order to see the formula as it is built. • Select the first cell in the Cost column • Type an = symbol to begin specifying a formula • Click in the first cell in the Price Each column • Type an * (the multiplication operator) • Click on the first cell in the Quantity column • Press the Enter key You could then fill this formula down the Cost column. However, because it uses just the default cell naming conventions of Excel ( =B2*C2 ) the formula is really quite difficult to...
View Full Document
- Fall '08