CSCI 104 Fall 2016, Excel, Practical Portion

You should perform all computations using excel. The worksheets should be set up so that the order is entered in the section of the Order Form with an orange background and all computations are based on these quantities.

Unless otherwise specified the exact appearance of the document does not have to match the example below, but all of the elements must be in place.

You may ask for help, but I may not provide that help. You may use your notes, book and my on line notes if you wish.

If you get stuck on a portion of the test, move on. Complete what you can.

Please be aware that the dropbox folder will close at 10:55 for the 10:00 am class and 11:55 for the 11:00 am class.

There are 10 points which can be deducted for poor, sloppy, or incorrect work. This includes unformated or inappropriately formatted data.

You may use your books, notes and the internet to do this portion of the test. You may not communicate with another person while working on this portion of the test.

1. Download a copy of this document.
   1. Move to the Order Form worksheet
      1. [1 point] Change the width of column A so that all description fields fit.
      2. [1 point] Place your name in cell B1
      3. [2 points] Make the labels in A1:B3, A5:G5 and F23:F26 bold
      4. [1 point] Change the width of column B so that the full description for each product is visible.
      5. [1 point] Place a double border under cells A5 through G5
      6. [1 point] Merge cells I2:L2 and center the title.
      7. [2 point] Format the titles in cells A5: G5 to be readable. This includes wrapping text.
      8. [1 point] Format the column C as currency.
      9. [2 points] In column F, compute the extended cost by multiplying the quantity by the cost for all items.
      10. [2 points] In cells F21 and G21 compute the total cost and total shipping weight.
      11. [1 point] Using a formula, place the total cost in cell G23
      12. [2 points] Compute the tax based on the Subtotal and the tax rate in J1. Place this value in G24
      13. [3 points] Using an exact match, find the shipping cost. This is based on the shipping zone, in cell B2, the Shipping Table in cells I5:L7, and the shipping type stored in cell B3. Multiply this cost by the total shipping weight in cell G21 to find the shipping cost, which should be placed in cell G25.
      14. [1 point] Compute the total cost in G26, which is a combination of the subtotal, the tax and the shipping.
   2. Move to the Financing worksheet. On this worksheet you will compute the monthly payments for financing the order from the previous sheet. All of the variables associated with the loan are base upon the loan amount. These terms are in the Finance table in cells A10 through D15.
      1. [2 points] Using a formula, copy the Total due from the Order Form sheet to cell b1 of this worksheet.
      2. [3 points] The percent down is computed based on the total purchase and the down payment column in the Finance Table. Compute the percent down and place it in cell B2
      3. [2 points] The down payment is based on the total purchase and the percent down. Compute the down payment in cell b3.
      4. [2 points] The amount financed is based on the total purchase and the down payment. Compute the amount financed in cell B4.
      5. [3 points] The interest rate is based on the total purchase, and can be found in the finance table. Compute the interest rate in cell B6.
      6. [3 points] The period of loan is based on the total purchase, and can be found in the finance table. Compute the period of the loan cell B7.
      7. [5 points] The monthly payment is based on the amount financed, the interest rate and the period of the loan. Compute the monthly payment in cell B8.
   3. Move to the Bar Comparison worksheet
      1. [1 point] Insert a new column after Total Fat (D). Label this column Calories From Fat in cell E1.
      2. [3 points] Calculate the calories from fat in this new column for each candy bar. This is found by multiplying the fat in a candy bar by the calories per gram for fat in cell c31.
      3. [1 point] Insert a new column after Calories From Fat. Label this column Low fat
      4. [3 points] In the new column compute if the candy is low in fat. For this computation a food is low in fat if the calories from fat are less than 30% of the total calories. Use a computation to place a Yes in this column if the candy has fewer than 30% calories from fat and a no otherwise. Use the value stored in B32 for his computation.
      5. [3 points] Create a table from cells A1:I28
      6. [2 pints] Sort the table by Chocolate bar name, ascending.
      7. [2 points] Filter the table so that only items with less than 250 Calories are visible.
      8. [4 points] Add a total row showing the average Calories, maximum Total Fat
      9. [5 points] Insert a column chart showing the Calories From Fat for each of the candy bars. Make sure the chart is properly labeled
   4. Save your workbook and upload it to the appropriate drop box folder
      • Students taking the test at OSD please upload to the OSD Excel Test folder. Anyone not taking the test at OSD who places a test in this folder will suffer a 10% penalty in addition to a 10% penalty for each half hour the test is submitted after the folder for their class closes.
      • Others please upload the test to the Excel Test folder.
      • If you miss the folder close time, you may email the test to dbennett@edinboro.edu. There is a 10% per half hour penalty for this based on the time stamp on the email.