## CSc 050 Assignment 2 - Spreadsheets Part I Due Mon, Sept. 26, 2005

### Goals

• To create a well-designed spreadsheet
• To practice writing spreadsheet formulas
• To practice with absolute vs. relative addressing
• To practice with What if? scenarios
• To create a basic web page

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:

• Your initial cost: \$9.89 per CD
• Your retail price is \$14.99 per CD
• 6.25% discount off your initial cost per CD if you sell more than 300 CDs in any given month (discount applies to that month only)
• Sold 275 CDs in January
• Projected sales indicate a 2% increase in sales monthly (meaning February will be 2% more than January, March will be 2% more than February, etc.)

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.

• Your initial cost: \$13.40 per DVD
• Your retail price: \$19.95 per DVD
• Sold 327 DVDs in January (before returns)
• Projected sales indicate an 8% increase in sales monthly
• You predict 5% of DVDs sold every month will be returned (and will invoke the guarantee above)

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: text and formatting (5 pts.)

Finally, use a simple text editor (like Notepad) to create a basic web page that will advertise the new album. Use the following template to get you started. The words printed in boldface MUST be included. The italicized portion of the text represents the areas where you can create your own text.

<html>
<title>

This is my web page title.
</title>
<body>
</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.

### What to turn in

Turn in electronic copies of your HTML file and your spreadsheet on Blackboard. To do this, please put both files into a new folder, and then compress the folder into a zip file. On a PC, do this by right-clicking on the folder, choosing "Send To" on the resulting menu, and then choosing "Compressed (zipped) Folder". Name the zip file with your name. It is this zipped folder you should upload to Blackboard.

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.)