Question

Bookstore Sale The SOU’s bookstore is having a student appreciation sale. Students will receive a discount on all nontextbook items, depending on the total spent on those items. Discounts will be applied as follows:

$0 - $50.99 4%

$51.00 - $60.99 5%

$61.00 - $70.99 6%

$71.00 – $80.99 7%

$81.00 – $90.99 8%

$91.00 – $100.99 9%

$101.00 or more 10%

Regular prices

Book Bags $40

Sweatshirts $20

Magazines $3

Notebooks $2

Candy $1

Spreadsheet will show the sales for three students. The regular prices will be placed in a table at the top of the sheet, along with today’s date and your name. (Make the date static so it won’t update when the spreadsheet is opened later.) The volume discount will be placed in a second table. You will make a table showing how many of each item the student has purchased (you will make up the number of items). Calculate the total for the sale (must use SUMPRODUCT), and then use a VLOOKUP function to determine the percentage discount. The percentage needs to display the dollar amount and not the discount percentage. Apply this percentage to the total to determine the discount amount in dollars. Subtract the discount price from the regular price to calculate the total sale. Try different purchases with different total amounts. Check to make sure that the correct discount is calculated for each sale. The bookstore manager is also interested in the total, average, largest, and smallest sale totals. Use currency format with two decimal places for the all dollar amounts and percent format with 0 decimal places for the percentage amounts. Make formatting changes to improve the look of the spreadsheet (enhancements), add clipart or picture to your worksheet and submit finished worksheet in Moodle. If you use a table, be sure to convert the table back to a normal range before submission.

student 1 student 2 student 3

Book Bags	1	2	1
Sweatshirts	2	1	0
Magazines	4	5	2
Notebooks	5	6	10
Candy	12	5	10
Total Price	$           114.00	$         132.00	$           76.00
Less Discount
Final Sale Price
Total Sales
Average Sale
Lowest Sale
Highest sale

I need help with the following equations

Less Discount

Final Sale Price

Total Sales

Average Sale

Lowest Sale

Highest sale

Answer 1

Regular prices			Applicable Discount
Book Bags	$40		$0	$50.99	4%
Sweatshirts	$20		$51.00	$60.99	5%
Magazines	$3		$61.00	$70.99	6%
Notebooks	$2		$71.00	$80.99	7%
Candy	$1		$81.00	$90.99	8%
			$91.00	$100.99	9%
			$101.00	more	10%
Book Bags	1	2	1
Sweatshirts	2	1	0
Magazines	4	5	2
Notebooks	5	6	10
Candy	12	5	10
Total Price	$         114.00	$       132.00	$         76.00
Less Discount	$           10.26	$         11.88	$          5.32
Final Sale Price	$         103.74	$       120.12	$         70.68
Total Sales	$         294.54
Average Sale	$           98.18
Lowest Sale	$           70.68
Highest sale	$         120.12

Excel Functions: