Microsoft Office
Commission!
Sales. Cost of Goods. Selling Price. Gross Profits. Net Income. Net Profits. Commission. So many terms to understand we determine the amount a business will earn on items they sell as well as how much a worker can earn for items they sell. For this project, you will be looking at predictions for some items in the Warrior's Warehouse. Please pay careful attention to what these terms means and how to calculate these in the spreadsheet. This will be excellent practice for your assessment...
- Sales - quantity of items or services sold by a business
- Cost of Goods - the amount the items or services cost a business
- Selling Price - the amount a business chooses to sell their items or services for, usually an increase from what they paid for the items
- Gross Profits - profits before expenses or commissions are subtracted
- Net Income - see Net Profits
- Net Profits - the amount of money earned after deducting expenses and commissions
- Commission - the amount paid to an employee for items they sell
|
- Open the Excel “Commission Template” file above. Save this document to your Excel folder as [First Name]’s Warrior’s Warehouse Profit Estimates.
- Look over the spreadsheet data that is provided. What do the #### symbols mean in Columns D and E? Adjust the columns as needed to view hidden text and/or numbers.
- Select the cell range A4:E15. Use the Sort & Filter feature to arrange the items alphabetically by Manager and then by the Store Item column. Note: You will need to use the Custom Sort feature and have two levels. Remember to select all data before sorting!
- Format the spreadsheet heading in A1 to a bold type font and enlarge so it looks like a title. Center this across the columns in Row 1.
- In cell A2, use the DATE function to enter the date. Home Tab > Editing Group > Click on Down Arrow near the SUM function > select More Functions > search for DATE > add information and click OK
- Use the Center Across Columns feature to center the date from cell A2:H2
- In cell F4, enter a formula to subtract the cost of goods from the selling price to determine the Gross Profit. Fill this formula down the column for the other items.
- In order to motivate employees, the Warrior’s Warehouse is contemplating offering a commission to the managers based on the gross profit. Enter a formula in cell G4 to calculate an estimated commission of 5%. Format these figures as numbers with two decimal places.
- Enter a formula in cell H4 to calculate the Net Profit. Your formula should subtract commission from the gross profit for each item. Copy this formula down for the remaining items.
- Save!
- Create a thick red top and bottom border for the column titles in Row 3.
- Select the range A3:H3 and fill this range solid black and change the text to white and bold.
- Select the range A14:H14 and add a thick black bottom border.
- You were just notified that the sweatshirts cannot be included in the commission and profit estimates. Select Row 8 and delete the row from the worksheet.
- In cell C15, enter Totals in bold.
- In cell D15, use the SUM function to add up the cost of goods numbers. Fill this formula across the row.
- Select the range A15:H15 and add a thick black bottom border.
- Format the totals to be bold and in currency format with two decimal places.
- In cell A17, enter a hyperlinked sentence to the Warrior’s Warehouse webpage http://muskegonorway.3dcartstores.com/ Insert Tab > Links Group > Hyperlinks
- Change the page layout to landscape and add a footer that has your name on the left, the date in the center, and your Hour # on the right. Note: Use the date feature so that each time this file is opened the current date will appear.
- Save!
- Create a Clustered Cylinder Horizontal Bar Chart using the Store Item data and the Commissions earned on these items.
- Move the chart so that it is below the table of information. Size so that all information is visible and format as desired, but be sure to include a chart title that includes your first and last name, no legend, and some color to enhance but not so much it distracts from the chart itself.
- Save!
- Insert the Warrior “M” logo (as shown at the top of this page) under the spreadsheet data and size to be less than 2 inches in size.
- Hyperlink the Warrior “M” to the Warrior’s Warehouse website as well.
- Save and upload your finished project to Edmodo!
Time to get started with PBL's...