Task 1 and Task 2 excel sheets

/in /by

Task 1.

•Download Excel1.xls from the BeachBoard.

•The last 2 digits of the product number indicates whether the product is a VHS or a DVD: 00 for VHS and 90 for DVD. (Note: do not rely on the product description for VHS or DVD; it is not always accurate).

Question 1.

•Calculate the total amount due of all transactions.

•Calculate the total amount due for VHS and DVD products.

•Calculate the percentage (to 2 decimal digits) of VHS and DVD amount due in relation to total amount due.

•Present your finding — the different amount due and percentages — in a new tab or file. Format it and make it presentable with clear label.

Question 2.

•There are reports of errors in pricing. The following is the correct price list. If the correct price is lower than old price, that means we have over-charged the customers.

•(Note. The same products may have different price points at different time. The following only applies to the particular price point mentioned.)

Product Number

Old price (on spreadsheet)

Correct price

00200290

8.24

6.99

N4709 90

11.99

12.69

02132490

16.25

11.54

N5415 00

13.10

11.34

•Update the spreadsheet with the correct price. Compare the new total amount due with the old one. Decide the amount that we should pay to the customers (or they should pay us).

•Present your finding in a new tab or file. Format it and make it presentable.

Note: All numbers should have a comma (,) every 3 digits, and to 2 decimal points.

All percentages should use a percentage sign (%) and to 2 decimal points.

Task 2

Download Excel2.xls from the BeachBoard.

Calculate how many transactions have incorrect Amount.

Note: Amount is price*qty, and applying the discount. If Discount Percentage is 0, that means no discount is applied. If Discount Percent is, say, 0.2, that means 20% discount is applied, and the amount should be price*qty*0.8.

Note: Notice that the Amount is a result of rounding to the 2nd decimal digits.

Add a column and label it “ErrorCount”. Assign it a value of 1 if the Amount is incorrect; assign it a value of 0 if otherwise.

At the bottom of the sheet, total the ErrorCount.

You are free to add new columns for interim calculations.

"Our Prices Start at $11.99. As Our First Client, Use Coupon Code GET15 to claim 15% Discount This Month!!":

Get started