Excel lab 2 sales summary worksheet

Revised Spring 2015 Excel 2013as

MT. SAN ANTONIO COLLEGE

CISB11

Excel Spreadsheet Assignment - Part 3

PURPOSE:

This assignment will continue to build your knowledge of spreadsheet operations by introducing you to

some of Excel’s more sophisticated features.

The objectives of this assignment are to:

 Group worksheets in order to format multiple worksheets at the same time

 Copy a worksheet

 Use 3-D references to display the contents of cells on multiple worksheets

 Use 3-D references to consolidate (summarize) data from multiple worksheets

 Perform What-If Analysis using Goal Seek

 Use Help to learn about features available in Excel

File you will need:

a. Excel P3 Fun Zone Amusements.xlsx from Canvas Assignment link

Files you will submit:

1) Quality and completeness of your completed assignment

b. YourLastName Fun Zone Amusements.xlsx -  Excel workbook (with Goal Seek values)

c. YourLastName Summary Formulas  PDF (step 15)

d. YourLastName EX3 Answers  Word document (step 16)

with the answers to the ten questions on the 2nd to last page of this document

2) Spelling and grammar (potential deductions)

3) Following directions (potential deductions)

ASSIGNMENT:

1. If you are not doing this assignment at Mt. SAC, make sure you are using Excel 2016. Although you

can use previous versions of Excel to complete this assignment, some of the assignment

instructions will not match your older versions—which may make it difficult to complete the

assignment.

 You cannot use versions of Excel before 2007 to do this assignment.

 If you are doing this assignment in the lab, remember that you need your student ID to be

assigned a computer.

2. If you have not downloaded the Fun Zone Amusements file from Canvas, return to the assignment

in Canvas and click the link to download the file. Refer to previous assignments for the instructions

to download a file from Canvas

3. To access Microsoft Excel (Windows 10):

 Open Excel 2016

 Click the Start button on the bottom left corner of the Taskbar.

 Click All Programs on the Start menu, and then click the Microsoft Office 2016 folder.

Next, click Excel 2016.

Page 2 of 12l

1. Let’s open the Excel P3 Fun Zone Amusements.xlsx file.

 Open the Excel P3 Fun Zone Amusements.xlsx workbook you downloaded earlier

 Click the File tab. Then select Open and then within the list of choices (under the large

Open caption) select Computer. (see figure below)

(OR double-click the Computer selection to open the Open dialog box and skip the next

step.)

 Then click the Browse button. The Open dialog box will open.

 Use the Navigation pane displayed at the left side of the Open dialog box to navigate to the

location where the saved Excel P3 Fun Zone Amusements.xlsx file. It will most likely be on

your flash drive.

For example, the illustration below shows the pathway as well as the hierarchy of the file

structure on the computer used for illustration purposes. The file path is shown at the top

of the window.

Computer ►USB DISK (E:)►Work in Progress►CISB11 Files►CISB11 Beginning File Excel

3►Fun Zone Amusements

 Find the

file Fun Zone

Amusements in the main window of the Open dialog box

Page 3 of 12l

 Select the file

 Move the mouse pointer to the Open button on the bottom right side of the window and

click it with the mouse. Excel P3 Fun Zone Amusements.xlsx spreadsheet should now be

displayed

2. Use the Save-As command to rename the file and save it to your flash drive.

 Save the file with the name: YourLastName Fun Zone Amusements-

replace the words YourLastName with your own last name

The file name for this example would end up looking like this: Sokol FunZoneAmusements

 Click the File tab in the upper-left corner of the window (see figure above)

 Click on Save AS

 Click on the folder you wish to save the renamed folder. It should be visible under Recent

Folders section.

 Click the folder to open the Save As dialog box.

(OR double-click the Computer selection to open the Save As dialog box.)

Check to make the location where your file will be saved. Look at the file pathway. You

probably want to save it to your flash drive. If it is not saving where you want it to save,

navigate to the correct storage destination.

 Once the Save As dialog box opens, change the text in the File name: box to:

YourLastName Fun Zone Amusements-

 Click the Save button.

Page 4 of 12l

3. Explore the YourLastName Fun Zone Amusements workbook.

 Fun Zone Amusements is a company that provides amusement rides for traveling fairs and

carnivals. Last weekend, there were carnivals in the cities of Chino, Fullerton, and Rosemead.

Management collected the information from these carnivals and they would like you to assist

them in developing the data so that it provides more robust information for managerial

decision making.

 Click on each of the tabs at the bottom: Chino, Fullerton, and Rosemead.

 Each tab is a worksheet also called a sheet. An Excel file can contain multiple worksheets. A

collection of worksheets is called a workbook. Click on each of the worksheets to see the

data contained in the sheet.

 Notice that each of the worksheets contains the total number of tickets sold and the ticket

sales.

 Sheet and worksheet will be used interchangeably throughout this document

 Click in cell D8 in any worksheet. With D8 as the active cell, look in the formula bar (directly

under the Ribbon) to see the contents of D8. It contains the formula: =$C$5*C8

 This formula multiplies the value in cell C5 by the value in cell C8. The $ signs in front of C

and $ in front of the 5 indicates that this is an absolute cell reference. (You may recall that

you learned about absolute cell references in Excel Part 2.) When the formula was copied

from cell D8 down to D14, Excel needed to ALWAYS use the value in cell C5 in the formula.

Reminder: you only need to be concerned about using absolute cell references if you are

going to copy a formula or function and you want to ALWAYS use the same cell reference in

the formula.

 Click in cell C15 or D15.

 Notice that the SUM function was used to total the values in the column above. The other

worksheets are set up using the same information in the same columns and rows.

4. Group multiple worksheets. (also, see page 11)

 The workbook you received is very simple. It has the data for each city, but management would

like for you to make it look more professional. You will begin by adding the company name to

each of the Sales worksheets. You can save a lot of time by grouping the worksheets.

 Group all of the worksheets in the workbook

 Group all of the worksheets in your workbook by pressing and holding the Ctrl key while

you click on each worksheet tab. Each tab should be underlined with a same colored

line, indicating that it is part of the group. (Another method for grouping worksheets

that are next to each other is to click on the first tab and then press and hold the Shift

key while clicking on the last tab that you want in the group. A method for grouping all

worksheets in a workbook is to right-click any sheet and then click Select All Sheets.)

 The Excel title bar indicates when worksheets have been grouped by displaying the

word [Group].

Page 5 of 12l

5. Format multiple worksheets at the same time and ungroup the worksheets.

 When worksheets are grouped, you can perform calculations or make formatting changes that

will affect all of the grouped worksheets.

 With your worksheets still grouped, click in cell A1 and type, Fun Zone Amusements. Press

Enter.

 Ungroup the worksheets by right-clicking on any worksheet tab in the group and selecting

Ungroup Sheets.

 Another option for ungrouping worksheets is to click any worksheet in the workbook that is

not a part of the group.

 Look at each of your worksheets and verify that cell A1 contains the text Fun Zone

Amusements.

6. Group the worksheets and add more formatting.

 Group all of the worksheets (refer to step 4 if you need help) and click in cell A1. Change the

font size to 18.

 Select the range B8 through B14, the names of the rides (click and drag from cell B8 through

B14).

 Use the B (bold) and I (italics) buttons in the Font group on the Home tab to make B8:B14 bold

and italicized.

 Ungroup the worksheets.

7. Copy a worksheet and use it to consolidate (summarize) the values from the city worksheets.

 The summary worksheet that you are about to create will

look identical to the other worksheets. The only difference is

that you will create formulas that will consolidate

(summarize) the data from the other worksheets into one

page.

 Copy the Chino worksheet tab (sheet).

Name the new worksheet Summary.

The new worksheet must be the 1st worksheet in the

workbook.

 Right-click on the Chino worksheet tab and select the option, Move or copy….

 Click the Create a copy option. Because Chino is selected, the copy will appear before the

Chino worksheet. Review the dialog box shown to the right. Press the OK button.

 The worksheet that you just created appears as the first worksheet in your workbook and

the tab displays, Chino (2).

 NOTE: If your new worksheet does not appear first in your workbook, you can easily move a

worksheet by using a drag and drop technique. As you drag a worksheet tab, a small black

arrow appears above the tabs and indicates where the worksheet will be relocated.

Page 6 of 12l

 Change the tab’s name to Summary.

Right-click on the Chino (2) tab and select Rename.

Once

Chino (2) is highlighted, type the new name:

Summary.

Another method for renaming a worksheet tab is

to double-click the tab and type a new name.

8. Edit the contents of the Summary sheet

 Change the word, Chino, in cell B3 to Summary.

 Clear the contents of cells C8:D14 on the Summary

sheet.

 Select C8:D14. Right-click in the selected range and

select Clear Contents in the short-cut menu. Note: If you click the left mouse button, the

range will no longer be selected and you must repeat this step. Your summary worksheet

should match the example shown to the right.

9. Use a 3-D reference to display a value from one worksheet on other worksheets.

 The person who originally entered the data into the Fun Zone Amusements workbook thought

that it was a good idea to put the ticket price on each workbook. You can find it in cell C5 of

each city worksheet. Management now feels that this is very inefficient. If the ticket price

changes, it should be changed in one cell and all of the other cells that use it should recalculate

automatically. They would like you to display the ticket price on the summary sheet so that

when the ticket price changes, only the summary sheet will need to be changed. The following

instructions will guide you in the use of 3-D references.

 FOLLOW THESE INSTRUCTIONS TO USE 3-D REFERENCES TO DISPLAY THE TICKET PRICE ON THE

CITY WORKSHEETS. DO NOT TYPE THE TICKET PRICE IN EACH WORKSHEET.

 To save time, group the city worksheets Chino, Fullerton, and Rosemead.

MAKE SURE THE SUMMARY SHEET IS NOT PART OF THE GROUP! (Review step 4)

 Delete the ticket price displayed in cell C5 in all three city worksheets.

 Click in cell C5, in any of the city worksheets. Press the Delete key.

The ticket prices disappear from the Chino, Rosemead, and Fullerton Worksheets and the

Ticket Sales values become zero.

 Next you will use a 3-D reference to display (reference) the ticket price in cell C5 of the

Summary worksheet in each city worksheet. The city worksheets won’t contain the actual

number - they will only refer to cell C5 on the Summary sheet. That way if the ticket price

changes on the Summary sheet the amount displayed in C5 in all of the city worksheets will

update automatically.

 If C5 is no longer selected, select cell C5 in a city worksheet (the city sheets must be grouped).

Type an equal sign (=) to start the formula. Select the Summary tab and then click in cell C5. A

green marquee starts moving around the edge of cell C5 on the Summary worksheet. Press

Enter.

Page 7 of 12l

 Ungroup the worksheets

 Right-click any city worksheet tab and select Ungroup Sheets or click on the Summary tab

 Click in C5 in one of the city worksheets. The ticket price is displayed in the cell and the formula

bar contains =Summary!C5.

 Examine the other city worksheets. You will notice that C5 in each city worksheet is referencing

the value of C5 in the Summary sheet.

10. Use 3-D references to calculate the total sales for all of the cities.

 Add the values from cell C8 in the Chino, Fullerton, and Rosemead worksheets and display the

total in this cell. We will use the Sum function to add these values together.

 The following instructions guide you in selecting all of the city worksheets and choosing the cell

that contains each city’s # Tickets Sold for the Aerial Tram.

 Click in cell C8 in the Summary worksheet. Begin a SUM function as you normally would by

typing =SUM(

o NOTE: You need to type the left parenthesis after the word SUM.

 Click on the Chino tab, hold the Shift key and click on the Rosemead tab. Click in cell C8 and

press Enter.

 You are now back in the Summary worksheet and cell C8 is the active cell. The formula bar

should display: =SUM(Chino:Rosemead!C8)

o NOTE: Do not be concerned if your formula displays =SUM(‘Chino:Rosemead’!C8). This

formula adds the values in cell C8 from the Chino worksheet to the Fullerton and to the

Rosemead worksheets.

 Use AutoFill to copy the formula in cell C8 down to cell C14.

 (You may recall that you learned about AutoFill in Excel Part 1).

To use AutoFill, make sure that C8 is the active cell. Point the cursor at the small square

(called the AutoFill handle) in the lower right-hand corner of the cell. You will know when

the cursor is positioned correctly because the cursor indicator will turn into a black thin-

lined plus sign. Hold the mouse button down and move the mouse straight down to cell

C14. Release the button. The Total for the # Tickets Sold cell, C15, will display 13,903.

 Using the skills that you just completed, repeat the steps above and calculate the total sales in

cell D8 on the Summary worksheet. ** Use 3D references to calculate Total Sales.**

 Copy the formula down to D14. The Total for the Total Sales cell, D15, will display $48,660.50.

 Select the range C14 and D14 and then fix the bottom border.

 In the Font group on the Home tab, click the drop down

arrow in the Borders button and select Bottom Border.

11. Perform What-If Analysis

 Management has agreed to contribute 10% of ticket sales to

charity when total ticket sales reach $50,000. With the current ticket price at $3.50, last week’s

ticket sales totaled $48,660.50.

 What did the ticket price need to be in order to reach $50,000 for last week’s ticket sales?

 It would be easy to change the ticket price in C5 by typing in a new number and then

watching the amount in D15 to see how it gets closer to the goal.

Page 8 of 12l

 On the Summary sheet, change the ticket price in C5 to $4.00. Notice that D15 is recalculated

automatically and the total sales now show as $55,612.00. That amount is too high.

 Try entering $3.75 in cell C5. The result is $52,136.25.

 That’s still too high. You could keep using trial and error to find the ticket price that achieves

the goal of $50,000, but there is a better way. It is a feature in Excel called Goal Seek.

12. Perform What-If Analysis using Goal Seek (see page 11)

 We will use Excel’s Goal Seek feature to find the exact ticket price needed in order to have total

sales of $50,000.

 Cell C5 in the Summary sheet is considered an input cell. Input cells contain values rather than

formulas.

 D15 is a dependent cell. Dependent cells typically contain formulas that use the values in input

cells in their calculations. Dependent cells are the basis of what-if analysis. In other words,

what will be the result of the calculation be if the input cell value (or values) changes?

 Click in cell D15 on the Summary worksheet.

 Select the Data tab and in the Data Tools group, click on What-If Analysis and click Goal Seek….

 The Goal Seek dialog box opens and D15 appears in the Set cell textbox

(because D15 was the active cell when you clicked the

What-If Analysis button on the Ribbon).

 Set cell identifies the cell that is used for setting the goal.

The cell selected for Set cell must be a dependent cell and

must contain a formula or function. The =SUM(D8:D14)

formula for total sales is in cell D15.

 Type: $50,000 in the

 To value box. To value is the amount of our goal, which is $50,000.

 Type: C5 in the By changing cell textbox.

 By changing cell is the cell that will change to give us the goal amount we are looking to

achieve. This is the ticket price in C5. If C5 displays as $C$5, that is OK.

 The Goal Seek dialog box reads like this: Set cell D15 to a value of $50,000 by changing cell C5.

See figure above.

 Click the OK button and the Goal Seek Status window appears.

It lets you know that it found a solution (or not!).

 Click OK. Look in cell C5, the ticket price has been changed to

$3.60.

 This is the ticket price for $50,000 in total sales.

13. Include your name in all of the worksheets

 Group ALL of the worksheets.

 In cell C3 type Prepared by: YourName (using your name instead of the words, YourName).

 After entering your name Ungroup the worksheets.

 Save the workbook.

Page 9 of 12l

14. Review Print options

 Select the File tab and then select Print. Click on the down arrow within the Settings area of the

Print dialog box.

 Excel provides various printing options:

o Print Active Sheets - prints the worksheet that is currently displayed

o Print Entire Workbook - prints all of the worksheets within the workbook

o Print Selection - prints only the part of the worksheet that has been highlighted.

15. Create a PDF with formulas displayed

 Create a PDF of the Summary worksheet with the formulas displayed using “Print”:

 Click on the Summary tab and display the formulas for this worksheet.

 To display formulas, simultaneously press the “Ctrl” and “`” buttons on the keyboard OR go

to the Formulas tab on the Ribbon and choose Show Formulas from the Formula Auditing

group.

The ` key should be at the upper left corner of your keyboard to the left of the 1 key. The

same key includes ~.

 In the Summary worksheet, adjust the column widths so that ALL of the formulas display

completely in column B, C, and D. (Pay special attention to column D and make sure it is wide

enough for the formulas to display!)

 Do not be concerned if A1 is not fully displayed.

 Select the File tab and then select Print. A preview of what will be printed is shown on the right

side of the page.

 Notice that the total sales column is on page 2. You will fix this by changing the orientation

to landscape.

 Change the page orientation by selecting the down arrow to the right of Portrait Orientation

and selecting Landscape Orientation.

 The result must fit on 1 landscape page!

If it NOT 1 page, click the drop down arrow to the left of No Scaling and select Fit Sheet on One

Page

 Click Export in the green navigation pane (left side)

 Click the Create PDF/XPS Document button (shaded green under the large font word Export)

 Click the Create PDF/XPS button (in the white area)

 Save the PDF file with the following name: YourLastName Summary Formulas

 Redisplay the values in the worksheet. You can use “Ctrl” and “`”. You will need to adjust the

column widths again so that the data (B7:D15) fits into each column. Do NOT make column C

too wide. The text “Prepared by: YourName”in cells C3 and D2 may spill over to column E3.

 SAVE your work but leave the Excel file open so that you can use it when needed to answer the

questions on the next page.

 IF you change values after this step, do NOT save!

Continued on the next page

Page 10 of 12l

16. Answer the ten (10) questions

 Open a Word document and name it YourLastName EX3 Answers

 Leave one blank line between each answer.

 Please do not retype any part of the question. Simply type the question number and the

correct answer followed by a blank line.

1. What key on the keyboard is used to group multiple worksheets

one at a time (not next to each other)?

2. True or False When you group worksheets, you can change the formatting for multiple sheets all at the same time.

3. True or False The formula =C5*C8 produces the same results as =$C$5*C8 when the formula is copied to other cells.

4. True or False An input cell contains a value.

5. What is the name of the number format that displays dollars signs on a value when you press the $ button from the Number group on the Home tab of the Ribbon?

6. Type the formula that is in cell C5 in the Rosemead worksheet.

7. What are the total sales in the Summary sheet if the ticket price is $3.00?

8. What are the total sales in the Summary sheet if the ticket price is $5.00?

9. What would the ticket price need to be to produce total sales of $60,000.00?

10. What would the ticket price need to be to produce total sales of $25,000.00?

 If you change the value of the ticket price as you answer the questions,

REMEMBER DO NOT SAVE!

The file you submit to the instructor should have the ticket price from the Goal

Seeking step 12 17. Wrap Up

 Select File tab, then select Close (to close the document) and then Exit (to close Excel).

 If you used a flash drive for this assignment, make sure you remove your flash drive before

leaving.

18. Close Excel by clicking on the Close button at the top right corner of the Window.

19. Remove your flash drive before leaving.

Page 11 of 12l

A. Use Excel Help to learn about grouping worksheets

 When you are working in Excel you can press the F1 key (at the top left of your keyboard) to

view Excel’s Help files.

or

click on the Tell me what you want to do textbox and type help to open the Excel 2016 Help

dialog box.

 When the Excel 2016 Help dialog box opens, enter the

topic that you want to learn about in the textbox near the

top of the window.

Type group worksheets in the textbox and press the Enter

key

 Select the topic, Enter data in multiple worksheets at the

same time. You may need to scroll down the results list to

find this entry.

Read this topic and note that when you group worksheets,

the changes that you make in one worksheet will be

changed in the other worksheets in the group.

Close the Help window.

B. Use Excel Help to learn about Goal Seek

 Press the F1 key (at the top left of your keyboard) to view

Excel’s Help files. Enter goal seek in the textbox near the

top of the window and press the Enter key. Select Use

Goal Seek to find the result you want by adjusting an

input value from the list of topics.

Page 12 of 12l

Rubric

Name of Excel file = YourLastName Fun Zone Amusements. -1

Name of PDF file = YourLastName Summary Formulas -1

Name of Word answer file = YourLastName EX3 Answers -1

s.5 A1 Fun Zone Amusements on all sheets -2

s.6 A1 fonts size = 18 pt, bold, italic -1/2 ea

s.7 Copy Chino sheet to new sheet, new sheet name = Summary -2, -1

s.8 B3 = Summary -1/2

s.9 C5 on each city sheet: =Summary!C5 -1 ea

s.10 Summary sheet C8: =SUM(Chino:Rosemead!C8), copy down to C14 -3, -1/2

s.10 Summary sheet D8: =SUM(Chino:Rosemead!D8), copy down to D14 -3, -1/2

s.10 Replace bottom border on C14:D14 -1

s.12 Goal Seek ticket price -3

s.13 C3: Prepared by: StudentName

s.15 Summary sheet, Display formulas, landscape, create PDF -2, -1

s.15 Adjust column widths to display in Column B, C, and D -1/2 ea

s.15 Turn off formula view, adjust columns again -1, -1/2 ea

Saved workbook should have goal seeking values (step 12) -2

s.16 Questions 1 ea