Question

EXCEL ASSIGNMENT

You are thinking of advertising Microsoft products on a popular TV music program. You will pay 1 price for the first group of ads, but as you buy more ads, the price per ad decreases as shown in the following table...

Ad number	Price per ad
1-5	$12,000
6-10	$11,000
11-20	$10,000
21 or higher	$9,000

For example, if you buy 8 ads, you pay $12,000 for the first 5 ads and $11,000 for each of the next 3 ads. If you buy 14 ads, you pay $12,000 for the first 5 ads, $11,000 for each of the next 5 ads, and $10,000 for each of the last 4 ads.

Build your spreadsheet in a way that when the user enters the number of ads, the total cost will be calculated. (You can build your sheet in any way you want and might need to use multiple formula in multiple steps to calculate the total cost)

HINT: For each range calculate the cost until breakpoint. For example for the range of 6-10 calculate what you have to pay for the first 5 ads. For 11-20 what do you have to pay for the 10 previous ads. You need to build it in a form of table that for each range it uses the cost you got from the previous range plus the cost of the remaining. Once you have that, then you will need to combine a couple of VLOOKUPs to get the final answer.

Answer 1

Here, Please have a look at my output for when the ads are 30

Number of Ads	Cost/ per Ad
"1-5"	12,000
"6-10"	11,000
"11-20"	10,000
"21 - higher"	9,000
Input ad number	30
Total cost	305,000