Test 1-version 3- Excel

Information Technology

Test1(v3)

Microsoft Excel 2010 – Lab Test II(v3)
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 – Lab Test II(v3)
Analysis of Indirect Expense Allocations
Purpose: To demonstrate the ability to plan a worksheet, create a worksheet, modify a worksheet, create a 3D column chart, and use the Goal Seek command.

Problem: You work part time as a consultant for Grand Resort & Casino. You have been asked to create an indirect expense allocation worksheet (Figure E3B-1) that will help the resort and casino administration better evaluate the profit centers described in Table E3B-1.

Instructions: Using techniques developed in the past three chapters, create the worksheet using the sample data in Table E3B-1. Your solution should be similar to that presented in Figures E3B-1 and E3B-2. Submit the following to your instructor.

Table E3B-1 Grand Resort & Casino Profit Centers Data

Casino  Business

Center Banquet
Room Conference
Rooms Gift
Shop Lounge Restaurant Spa
Total Net Revenue
1,235,356
98,190
17,8435
212,300 175,350 752,900
845,230 112,400
Cost of Sales
329,750
13,900
38,920
12,850 86,050 275,890
275,925 48,275
Direct Expenses
256,000
12,550
14,750
15,300 42,670 121,500
126,340 28,100
Square Footage 15,500 775 7,550 8,250 950 6,275 8,600 ,0

Instructions Part 1: Do the following to create the worksheet shown in Figure E3B-1.

1. Apply the Foundry theme to the worksheet. Bold the entire worksheet by selecting the entire worksheet using the Select All button and then using the Bold button on the Home tab on the Ribbon.
2. Change the following column widths: A = 30.00; B through I = 13.00; and J = 14.00.
3. Enter the worksheet titles Grand Resort & Casino and Analysis of Indirect Expenses in cells A1 and A2, respectively. Enter the system date in cell J2. Format the date to the 14-Mar style.
4. Enter the column titles in row 3 and the row titles in the range A4:A17 in column A as shown in Figure E3B-1. Use the Increase Indent button on the Home tab on the Ribbon to indent the text in the range A8:A13. The remaining formatting of the column titles and row titles will be done later in this exercise. Copy the row titles in range A8:A13 to the range A18:A23.
5. Enter the first three rows of numbers in Table E3B-1 in rows 4 through 6. Center and italicize the column headings in the range B3:J3. Add a thick bottom border to the range B3:J3. Sum the individual rows 4, 5, and 6 in the range J4:J6.
6. Enter the Square Footage row in Table E3B-1. Sum row 16 in cell J16. Format the range B16:J16 to the Comma format with no decimal places and negative numbers in parentheses. Change the height of row 16 to 42.00. Vertically center the range A16:J16 by using the Format Cells dialog box.
7. Increase the font size in cells A7, A14, and A15 to 14 point.
8. Enter the numbers shown in the range B18:B23 of Figure 3–EOC 6 with format symbols.
9. The planned indirect expenses in the range B18:B23 are to be prorated across the profit center as follows: Administrative (row 8), Energy (row 10), and Marketing (row 13) on the basis of Total Net Revenue (row 4); Depreciation (row 9), Insurance (row 11), and Maintenance (row 12) on the basis of Square Footage (row 16). Use the following formulas to accomplish the prorating:
   a. Casino Administrative (cell B8) = Administrative Expenses * Casino Total Net Revenue / Resort Total Net Revenue or =$B$18 * B4 / $J$4
   b. Casino Depreciation (cell B9) = Depreciation Expenses * Casino Square Footage / Total Square Footage or =$B$19 * B16 / $J$16
   c. Casino Energy (cell B10) = Energy Expenses * Casino Total Net Revenue / Resort Total Net Revenue or =$B$20 * B4 / $J$4
   d. Casino Insurance (cell B11) = Insurance Expenses * Casino Square Feet / Total Square Footage or =$B$21 * B16 / $J$16
   e. Casino Maintenance (cell B12) = Maintenance Expenses * Casino Square Footage / Total Square Footage or =$B$22 * B16 / $J$16
   f. Casino Marketing (cell B13) = Marketing Expenses * Casino Total Net Revenue / Resort Total Net Revenue or =$B$23 * B4 / $J$4
   g. Casino Total Indirect Expenses (cell B14) = SUM(B8:B13)
   h. Casino Net Income (cell B15) = Total Net Revenue – (Cost of Sales + Direct Expenses + Total Indirect Expenses) or =B4 – (B5 + B6 + B14)
   i. Copy the range B8:B15 to the range C8:I15.
   j. Sum the individual rows 8 through 15 in the range J8:J15.
10. Save the workbook using the file name Excel Chapter 3 – Lab Test B.
11. Add a thick bottom border to the range B13:J13.
12. Assign the Currency style with two decimal places and show negative numbers in parentheses to the following ranges: B4:J4; B8:J8; and B14:J15. Assign the Comma style with two decimal places and show negative numbers in parentheses to the following ranges: B5:J6 and B9:J13.
13. Change the font in cell A1 to 48-point Blade Runner Movie (or a similar font). Change the font in cell A2 to 26-point Britannic Bold (or a similar font). Change the font in cell A17 to 18-point italic Britannic Bold (or a similar font).
14. Use the background color light green (column 5 under Standard Colors) and the font color white (column 1, row 1 under the Theme Colors) for the ranges A1:J2, A7, A15:J15, and A17:B23 as shown in Figure E3B-1.
15. Rename the Sheet1 sheet, Indirect Expenses Analysis, and color its tab light green.
16. Update the document properties as specified by your instructor. Change the worksheet header with your name, course number, and other information requested by your instructor.
17. Use the Zoom button on the View tab on the Ribbon to zoom to 75%.
18. Preview the worksheet by clicking the Office button, pointing to Print, and clicking Print Preview. Use the Page Setup button in Print Preview to fit the printout on one page in landscape orientation. Save the workbook.
19. Preview the formulas version (CTRL+) of the worksheet in landscape orientation. Press CTRL+ to show the values version of the worksheet.
20. Use the Zoom button on the View menu on the Ribbon to zoom to 100%. Divide the window into four panes by selecting cell F7 and using the Split button on the View menu on the Ribbon. Show the four corners of the worksheet. Remove the four panes. Close the workbook without saving it.
21. Submit the workbook as requested by your instructor.

Instructions Part 2: In this part, you will create the 3-D Column chart shown in Figure E3B-2. Do the following:

1. Start Excel. Open Excel Chapter 3 – Lab Test B.
2. Draw a 3-D Column chart that shows the contribution of each category of indirect expense to the total indirect expenses. That is, chart the nonadjacent ranges A8:A13 (category names) and J8:J13 (data series) using the CTRL key. Move the chart to a separate sheet. Delete the legend.
3. Format the columns and wall behind the columns as shown in Figure E3B-2. That is, change the colors of the columns to red and the wall to blue. To change the colors of the columns, select the columns and right-click; choose Format Data Series; choose Fill; and select the desired color. Follow the same steps to format the wall behind the columns.
4. Use the Chart Title button on the Layout tab on the Ribbon to add the chart title Indirect Expenses. Change the font to Rockwell, 28 point font, Red. Format it as shown in Figure E3B-2.
5. Rename the chart sheet 3-D Column Chart and color the tab red. Move the chart tab to the right of the worksheet tab.
6. Save the workbook and then close the workbook. Submit the workbook as requested by your instructor.

Table E3B-2 Grand Resort & Casino Indirect Expense Allocations What-If Data

 Case 1  Case 2     

Administrative 324,000 156,000
Depreciation 156,575 162,000
Energy 72,525 56,000
Insurance 46,300 67,000
Maintenance 75,000 48,000
Marketing 39,000 82,400