# Practice Problem 1

## CIL Practice Production Test

Type the spreadsheet information as shown below:

There are two sheets - Sheet 1 and the Dues Sheet.  You need to work both.  Start on Sheet 1.  See the Dues Sheet information at the bottom of this page.

For the Excel practice problem you will create a spreadsheet and two charts that summarize a stock club’s current stock holdings.  Type in the information as shown below then follow the instructions.  There are books available in the NTM Lab in room 311 in Elizabeth Hall that you can use while in the lab.  You need a student ID to use a book.  The tests are meant for those who have enough knowledge of the software to pass the tests.  There are classes for those who do not.

If the start file will not open - type the following on sheet 1.  The Dues sheet information is at the bottom of this page.

Step 1: Change the theme to the Trek theme and add a column heading to the right of the Value After Commission column that reads: Check Percent of Gain/Loss (do not put heading in italics) then enter the formulas below:

• Cell F4  (Initial Cost)    = shares times initial price per share

• Cell H4 (Current Value) = shares times current price per share

• Cell I4   (Gain/Loss) = current value minus initial cost

• Cell J4    (Percent Gain/Loss) = current value minus initial cost divided by current value (remember where to put parentheses)

• Cell M4 (Check Percent Gain/Loss) =Percent Gain/Loss times Current Value

• AutoSum the totals in cells E13:F13 and H13:i13

• Figure the average, highest (max), and lowest (min) for cells E14 through i16.

Step 2:  IF Statements

Set up an IF statement in cell K4 that inserts the word “Keep” in the cell if the percent gain/loss is less than zero. The word “Sell” is to be inserted in the cell if the percent gain/loss is greater than zero. Copy the formula down to rows 5 - 12.

Set up an If Statement in cell L4 that subtracts \$250 from the current value if the percent of gain or loss is more than 20% and shows zeros if the percent of gain or loss is less than 20%.  Copy the formula down to L12.  In other words you are paying an extra commission to the broker if the percent of gain is 20% or  higher.

• Determine the question, the answer if true, and the answer false.

• Remember to change all the columns to best fit.

Step 3: Use the title format for the main and subtitles.

• Change column A to 13.00 wide and autofit all of the other columns.

• Merge and center the titles across A1:L1 and A2:L2

• Change the size of the title to 18 and the subtitle to 16 points.

• Leave the text as black

• Format the title and subtitle background to Orange, Accent 1, lighter 60%.

• Put a thick box border around the cells A1:L2

• Format the column headings in cells A3:L3 to the heading 3 style, center align, and wrap text

• Format cells E4 through i4 to accounting, cells E5:i12 to comma style

• Format cells E14:i16 and L4:L12 to currency

• Format the total row (Row 13) to currency and use a total cell style

• Format cells C4:C12 to the short date format as shown in the Key

• Format cells J4:J16 to percentage

• If in doubt look at the key to determine which cells to format to currency or accounting style (accounting aligns on the left and currency has the floating dollar sign that stays with the numbers)

• Change all columns with minus numbers to show minus numbers in red and with parentheses

Step 4: Apply conditional formatting to any cell in Column J4:J12 that is less than 0.  Use a light orange fill from the theme colors for the cell background and bold the font. Make sure you can clearly see the black text over the cell background.

Step 5. Enter a “static” date and time by following these steps (not shown on key):

• In cell M1 press CTRL and ; (semicolon), which inserts the date.

o   Right click on the date and select Format Cells from the popup menu and change cell M1 formatting to show the name of the month, day, and year (08/25/2014).

• In cell M2 Press CTRL and SHIFT and : (colon) which inserts the time.

Step 6: 3-D Pie Chart

Insert a 3-D pie chart on the spreadsheet page that shows the shares of each stock purchased (remember to choose the stock titles as well as the shares).

Take the following steps to format the pie chart:

• Move the chart to fill the range A18:F33 (approximate)

• Assign Chart Style 10 to the chart.

• Delete the legend and display slice labels with the names of the stock and the percent.

• Position the labels outside the chart.

• Explode the two largest slices to  12% and change the fill color to light yellow.

• Decrease the size of the labels to 9 pts and change the color to a dark orange.

• Add a 2 pt dark brown border to the Chart area. Change the border style to rounded corners.

Step 7: 3-D Clustered Column Chart

Construct a 3-D clustered column chart that shows the percent gain or loss for each stock. Format as follows:

• Move the chart to a new sheet and name it Column Chart.

• Assign Chart Style 26 to the chart.

• Delete the legend and display data labels with the percent gain or loss.

• Format the axis labels so they include no decimal points.

• Change the color of the back wall to Brown Accent 4.

• If you did not select the column headings before inserting the chart - insert the chart title Percent Gain/Loss above the chart and add your full name under the title.

• Move the Target data label (-0.20%) above the name as shown in the key.

Final Steps:

1.   Open Print view and choose Page Setup.  Add a custom centered header to the spreadsheet with your first and last name on the left side, EX5 ASSIGN5 Practice Production Test in the middle, and the date and time on the right side (remember to do this in print view to avoid the change of view on the spreadsheet when using the ribbon header/footer option).

2.   Change the orientation to landscape and Scale to Fit 1 to 1. Show gridlines and row and column heading.

3.   Save the final version of the Excel file as [Last and first Name] EX5 ASSIGN5 and Hand in via the online System.

4.   Double check the information against the key below to ensure that the data and formatting are correct.  Check your charts again to ensure they are correct. Remember that colors differ from computer screen to computer screen.  If your theme is correct the colors will be correct.

1.    Type and format the information as shown below:  Rename the sheet "Dues" and recolor the tab to red.

2.    Calculate the percent of change in cell D3.  Subtract the 2009 amount from the 2012 amount and divide by the 2009 amount.  Remember to use parentheses where needed and to copy the formula down to cells D4:D12. HINT: =(   )/

3.   You are to calculate the total amount due from each member at the end of the year using an IF statement. Make cell G3 active.  Set up the IF statement for the first club member in Row 3 and then copy the formula down for each of the other members.  Remember to use absolute cell references and parenthesis where necessary so that the formula copies correctly.

4.    Use AutoSum to total cells B13:C13 and E13:G13.

5.    Information for the three parts of the IF statement in column G follows (please work the problem carefully and methodically before looking at the key):

Logical Test:  If a member’s percent of change increases or is equal to 5%

Value_if_True:  Members pay the reduced annual club fee of \$150 using the formula described below.

The total amount due is calculated by taking the Reduced Annual Club Fees [cell C17] minus  (the New Members multiplied by the Member Discount [cell C18] plus (the #Meetings multiplied by the Meeting Charge) [cell C19]

·         Remember absolute cell references where needed

·         Note where the parentheses are located – the cell references in brackets [C17], [C18], and [C19] denote the cells to use - they are not counted as parentheses for the formula.

IF(D3>=5%,\$C\$17-(E3*\$C\$18)+(F3*\$C\$19),\$C\$16-(E3*\$C\$18)+(F3*\$C\$19))

Value_if_False:  Otherwise, members pay the regular \$200 club fee.  Simply copy the Value_if_True  statement to this box and change the Reduced Annual Club Fees cell reference to the Regular Annual Club Fees [C16].

6.    Format Cell G3 to Currency with two decimal places.  Format cells G4:G12 to currency comma style.

KEY