…………………………………………………..
capstone_application_project_instructions.pdf
Unformatted Attachment Preview
Excel
10–11
Application Capstone Project
In This Project
This project relates to Front Range Action Sports, which is one of the country’s largest retailers
of sports gear and outdoor recreation merchandise. The company has large retail stores in
Colorado, Washington, Oregon, Idaho, California, and New Mexico, in addition to a growing
online business. Major merchandise categories include fishing, camping, rock climbing, winter
sports, action sports, water sports, team sports, racquet sports, fitness, golf, apparel, and footwear.
In this project, you will apply skills you practiced from the objectives in Excel Chapters 10 and 11.
You will develop a workbook for Frank Osei, the Vice President of Finance. You will import data
about Sports suppliers and query the database to locate information. You will assist Mr. Osei in
distributing a workbook of equipment and clothing inventory to the department managers. You will
also create a shared workbook that the Store Manager will use to review proposed salary changes.
Your completed worksheets will look similar to the ones shown in Figure 4.1.
Project Files
For Project Cap4, you will need the
following files:
eCap4_Company_Report
eCap4_Sports_Suppliers
You will save your workbook as:
Lastname_Firstname_Cap4_Company_Report
Project Results
Figure 4.1 Project Cap4 Company Report
652
Excel
|
Chapters 10–11: Application Capstone Project
1
Start Excel. From the student files that accompany this textbook, locate and open
eCap4_Company_Report. In the location where you are storing your projects, create a new
2
Run the Document Inspector. Remove all hidden worksheets.
3
Display the Sports Suppliers Info worksheet. Click in cell A9. On the DATA tab, in the Get
External Data group, click From Access. From your student files, select the Access file eCap4_
Sports_Suppliers as the data source, and then click Open. Import the data as a table in the
existing worksheet. Convert the table to a range.
a. Delete row 9 and then click cell A1. On the DATA tab, click Remove Duplicates, verify all
columns are selected and My data has headers is checked, and then click OK, leaving 17
records.
4
Display the Front Range Staff worksheet. Copy cell C3, and then paste it to cell C28. In
. Click cell B28, and then insert the
cell C29, type Customer Service Rep and press
DAVERAGE function. In the Function Arguments dialog box, as the Database, use the range
A3:F26 as the Field, type Hourly Wage and as the Criteria, use the range C28:C29 Click OK.
Apply Accounting Number Format to the result.
5
Display the Retailers Info worksheet and then click cell A5. Start your browser. In the
address bar, type www.nssra.com and press
. From the taskbar, redisplay your
Lastname_Firstname_Cap4_Company_Report workbook. On the INSERT tab, in the
Illustrations group, click Screenshot. In the displayed gallery, point to the National Ski and
Snowboard Retailers Association screen to display the ScreenTip. Click the National Ski and
Snowboard Retailers Association screenshot, if necessary, in the Microsoft Excel message box,
click No, and then notice that the screen image displays in your worksheet and is selected.
Click the FORMAT tab, in the Size group, change the Shape Height to 4.25”
6
Display the Salary Ranges worksheet. Take a moment to examine the worksheet. The
Salary Ranges sheet shows selected salary grades at Front Range Sports. Examine the other
two worksheets, Equipment Department and Clothing Dept. Each sheet contains a list of
employees, their current grade and salary, and their proposed grade and salary based on
a 2 percent increase. This information will be sent to the Department Managers of the
Equipment and Clothing departments to review and adjust. Overall, the pay increases for
the next fiscal year must be 2 percent on average, unless a promotion requires an increase in
grade. Moving to a higher grade automatically includes a salary increase.
a. Display the Highlight Changes dialog box. Select the Track changes while editing check box,
and be sure When indicates All, and Who is selected and indicates Everyone. Click OK to
save the workbook. Close the file, and then open it again. Display the Excel Options dialog
box. Write down the current User name, and then change the User name to Tony Paxton
Chapters 10–11: Application Capstone Project
|
Excel
653
Excel
folder named Front_Range_Action_Sports or navigate to this folder if you have already
created it. Save the workbook as Lastname_Firstname_Cap4_Company_Report Group the
worksheets, and insert a footer in the left section that includes the file name and in the right
section, includes the sheet name. Return to Normal view. Make cell A1 the active cell. Set
the orientation to Landscape, the Width to 1 page and the Height to 1 page. Add your name,
course information, and the Tags company report to the document properties. Return to the
workbook and ungroup the worksheets.
b. From this point forward, changes made to the workbook will be recorded as being made
by Tony Paxton, Manager of the Equipment Department. Click the Equipment Dept sheet
tab and change the following anticipated grade and proposed salaries:
Last Name
Anticipated Grade
48,000
Chang
Desprez
Proposed Salary ($)
17
46,800
53,000
Natarajan
c. Save the file and Close it. Reopen the file and change the User name to Randy Houston
From this point forward, any changes made to the workbook will be recorded as being
made by Randy Houston, Manager of the Clothing Department. Click the Clothing Dept
sheet tab and change the following anticipated grade and proposed salaries:
Last Name
Anticipated Grade
Proposed Salary ($)
Tada
57,000
Nicholson
50,100
Chu
17
Andersen
45,500
49,900
d. Save the file and Close it. Reopen the file and change the User name to Chris Jackson the
Store Director, who will accept or reject the changes. Display the Highlight Changes dialog
box, change When to All, and be sure Who is selected and displays Everyone. Click each
new salary in column F. Notice that salary changes were a change from a simple 2 percent
increase to a specified amount.
e. Display the Select Changes to Accept or Reject dialog box and save if prompted. Review
changes for Everyone but Me. Accept all changes except the change for Tada in the
Clothing department. After reviewing the changes, change Tada Anticipated Grade to 20
The higher grade is necessary to give him a full 2 percent increase in salary.
7
Save the workbook. In the Excel Options dialog box, change the User name back to the user
name that you wrote down earlier. Print or submit your workbook electronically. If required,
print or create an electronic version of your worksheets with formulas displayed using the
instructions in Project 1A. Close the workbook and Close Excel.
End | You have completed Business Running Case 4
654
Excel
|
Chapters 10–11: Application Capstone Project
…
Our essay writing service fulfills every request with the highest level of urgency.
attachment
