1. Open the file Imports.xlsx, then save it as username_Parton Imports in the location specified by your professor.
2. In the Documentation sheet, enter your name and the date on which you began this assignment.
3. ** There is a problem with the Commission column in the Transactions sheet** The sales rep commission rate varies for each sales rep. In column F, Jacob used a function to look up the commission rate for each sales rep, and then multiplied the commission rate by the invoice amount to calculate the commission. Some of these result in an error. Find and fix the problem(s) with the formulas in the Commission column.
4. The products sold by Parton Imports are seasonal, with Summer and Fall considered as 'Peak', while Winter and Spring are considered 'Off-Season'. Enter an appropriate nested formula in Column H to show either 'Peak' or 'Off-Season'.
5. There is a special customs duty which must be noted on all USA orders with an invoice amount less than $15,000. Enter an appropriate nested formula in Column I to show either 'Duty' or 'Exempt', as an indication whether the duty applies to each order.
6. In the Reports worksheet, complete the Sales Rep Analysis report. In the Total Amount and Commission columns, use appropriate functions to calculate the invoice amounts and the total commissions from the Transactions worksheet for each sales rep. In the next column use an appropriate function to count the number of invoices for each sales rep from the Transactions worksheet. Do NOT reference entire columns (e.g. Z:Z).
7. In the Reports worksheet, in the range B17:B19, use the appropriate conditional functions to complete the report. Jacob wants to know how many invoices there are where the invoice amount is greater than the criteria, the total sum of those invoices whose amount is greater than the criteria, and the average value of those invoices whose amount is greater than the criteria. In the following formulas, you will not manually enter the criteria, rather, you must reference the criteria cell.
a) In cell B17, enter a formula which sums the total value of invoices greater than the amount in cell B13
b) In cell B18, enter a formula which calculates the average value of invoices greater than the amount in cell B13
c) In cell B19, enter a formula which counts the number of invoices with a value greater than the amount in cell B13.
8. In cell B13, change the criteria to $7,500. This will affect the results of the conditional formulas entered in the previous step.
9. Go to the ‘Reps’ worksheet. If necessary, move the instructional text box so it does not cover any of the used cells.
10. Using the same approach as in Step #6, enter an appropriate function in column D to calculate the invoice totals from the Transactions worksheet for each sales rep.
11. Parton Imports offers an annual bonus to their sales reps, based upon their years of service. In Column E, enter a lookup function which calculates the annual bonus amount for each rep. You will need to first create a lookup table in J4:K7 in order for your loyalty bonus formula to work. The following information can be entered in the lookup table. It is shown below in conversational English, but must be entered in the lookup table in a manner which is understood by Excel.
|
Loyalty Bonus |
|
|
Years of Service |
Bonus Amount |
|
>=0 and <3 |
$0 |
|
>=3 and <5 |
$500 |
|
>=5 and <10 |
$750 |
|
>=10 |
$1,000 |
12. In Column F, use a function which will accommodate multiple IF conditions to determine the rating of each of the reps, based on the details shown in the instructional text box on the 'Reps' worksheet. Copy to the rest of the cells in Column F.
13. In the Reps worksheet, cell A10, Jacob wants the user to be able to input a rep’s name only if the name appears in the range above. Any other name will be disallowed. Create a rule in A10 to specify which data will be allowed. Create a popup message that will appear if the user attempts to enter an invalid entry. This message should contain an appropriate message to the user, explaining why they have been stopped and are unable to continue.
14. Enter a rep name in A10.
15. Apply worksheet protection to the 'Reps' worksheet, so that users can select any of the worksheet cells but will be able to modify only the rep name in cell A10. Protect the sheet, but DO NOT apply a password.
16. In the ‘Reps’ worksheet:
a) Add your name in the left section of the footer and insert the file name element in the right section of the footer
b) Use Print Preview to confirm that the header/footer is set properly. (Note: the textbox properties are set so it does not print.) Do not print the worksheet.
(continued on next page)
All PivotTables and charts must use the source data from the 'PartonData' table on the Transactions worksheet.
17. Create your first PivotTable, and place it in cell A5 in the 'PT' worksheet. Name the PivotTable 'pvtCustomer'. Display the sum of the Invoice Amt by Company.
18. Create another PivotTable, and place it in cell D5 in the 'PT' worksheet. Name it 'pvtMonth'. Display the sum of the Invoice Amt per Month. In the same PivotTable, use the Invoice Amt for the second time, and set it to show the average monthly invoice amounts.
19. Create another PivotTable, and place it in cell H5 of the 'PT' worksheet. Name it 'pvtCountry'. Display the sum of the Invoice Amt per Country.
20. Create another PivotTable, and place it in cell K5 of the 'PT' worksheet. Name it 'pvtSeason_Country'. Use the Rows section to display Season, the Columns section to display Country, and the Values section to display the Invoice Amt. Change the values settings to show the percentage that each country contributes per season. Each row will total 100%. Now change the PivotTable settings so that Grand Totals are turned OFF for both rows and columns.
21. Format all the PivotTables EXCEPT THE LAST ONE using the Accounting format (Dollar sign) with zero decimals. Update each value field label with a short intuitive custom name.
22. Create Pivot Charts for all the PivotTables except 'pvtSeason_Country', as per the following instructions.
|
PivotTable |
Chart Type |
Chart Title |
Other |
|
pvtCustomer |
Bar |
Sales by Company |
Axis units = Display Units in 'Thousands'; Zero decimals |
|
pvtMonth |
Column |
Sales by Month |
Format Average series = No fill / No border |
|
pvtCountry |
Pie |
Sales by Country |
Data labels = Category Name & Percentage; 1 decimal; white fill |
|
Remove the legends and field buttons on all charts. |
|||
23. Move each of the Pivot Charts to the 'Dashboard' worksheet. Feel free to resize them if you wish, to make the charts easier to read.
24. In the 'PT' sheet, click in the pvtCustomer PivotTable. Insert a slicer for the Country and the Season fields. Move both slicers to the 'Dashboard' sheet, and arrange them neatly. Connect each of the slicers to the pvtCustomer and pvtMonth PivotTables.
25. Arrange the worksheets in this order:
1) Documentation
2) Transactions
3) Reports
4) Reps
5) PT
6) Dashboard
26. Save the workbook, and then close it.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
