Melody Music Supply Invoice Spreadsheet

Task

You are the owner of a small music store called Melody Music Supply.  You have decided to put all your invoices onto a spreadsheet so you can use Excel’s features.  Here is a purchase order from the Arcola High School: 

April 14, 2021

 

1. Create an invoice that is attractive and logically organized for your customers.  An example is provided for your reference on page three of this assignment.  You can choose to follow it or create your own layout.  It will fit on one page, portrait orientation with minimal scaling. 

2. You will manually enter the invoice date, but have the spreadsheet automatically calculate two additional dates:  a) 30 days from the invoice when the total amount is due, and b) an Early Payment date 10 days after the invoice date. 

3. As a small store, you encourage quantity sales by giving your customers discounts.   For each item ordered, (ex. Tama Drum Stick #10) if customers order 5 or more of that item they receive a discount equal to 10% of the Total Purchase value for each item before taxes.  Place this as a new column immediately after the Total Purchase column for each item. 

4. You want to show your customers how much in total they are saving with their discounts. For example, if a customer is getting $30 in Quantity Discounts and your total invoice balance before taxes was $1200, that would be a savings of 2.50%.

5. Early Payment Discount.  Create a formula that calculates 1% discount off of the invoice total (including taxes). A customer can then choose to pay this amount if the total invoice is paid within 10 days of the purchase date.  

6. You want to summarize the total purchases for each product category. You have categorized your products by Category: Band, Drums, General and Guitar.  Create a Summary of Purchases section that will automatically add up the amounts of purchases made for each department.  Use the SUMIF function to create totals by department.  

 

7. Create a pie chart that summarizes the total purchases by category from Part 6.  Place this on the same sheet (see invoice example). 

8. GST is charged at 5% and PST is charged at 5%.  You do not need to show this calculation for each item, but can place it at the bottom of the invoice based on the invoice total. 

Below are two more purchase orders for Arcola High School:

9. You will copy the original worksheet (copy worksheet, not copy/paste!) to create two new worksheets so that all you need to do is change the data.  Notice that these new invoices do not contain all of the items from the first invoice. Remove any items that are not included on the new invoice, but do not delete the extra blank lines that are unused. To remove the blank lines that remain, use a Sort technique that will bring all of the items to the top of the invoice section and then will send the blank lines to the bottom.

 

May 17, 2021

 

 

June 3, 2021

 

10. You will also summarize the three invoices to Arcola High School. Create an Order Summary worksheet as the first worksheet on your file.  Have it total up the Category totals from each invoice, as well as the total values for the discounts received from the three invoices using 3-D Referencing.  An example is given on page 4 of this document. 

 

Essential Assignment Considerations:

• This assignment is designed to challenge your knowledge of creating and using Excel formulas, functions, and utilizing proper cell referencing techniques.  Before you move on to the second and third invoices, ensure that you have considered all of the opportunities to use these techniques on your invoice.
• The invoice should be of a quality that could be used in a business setting, so attention to detail regarding layout, consistency of fonts and proofreading is also important.