Module 1 Capstone Instructions

Overview In the following capstone project, you will create an Excel from a blank file. This file will incorporate the items learned in Chapter 1 and Chapter 2. Each step has a point value associated with it and is shown in the instructions below.

Steps

Start a new (blank) workbook in Microsoft Excel. Change the Theme of the spreadsheet to Facet. (5 pts)

Enter the following information in the cells as listed: (16 pts)

A1 Pro Fit Colrado

A2 2014 Regional Sales at Fitness Clubs

B3 Quarter 1

A4 North

A5 South

A6 West

A7 East

A8 Total Sales

Use Auto-fill to fill Quarter 1 across to E3 (you should have Quarter 2 to Quarter 4) (3 pts)

Enter column headings as follows after Quarter 4: (12 pts)

F3 Total

G3 % of Total Sales

H3 Trend

I3 Average

J3 Maximum

K3 Minimum

Ensure the column width of Column A is 10.00 (97 pixels), Column F is 18.00 (169 pixels), and 15.00 (142 pixels) for Columns B through K (excluding F). (6 pts)

Merge & Center cells A1:K1. Change style to Heading 1. (4 pts)

Merge & Center cells A2:K2. Change style to Heading 2 (4 pts)

Enter the following data into the quarterly columns (5 pts)

	Quarter 1	Quarter 2	Quarter 3	Quarter 4
North	225684.50	210760.45	181555.32	238634.85
South	138792.12	145645.29	142160.81	195874.75
West	292716.67	274914.48	262874.14	281798.99
East	228698.82	247761.49	235749.22	214749.45

For B8:F8, use the SUM function to add up the values from the four regions. (5 pts)

For F4:F7, use the SUM function to add up the values from the four quarters. (5 pts)

For cell G4, calculate the % of Total Sales (HINT: divide the regional total by the Total Sales in Cell F8). Copy G4 down to G7 (HINT: use an absolute value as necessary in your G4 formula to prevent errors). (5 pts)

Insert Line Sparklines in ranges H4:H7 (HINT do not include totals). Show Sparkline Markers. Change color of the Sparklines to a color of your choice. (5 pts)

For I4:I7, calculate the mean of each region using the AVERAGE function. (5 pts)

For J4:J7, calculate the maximum value of each region using the MAX function. (5 pts)

For K4:K7, calculate the minimum value of each region using the MIN function (5 pts)

Format cells A3:K3 as Heading 3 style. Format A8:K8 as Total style. (4 pts)

Format first row of monetary numbers (North) and Total Sales row to Accounting number format. Format all other monetary numbers to Comma number format. Format percentages to percent with two decimals. (6 pts)

Save the file with this naming convention: LastName_FirstInitial_Module1Capstone (i.e. Weide_J_Module1Capstone)

Submit completed file to Module 1 Capstone Dropbox before due date.