Project # 1:

  E x c e l   P r o j e c t  

CPT 105 · Franklin College · Erich Prisner · 2002-2006

Project #1

Excel Project

Deadline for the project is Thursday, February 12th.

Download the following Excel sheet. It contains a list of computer items you can buy at an online computer shop. Displayed are the prices per item without sales tax and without shipping and handling. The sales tax rate is displayed in cell B2. Shipping and handling depend both on the total weight of your order and on the total sum (with tax, without shipping and handling, of course, and in Dollars, since it is an American company), as can be seen in rows 3 to 5.

For CDs and DVDs, there are three prices displayed in column B. The first one is valid per item if you buy between 1 and 9 CDs or DVDs (no matter which brands, just the sum counts). The second one is valid if you buy between 10 and 99 of them. The third one applies if you buy more than 100 of them.

  1. Format the sheet (50%)
  2. Compute the total weight of the order. (5%)
  3. Compute the total prices without and with tax, and also convert this into Dollars using the conversion rate that you should write into cell L2. Be in particular careful in the CD, DVD rows, use the "IF(condition,yespart,nopart)" command there. (30%)
  4. Compute the price for the total order including shipping and handling. (10%)
  5. Compute the remaining items in stock. (5%)

Erich Prisner, September 2003