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.
- 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
- 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.
- Compute the total weight of the order. (5%)
- 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%)
- Compute the price for the total order including shipping and handling.
- Compute the remaining items in stock. (5%)