You are the office administrator for a music store. You have collected sales information for the months of September, October and November for a local high school. This information is on the Excel spreadsheet called Melody Music Sales.xisx.
1. Your goal for this project is to create invoices for each of the three months: September, October and November. The date for each invoice will be the last date of that month. Create an invoice that is attractive and logically organized for your customer. An example is provided for your reference on the next page of this assignment. You can choose to follow it or create your own layout. It must 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, if customers order 5 or more of that item they receive a discount equal to 10% of the Total Purchase value before taxes.
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 an Excel function or feature that you have learned 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 and PST are both 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.
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 the same number of items. Do not delete the extra blank lines that are unused on an invoice.
10. You will also summarize the three invoices for Campbell 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 the last page of this document.You are the office administrator for a music store. You have collected sales information for the months of September, October and November for a local high school. This information is on the Excel spreadsheet called Melody Music Sales.xisx.
1. Your goal for this project is to create invoices for each of the three months: September, October and November. The date for each invoice will be the last date of that month. Create an invoice that is attractive and logically organized for your customer. An example is provided for your reference on the next page of this assignment. You can choose to follow it or create your own layout. It must 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, if customers order 5 or more of that item they receive a discount equal to 10% of the Total Purchase value before taxes.
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 an Excel function or feature that you have learned 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 and PST are both 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.
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 the same number of items. Do not delete the extra blank lines that are unused on an invoice.
10. You will also summarize the three invoices for Campbell 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 the last page of this document.
