Chapter 2 - COSC1520.03 Computer Use Fundamentals The Glade...

Info icon This preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
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
Image of page 1

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
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 understand. If the model were large it would be very difficult to understand a formula like
Image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern