In this assignment, you'll deal with the financial aspects of a fictitious music store. As the store's manager, you wish to create a spreadsheet to determine whether the CD or DVD version of an artist's album will be more profitable. Use Microsoft Excel to lay out the following three questions on a single worksheet in a single workbook. Be sure to clearly label everything and use Master Rule #1 whenever you can.
Question 1 (10 pts.)
The band "Gopher Buckets" has a new CD called We sing, You pay. It's guaranteed to give
the consumer a unique, eclectic flavor that only Gopher Buckets could provide. Really.
Your store will be selling the new album.
For each CD you sell, your store purchases it for an initial cost from the manufacturer and then in turn sells it to the customer. The manufacturer will give you a bulk discount rate if you sell more than a certain number of CDs in any given month. Here's the relevant info:
For each month for an entire year (Jan-Dec), show the number of CDs sold, projected gross income, projected gross expenses, and projected profit. Remember that profit is gross income minus gross expenses. Also determine what your total profit would be for the entire year. Remember, Excel (not you) should be determining if you receive the discount rate each month.
Question 2 (10 pts.)
You will also sell the DVD version of We sing, You pay, which costs more since
it includes 3 bonus tracks not on the CD. This DVD is guaranteed to
make the consumer a Gopher Buckets fan, or the consumer will get
double his/her money back. Unlike Question 1, no
bulk discount applies to the DVD.
Note that your store only needs to pay the initial cost for DVDs that were sold without being returned. The manufacturer will absorb the return cost.
For each month for an entire year (Jan-Dec), show the number of DVDs sold, the number returned, projected gross income, projected gross expenses, and projected profit. Remember that your expenses includes both your initial costs and the money-back guarantee. Also determine what your total profit would be for the entire year.
Question 3 (5 pts.)
Which medium, DVDs or CDs, will produce a better profit over the course of a year?
Put the answer on your spreadsheet. Then, perform a What-if? scenario to
determine by how much we could place the more profitable medium on sale and
still make a better profit than the other medium.
To do this, use Goal Seek to see by how much we could reduce the retail
price of the more profitable medium and still have the total annual profit be
greater than the annual profit of the other medium. What is the best deal we
could give our customers (i.e. the minimum retail price) and still make a bigger
profit than the other medium?
<html>
<head>
<title>
This is my web page title.
</title>
</head>
<body>
Type an advertisement for the new album here.
</body>
</html>
Your advertisement must include at least three different formatting tags (boldface, italics, centering, multiple paragraphs, underlining, etc.) See the Barebones guide for a complete list of them.
When you are finished typing in the above sample, and whatever additional text you want to include, save this file, making sure that the file name has no spaces and ends in .html (that's period, then html). So a file name like fred.html or index.html would suffice. The file name Fred's Webpage.html would not because it has a space between Fred's and Webpage.
You can see your web page with a web browser by going to the File menu, choosing Open, and then selecting the name of your html file.
You should also turn in your spreadsheet on paper. Please print it twice: once with the formulas showing and once with the actual numbers. You can get the formulas to show by going to the Tools menu and choosing Options. Under the View tab, there's a checkbox for Formulas. You will probably want to scale down the spreadsheet using Page Setup so that each printout will fit on a few pages. While you're at Page Setup, please print the row and column headings too (check the Sheet tab.)