Microsoft Excel Practice Exam 2 - CS 12 Lab

[Pages:3]Microsoft Excel Practice Exam 2

Instructions to download and unzip the file needed to perform this practice exam.

Go to the Practice Exam Files Download web page: on the CSCI 012 Lab Website to follow the instructions to obtain the practice exam files.

Open the file Practice_2 and save it with the name Practice_2_Solution.

Note: Each section in this exam corresponds to one of the worksheets in the spreadsheet file. For example, Fuel Estimates corresponds to the Fuel Estimates worksheet in the spreadsheet file.

Fuel Estimates: 1. Type your name into cell A22. Use the TODAY function to insert today's date into cell A23. Center and merge Fuel Estimates across cells A1 through H1. Change its font size to 16 and bold it. Format the headings in row 3 to wrap text, bold and center. Change the fill color for cells A1:H3 and A15:H15 to Orange, Accent 6 and the font color to White, Background 1.

2. Adjust column widths and use functions when necessary. Enter formulas to fill in cells according to the following:

Fuel Required in cell D4: The fuel required is the gallons per hour consumed by the plane multiplied by the number of flying hours. In this spreadsheet, each flight uses a particular type of plane. Each type of plane consumes fuel at different rates. The Fuel Required formula should be general enough to compute its value regardless of the type of plane.

Sometimes a formula is more than just using a single function. Also, it is useful to create the formula on paper before typing it into the cell. Therefore, the first part of the formula uses the VLOOKUP function to lookup the gallons per hour in the lookup table of cells A16:C18, based on the type of plane given in cell A4. The VLOOKUP function will return the appropriate gallons per hour from the 3rd column of the lookup table.

Once you have the VLOOKUP function constructed, complete the formula by multiplying the VLOOKUP function by the number of flying hours.

Reserve Fuel in cell E4: The fuel required multiplied by the percent of flying fuel required for reserves.

Holding Fuel in cell F4: The fuel required multiplied by the percent of flying fuel required for holding.

Total Fuel in cell G4: The sum total of the fuel required, reserve fuel and holding fuel.

1

Cost in cell H4: The cost is the total fuel multiplied by the price per gallon. But, the price per gallon depends on the amount of fuel required, given in cell D4. If the fuel required reaches or exceeds the threshold number of gallons, then the flight's cost qualifies for the reduced price per gallon; otherwise, the flight's cost qualifies for the normal price per gallon. The Cost formula should be general enough to compute its value using the appropriate price per gallon based on the fuel required compared to the threshold number of gallons.

See the Fuel Information table in the Fuel Estimates worksheet. Remember to use the cell address of a value in your formula if the value exists in the worksheet rather than the actual numeric value.

Copy the formulas in cells D4:H4 to the 6 rows below row 4, rows 5 to 10.

Totals in row 11: Compute the sum total of each column from column D to H.

Average Value in row 12: Compute the average of each column from column D to H.

Maximum Value in row 13: Compute the maximum value of each column from column D to H.

3. Format cells B11:H13 to bold. Format cells D4:G13, H16, and B16:C18 to zero decimal places with the comma style. Format cells H4:H13 to currency with zero decimal places. Format cells H17:H18 to currency with two decimal places. Format cells H19:H20 as percentage with zero decimal places.

4. Insert a thick bottom border for cells A3: H3, A10:H10, A13:H13, and A15:H15.

5. Press the Save button to save the spreadsheet file.

6. Make a copy of the Fuel Estimates worksheet, placing the worksheet copy between the Fuel Estimates and Chart worksheets. Rename the copied worksheet with the new name Sorted.

Sorted: Click the worksheet tab labeled Sorted. Sort the entire list of flights by sorting the Planes in ascending order. Create a custom header for the Sorted worksheet that contains your name on the left and your email address on the right. Press the Save button to save the spreadsheet file.

2

Chart: Click the worksheet tab labeled Chart. Use cells A2 through E12 to form a Scatter with Smooth Lines and Markers chart. Insert the above chart title: Study Time and Quiz Scores. Insert the Primary Horizontal axis title: Time in Hours. Insert the Primary Vertical axis title: Test Score. Move and resize your chart to fit it within the chart area specified in the worksheet. Press the Save button to save the spreadsheet file.

PMT: Click the worksheet tab labeled PMT. Enter a formula in cell B6 using the PMT function to calculate the monthly payment for a 30 year mortgage with the yearly interest rate given in cell A6. Enter a formula in cell C6 using the PMT function to calculate the monthly payment for a 15 year mortgage with the yearly interest rate given in cell A6. Enter a formula in cell D6 to compute the difference between the monthly payment of the 15 year mortgage and the 30 year mortgage. Copy the formulas in cells B6:D6 to the 5 rows below row 6, rows 7 to 11. Press the Save button to save the spreadsheet file.

Exit Microsoft Excel.

3

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download