Excel Spreadsheet Help Needed

Application Exercises 521

c. Suppose other team members want to use your spreadsheet. Name three ways you can share it with them and describe the advantages and disadvantages of each.

r AE2-2. ,_. 'A

u~t:r.m Suppose that you have been asked to assist in the managerial decision about how much to increase pay in the next year. Specifically, you are tasked to deter-

mine if there are significant salary differences among departments in your company. You are given an Access database with a table of employee data with the following

structure:

EMPLOYEE (Name, Department, Specialty, Salary)

where Name is the name of an employee who works in a department, Department is the department name, Specialty is the name of the employee's primary skill, and Salary is the employee's current salary. Assume that no two employees have the same name. You have been asked to answer the following queries:

( 1) List the names, department, and salary of all employees earning more than $100,000.

(2) List the names and specialties of all employees in the Marketing department. (3) Compute the average, maximum, and minimum salary of employees in your

company. (4) Compute the average, minimum, and maximum salary of employees in the

Marketing department. (5) Compute the average, minimum, and maximum salary of employees in the

Information Systems department. (6) Extra credit: Compute the average salary for employees in every department.

Use Group By.

a. Design and run Access queries to obtain the answers to these questions, using the data in the file Ch02Ex02_Ul0e.accdb.

b. Explain how the data in your answer contributes to the salary increase decision. c. Suppose other team members want to use your Access application. Name three ways

you can share it with them, and describe the advantages and disadvantages of each.

Chapter 3 AE3-l. Figure AE-1 shows an Excel spreadsheet that the resort bicycle rental business uses

to value and analyze its bicycle inventory. Examine this figure to understand the meaning of the data. Now use Excel to create a similar spreadsheet. Note the following:

• The top heading is in 20-point Calibri font. It is centered in the spreadsheet. Cells Al through Hl have been merged.

• The second heading, Bicycle Inventory Valuation, is in 18-point Calibri, italics. It is centered in cells A2 through H2, which have been merged .

.. A B C D E G H 1 Resort Bicycle Rental 2 Bicycle Inventory Valuation 3j Saturday, May 27, 2018

Revenue as Number on Cost of Current Number of Total Rental Revenue per Percent of Cost

4 Make of Bike Bike Cost Hand Inventory Rentals Revenue Bike of Inventory S •wander Bike $325 12 $3,900 85 $6,375 $531 163.5% 6 .Wonder Bike II $385 4 $1,540 34 $4,570 $1,143 296.8% ,,---. -

$475 $3,800 $5,200 $650 FIGURE AE-1 7 Wonder Bike Supreme 8 44 136.8% Excel Spreadsheet 8 Litelift Pro $655 8 $5,240 25 $2,480 $310 47.3%

9 Litelift Ladies $655 4 $2,620 40 $6,710 $1,678 256.1% Source: Microsoft Corporation 10 Litelift Racer $795 3 $2,385 37 $5,900 $1,967 247.4%

522 Application Exercises

• The column headings are set in 11-point Calibri, bold. They are centered in their cells, and the text wraps in the cells.

a. Make the first two rows of your spreadsheet similar to that in Figure AE-1. Choose your own colors for background and type, however.

b. Place the current date so that it is centered in cells C3, D3, and E3, which must be merged.

c. Outline the cells as shown in Figure AE-1. d. Figure AE-1 uses the following formulas:

Cost of Current Inventory = Bike Cost x Number on Hand Revenue per Bike = Rental Revenue/Number on Hand Revenue as a Percent of Cost of Inventory = Total Rental Revenue/

Cost of Current Inventory Use these formulas in your spreadsheet, as shown in Figure AE-1.

e. Format the cells in the columns, as shown. f. Give three examples of decisions that management of the bike rental agency might

make from this data. g. What other calculation could you make from this data that would be useful to the

bike rental management? Create a second version of this spreadsheet in your work- sheet document that has this calculation.

AE3-2 . .....,.., In this exercise, you will learn how to create a query based on data that a user · t_:,....,_ enters and how to use that query to create a data entry form. a. Download the Microsoft Access file Ch03Ex02_Ul0e.accdb. Open the file and

familiarize yourself with the data in the Customer table. b. Click Create in the Access ribbon. Click the icon labeled Query Design. Select the Cus-

tomer table as the basis for the query by double-clicking on Customer. Close the Show ~ Table dialog. Drag CustomerName, CustomerEmail, DateOtLastRental, BikeLas- tRented, TotalNumberOfRentals, and Tota!RentalRevenue into the columns of the query results pane (the table at the bottom of the query design window).

c. In the CustomerName column, in the row labeled Criteria, place the following text: [Enter Name of Customer:] Type this exactly as shown, including the square brackets. This notation tells

Access to ask you for a customer name to query. d. In the ribbon, click the red exclamation mark labeled Run. Access will display a dia-

log box with the text "Enter Name of Customer:" (the text you entered in the query Criteria row). Enter the value Maple, Rex and click OK.

e. Save your query with the name Parameter Query. f. Click the Home tab on the ribbon and click the Design View (upper left-hand button

on the Home ribbon). Replace the text in the Criteria column of the CustomerName column with the following text. Type it exactly as shown:

Like"*"& [Enter part of Customer Name to search by:]&"*"

g. Run the query by clicking Run on the ribbon. Enter Maple when prompted Enter part of Customer Name to search by. Notice that the two customers who have the name Maple are displayed. If you have any problems, ensure that you have typed the phrase above exactly as shown into the Criteria row of the CustomerName column of your query.

h. Save your query again under the name Parameter Query. Close the query window. i. Click Create on the Access ribbon. Under the Forms group, choose Form Wizard. In

the dialog that opens, in the Tables/Queries box, click the down arrow. Select Query: Parameter Query. Click the double chevron<< symbol and all of the columns in the query will move to the Selected Fields area.

Application Exercises 523

j. Click Next two times. In the box under What title do you want for your form? enter Cus- tomer Query Form and click Finish.

k. Enter Maple in the dialog box that appears. Access will open a form with the values for Maple, Rex. At the bottom of the form, click the right-facing arrow and the data for the second customer named Maple will appear. What is that customer's first name? will appear.

I. Close the form. Select Object Type and Forms in the Access Navigation Pane. Double- click the Customer Query Form and enter the value Amanda. Access will display data for all three customers having the value Amanda in their name.

Chapter 4 AE4-l. l Sometimes you will have data in one Office application and want to move it to

I'. another Office application without rekeying it. Often this occurs when data was created for one purpose but then is used for a second purpose. For example, Figure AE-2 presents a portion of an Excel spreadsheet that shows the assignment of computers to employees.

Suppose you want to use this data to help you assess how to upgrade computers. Let's say, for example, that you want to upgrade all of the computers' operating systems to Windows 10. Furthermore, you want to first upgrade the computers that most need upgrading, but suppose you have a limited budget. To address this situation, you would like to query the data in Figure AE-2, find all computers that do not have Windows 10, and then select those with slower CPUs or smaller memory as candidates for upgrading. To do this, you need to move the data from Excel into Access.

Once you have analyzed the data and determined the computers to upgrade, you want to produce a report. In that case, you may want to move the data from Access back to Excel, or perhaps into Word. In this exercise, you will learn how to perform these tasks. a. To begin, download the Excel file Ch04Ex0l_Ul0e.xlsx into one of your directo-

ries. We will import the data in this file into Access, but before we do so, familiarize yourself with the data by opening it in Excel. Notice that there are three worksheets in this workbook. Close the Excel file.

b. Create a blank Access database. Name the database Ch04Ex0l_Answer. Place it in some directory; it may be the same directory into which you have placed the Excel file, but it need not be. Close the default table that Access creates and delete it.

c. Now, we will import the data from the three worksheets in the Excel file Ch04Ex01_ UlOe.xlsx into a single table in your Access database. On the ribbon, select Exter- nal Data and in the Import & Link section, click Excel. Start the import. For the first

A 8 0 G 11 1 '.£m.P:L'astName EmpFirs.tName Plant Computer Sr:and CPl:11{GHtz) Memc;,ry1(GB) D!Sk,{TB) OS 2

1 Ashley Linda Oerwer 0.11 .3 16 2 Windows 10

3 ~O.avicls.ol'l Vktor Dr.n11er full 3 12 2 Windows1'0 4 Chil:'!g Diem fhi Denver llP .3 8 2 . .5 Win&tws,8

.s 'eomns Iemes O.erw.er 0,1/ 2:S 1 Windows 7 6 -COming Haley Oenwer llP 3 8 2 \t-;lindows.B

7 Scott Rkhard Deneer llP 2.5 :s 25Wlndo.ws.S 8 ,Cor.ovic Ann.a Denver Dall 4 12 JWindows"l.G

9l••• Kath¥ Denver Lenovo 25 '6 1 Windows 7 WiWei James Denver IBM 3 16 l Vrlindo-:w_s '1'0

u joix:on M.ary Oen11.er IBM 2 ,6 1Window.s7 t'2 ltee M:a;t:tthi;ew O.e-m,wr 0,11 2_5 .6 1Windo·ws7 ,

'Sreven Oerwer 'oell 2 2 U ~Duong 0,7.5Vi1:ta 14 Bosa William D.enw.er llP .3 s .2~5 ~,~inc!O?WS S LS o ... w ifciray Deever !JP .3 :s 2 Windows:S 16 ·Ad.ams Mark Denver llP 2-5 '4 1 M/iDOo-ws 7

FIGURE AE-2 l7 - L1.ur.idei;i Afk:ole Denver te/,IOl/,o 4 u .3Win<loo.4s"1!0 ta iUitr.an Brv.an Denver Dell .3 :s 2Windi:wvs:S

Sample Excel Data for Import L9 Source: Microsoft Corporation 201 Pr'iman,Contact: Kaye O.allklson