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

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

# 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%)
• adjust the columns widths to something reasonable
• change the font and size of the headings, maybe some color would be nice as well...
• freeze the heading row.
• change the format of columns C, H, and I into Euro, and the resulting total Dollar price into American Dollars.
• turn the alignment of the of some cells in the heading row (row 6) until the text is vertical---that allows you to keep the column width small. Allow text wrap there.
• Use conditional formatting to highlight (for instance, in boldface) those instances where the amount ordered is larger than the amount available (since then the order would be delayed).
• Use conditional formatting to highlight those cases where after the order, fewer than 10 items are left in stock.
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