Preparation Tasks: One person in each group needs to do this; the other members support him/her.
- Locate the file Sample Inventory.xlsx on S:\Handout\
- Drag and drop file into Google Drive. Note: You need to turn on conversion to Google Docs. If it is uploaded correctly, it will show a green icon of a box. If it is uploaded incorrectly, it will show a green X.
- Open the converted file on Google Drive. Note: If it is uploaded incorrectly, you will not be able to edit the file.
- Modify the spreadsheet to fit the needs of your store. Read criteria for more information.
Group Tasks: Each group member needs to follow these instructions and participate.
- Do NOT modify Total Cost, Revenue Sold at Retail Price, Revenue Sold at Sale Price, Total Revenue and Profit. These are formulas and their values automatically update. If you remove or modify these cells, you will need to put the formulas back in later (which is extra, unnecessary work).
- Complete the rest of the table in the following order:
- Item: Select 8-12 items that your store sells (4 items per member). Items of different sizes or colors are considered different items.
- Retail Price: Go online to check how much other stores are selling the same item (or similar item) for. Round to the nearest 25 cents (value ends with either .00, .25, .50 or .75).
- Sale Price: Less than the retail (regular) price. It can be either greater or lesser than the cost below. Round to the nearest 25 cents.
- Unit Cost: Use these formulas based on average markups (Australia Bureau of Statistics, 2012). Round up costs to the nearest 25 cents (value ends with either 0 or 5).
- Clothes and shoes: divide unit price by 2.5 to estimate unit cost.
- Electrical and electronic goods: divide by 2.
- Furniture: divide by 1.75.
- Books newspapers and magazines: divide by 1.5
- Fresh food: divide by 1.5
- Restaurants and bakeries: divide by 4.
- DVDs and music : divide by 1.5
- Manufactured products: divide by 2.
- Others (not listed): divide by 1.65.
Note: This is not typically how you find the unit cost. In real-life situations, you need to spend money to make money. The amount that you spend per unit is the unit cost. Buying in bulk and building strong connections distributors are ways to reduce costs.
- Quantity Ordered: The number of items needed, assuming you are only selling to your classmates in a 40-minute time frame. Do not over estimate–any items not sold will become losses! Better to sell and earn less than to have losses.
- Only if you modified in step 1. Total Cost, Revenue at Retail Price, Revenue at Sale Price, Total Revenue and Net Profit: Fill down the formula from above. If you deleted accidentally or purposely them, you need copy and paste the formulas from the original template.
- Quantity Sold at Retail Price and Quantity Sold at Sale Price: Keep the rows blank. You will complete this row on Grand Opening.
Evaluation: The assignment is marked holistically out of 10. The teacher will examine whether the template is completely filled in, whether the items have good descriptions, whether the prices and quantities ordered are reasonable, and whether the formulas are correctly referenced.