Yorkville High School Computer Science

Computer Applications I :: Projects :: Mystic Music Emporium

Problem

Your most recent job is working for the Mystic Music Emporium. The store wants you to create a spreadsheet that breaks down the accounts of different customers.

Instructions

1. Type the title, Mystic Music Emporium, in Cell A1. Type Customer Accounts in Cell A2. Be sure to center both titles over ALL your work.
2. Enter the information in the spreadsheet above. Some of the cells will be calculated later on.
3. Use the choose function to determine a customer's discount based on their status. Below is listed what each status code represents.
   
4. Use the following formula to calculate the Service Charge: 2.25% * (Beginning Balance - Payments - Credits - Discount)
5. Use the following formula to calculate the New Balance: (Beginning Balance + Purchases) - Payments - Credits - Discount - Service Charge
6. Calculate totals only for Beginning Balance, Purchases, Payments, Discount, Credits, Service Charge, and New Balance.
7. Use the appropriate function to determine the HIGHEST and LOWEST values of each column except for Customer Number, Customer Name, and Status. Place your answers below your totals. DO NOT include totals when determining these numbers.
8. Format all appropriate numbers in currency style and format the table so it has a professional look.
9. Oh shoot, your boss has just handed you some corrections for your spreadsheet. Please make the following changes:
   1. Alanis Morissette's BEGINNING BALANCE should be $556.20.
   2. Smash Mouth's PAYMENTS formula should be =Purchases * 51.5%.
   3. Van Morris should have a CREDIT of $15.73.
10. Enter the AutoSum of the New Balances and then click the button. If the answer is correct then all your numbers should be correct.

    Type your answer without commas:
    

11. Create a pie chart that displays only the NEW BALANCE column (no totals, highest, or lowest). Be sure that each slice of the pie is labeled by its value. Be sure that your legend is visible and contains each customer's name.
12. Finally, create a line chart that shows the Beginning Balance column on one line and the New Balance column on the second line. Each line should be correctly labeled and the names of each customer should be on the bottom of the graph.