Test 1-version 1- Excel

Information Technology

Test1(v1)

The Following are the Fill-in-the-blank questions. Select the most appropriate word or phrase from the ones supplied.

_________ alignment is the Excel default for cells containing numeric data.

_________ is the Excel function that will produce the date and time in the cell where it is used.

________ is the term used for alphabetic, alphanumeric, or non-computational numeric data stored in the cell.

________ is the Excel arithmetic expression to add the contents of cell B5 to the contents of cell B6 and dividing the resulting sum by 3.

The basic unit of storage in a spreadsheet is the ____, which is at the inetersection of a column and row.

A(n) _____ is a user defined group of cells in a worksheet.

________ is the Excel expression using a function that will result with the highest value contained in cells C4 to C8 inclusive.

_________ is the Excel expression expression using a function that will result with the lowest value contained in cells C5 to C8 inclusive.

__________ is the term generally used to refer to arithmetic expression stored in a cell.

In the arithmetic expression = A10*$B$25, the reference to the contents of cell B25 is said to be a(n) _______ reference.

When the arithmetic expression = G6+G7+G8+G9 (found in cell G10) is copied to cell H10, the result would be = H6+H7+H8+H9 – this is an example of a(n) _______ reference.

__________ is the Excel expression using a function that will result in the totalong of the contents of cells C5 to H5 inclusive.

Computational numeric data stored in a cell are generally referred to as a(n) _________.

____________ alignment is the Excel default for cells containing non-numeric data.

Centre aligned =date()+time() =tot(C5:H5) =today() right formula
Numbers range =min(c5:c8) group cell left
Label alpha data absolute address =average(f4:f9) block =(b5+b6)/3
=now() =count(c5:h5) =max(c4:c8) =sum(c5:c8) relative address typeface
Point size =b5+b6/3 =avg(f4:f9) =min(c5:c8) =sum(c5:h5) value
=max(c4:c8) equation =high(c4:c8) =average(f4:f9) =low(c5:c8) =(b5:b6)/3

Microsoft Excel 2010 – Test01(v1)
Creating a Current and Projected Quarterly Analysis Worksheet

Problem: You are the accountant for JJW Discount Warehouse. The president of the company, JW, is concerned that the store is carrying too many customers on its own in-store credit accounts (referred to as FE Accounts). He has asked you to create a worksheet for the second quarter of sales that depicts the amount of sales by cash, bank cards (referred to as BC), credit through a local finance company (referred to as Finance Co.), and layaway. Furthermore, he wants to see projected third quarter sales. Finally, the worksheet should show the percent of total sales by each type of payment for April, May, and June.

Part 1 Instructions: Perform the following tasks to create the worksheet in Figure E3A-1.

1. Bold the entire worksheet. Enter the worksheet title, JJW Discount Warehouse, in cell A1. Enter a subtitle, Quarterly Sales Analysis, in cell A2. Change the height of rows 1 and 2 to 18.00 points.
2. Change the font of cells A1 and A2 to any available font besides Arial or Times New Roman.
3. Center the two titles across columns A through G.
   Change the background color of the first two rows to aqua (column 5, row 3 of the Color palette) and the color of the text to white.
4. Enter the words, Prepared by, followed by your initials in cell A3. Enter the NOW function in cell G3, and change the format to dd-mmm-yy style.
5. Enter the words, Second Quarter Sales, in cell A4 and the words, Projected Third Quarter Sales, in cell E4.
6. Enter the first month of the third quarter, APR, in cell B5 and right-align it. Use the fill handle to create the month series in row 5, so the headings read APR through SEP.
7. Change the column headings’ background colors in row 5 to sky blue (column 6, row 4 of the Color palette).
8. Enter the row titles from Table E3A-1 below, starting in cell A6.
9. Change the width of column A to 12.00 points and of columns B through G to 10.57 points.
   Enter the data shown in Table E3A-1 below in the range B6:D10. Format the range B6:G10 to Comma style with two decimal places.
   APR MAY JUN
   Cash 11,985.00 10,697.00 11,357.00
   BC 9,951.19 10,624.16 9,852.19
   FE Accts 7,384.36 8,212.04 7,613.36
   Finance Co. 4,582.57 4,127.48 4,546.57
   Layaway 609.74 681.36 673.74
   Table E3A-1
10. The projected third quarter sales are based on previous quarter’s sales. To calculate the month of July, increase June sales by 1.5 percent. August is expected to have a 3.0 percent increase over July sales, and September is expected to have a 5.5 percent increase over August. Enter the following formulas in the designated cells: E6=D61.015; F6=E61.03; and G6=F6*1.055. Use the fill handle to copy the formulas for each column.
11. Enter the title, Total Sales, in cell A11 and change the background colors in the range A11:G11 to yellow. Use the SUM function to calculate the total sales for April. Use the fill handle to copy the sum for all the columns. Format the range B11:G11 to comma style with two decimal positions.
12. Complete the following entries.
    a. In cell A12, enter the title, Analysis
    b. Copy the labels for the five types of sales, Cash, BC, FE Accts, Finance Co., and Layaway, from cells A6:A10 to A13:A17
    c. Calculate the percentages for each of the five types of sales by dividing the APR value by the Total. The APR cash sales in cell B13 is calculated using the following formula: =B6/$B$11
    d. Use the fill handle to copy the formula down to cell B17. Format these cells as Percent with no decimal places
    e. Create similar entries for MAY and JUN in columns C and D
    f. Change the color of the range A12:D17 to gray-25%
    g. Adjust the height of row 12 to 21.00 point
13. Enter your name in cell A20. In the cells directly below your name, enter your course identification, computer lab assignment (Lab Test A – Excel Chapter 3), date, and instructor name.
14. Rename the Sheet 1 tab, Analysis.
15. Save the workbook using the file name, Yourname – Excel Chapter 3 – Lab Test A, where Yourname is your own last name.
16. Preview and print the worksheet. Preview and print the formulas (CTRL+`) in landscape orientation using the Fit to option button in the Page Setup dialog box.
17. Redisplay the values version of the worksheet.

Part 2 Instructions: Using Chart Wizard, create the 3D pie chart chart illustrated in Figure E3A-2 showing the monthly amount of sales based on JJW Discount Warehouse’s customer credit (FE Accts). This chart uses the nonadjacent ranges A5:G5 (category) and A8:G8 (data series). Place this chart on a separate sheet.

Make the following modifications to the chart.

1. Double underline the chart title and make its font size 24 point.2.Rename the tab for the chart to Credit Chart. Rearrange the tabs so Analysis is before Credit Chart.
2. Delete unused sheets and save the workbook again.

Part 3 Instructions:

1. You determine that the increases for each type of sale may not be the same. You need to add an additional area to the worksheet called Projections. Enter the label, Projections, in cell E13. Put the data from Table E3A-2 in cells E14:F17.

Projections
BC 1.07
FE Accts 1.065
Finance Co. 1.015
Layaway 1.03
Table E3A – 2

1. Modify the formulas used for noncash third quarter projections in cells E7:G10. Using absolute references, update the July formulas using the projections in the range F14:F17. Use the fill handle to copy these formulas across for August and September sales.
2. JW is concerned that too many sales are made on in-house charge accounts. Use the Goal Seek command to determine the FE Accts amount for April, if the percentage is to be only 12%.
3. Save the workbook as Yourname – Excel Chapter 3 – Lab Test A-P3.
4. Print this revised Analysis sheet. It should display as shown in Figure E3A-3.

Figure E3A – 1

Figure E3A – 2

Figure E3A – 3