Step-By-Step Instructions
Sales/Marketing Beginning Inventory Calculations
Day 1
Teacher gives printout of this week's Department Templates and blank
project logs, for each employee in Sales/Marketing
Departments. to CEO. CEO gives material to VP of
Sales/Marketing/Advertising. VP distributes the workload among
department employees. Each employee fills in their own log with
assignments given by the VP.
Meet with department staff to inform them about project.
Go over time line for first half of the project - Beginning Inventory calculation
Printout your own companiy's products page or use Sales Catalog
Printout the Beginning Inventory Calculation Spreadsheet
Determine cost price of each item and write it down next to the item
Multiply the retail price times the cost of goods sold percentage to determine the cost of each item.
If you do not know the percentage of cost of goods sold, ask the accounting department for this percentage
Printer
Friendly Version for the whole week
Sales/Marketing Beginning Inventory Calculations
Day 2
Look over spreadsheet headings
Begin to create your own spreadsheet to calculate beginning inventory
Modify headings for your product line
Check template formulas and determine if they will work for your products
Formulas are quite simple. If you have different sizes, colors or models, you need a formula to add up the total number to be purchased
For example, find the first box where you want to begin to add. Let's say it is C3.
If box M3 is the last one, then the formula to type would be
=SUM(C3:M3)
Type this formula at coordinate N3
Copy and paste this formula going down the page far enough to cover all items in your inventory .
The next formula needed is to calculate total value.
Total value is determined by multiplying total purchases by the cost price of each item
If the Total Purchases for this item resides at H6 and the cost price is located at I6 then the formula would be
=H6*I6
Type this formula at J6
Copy and paste the formula vertically down the page far enough to cover all of your products
The last formula needed is to total the entire inventory.
Locate it at the bottom of the column of Total Value
If the first number to be added is located at J6 and the last item in that column is located at J39 then the formula would read
=SUM(J6:J39)
Key in the formula at J40
Save the template and send a copy to administration for approval
Sales/Marketing Beginning Inventory Calculatons
Day 3
Decide on how you want to format the cells. Many of them contain numbers
Select cells by dragging the mouse over the cells affected and format them either in accounting or currency format.
Some cells like the quantites are just numeric and contain no decimal points or dollar signs
Determine your best sellers from your product line. A good business partner in your field would be invaluable
When deciding sizes, remember most people fall in between the small and XXL sizes so order more of the medium and large sizes.
Over the last 20 years or so our population has gotten heavier, so you might want to order more of the larger sizes.
Some styles of clothes are worn in a baggy fashion, so consider this when ordering
Most, if not all retailers, attend trade shows where all the merchandise is featured. Buyers can compare different lines at these shows.
You might want to survey your own class or others to detrmine best sellers
Once determined, order more of the best selling items
At this point you might want to decide which items you would like to feature at open houses, grand openings and trade fairs.
Consider Internet specials and any advertising you may want to run to promote certain products.
Turn in you template to your department head
Sales/Marketing Beginning Inventory Calculations
Day 4
Now it is time to input the quantities for each item
Remember best sellers
Consider Trade Fairs, Open Houses and Grand Openings
Enter larger numbers for the more popular sizes.
Remember the total inventory must equal the cost of goods sold
Your accounting department has this number
. In the sample template I gave you for Wipeout Sports Apparel, that number is $165,810
As you can see in my template, the total beginning inventory number is $165,836.29 - very close
Sales/Marketing Beginning Inventory Calculations
Day 5
Continue entering quantities of items to come up with final beginning inventory amount
Spell check document for errors and make necessary corrections
Printout out a copy of the spreadsheet for your department
Give a copy of the file to your department head and the head of accounting to be included in the business plan if desired.
Fill out your progress logs and turn into your department head.
Perpetual Inventory Spreadsheet Template
Sales/Marketing Perpetual Inventory Week 1
Printer
Friendly Version for the whole week
Load perpetual inventory spreadsheet Perpetual Inventory
This spreadsheet is how we keep track of the inventory
Each product has a few lines that describe the product, show a picture, have amounts for quantity on hand, purchases, sales, cost of item and value of the item
The beginning balance for each product has been entered into the quantity on hand.
A formula is entered for each product that adds the amount on hand with purchases and deducts sales of titems to give a final quantity
The final quantity is multiplied by the cost price to get the total value for this inventory item
The catalog numbers are descriptive as to item and size. For example wbsa-l stands for first women's board shorts, size large.
You can decide catalog numbers for your self. Just keep in mind that each product needs a unique catalog number.
To arrive at the total inventory, just add up the totals of each inventory item total.
After finishing the first inventory item, with all of its formulas, try it out with a few numbers to make certain that it works correctly.
Remove the on hand, sales and purchases numbers before completing the next step
When the first one is done, they copy and paste it for all of the other items
Edit as needed for each item.:Item name, picture, quantity on hand cost price, etc
Turn in you spreadsheet as well as the file at the end of the week
The more catalog items you have the longer this assignment will take
When finished entering all the data make certain that your total inventory value is the same as th beginning inventory calculation
Make a backup of this file before entering numbers for purchases and sales
Remember, each month, you must decide on what items to purchase
The amount must equal what VE sends for Cost of Goods Sold each month
After open houses, grand opening and trade fairs, key in the quantity of each item sold to get the ending inventory number for the accounting department
Give this ending inventory number to the head of Sales/Marketing to forward to accounting.
1. Virtual Enterprise California
- Sales Catalog Workflow
2. Virtual Enterprise California - Product Development, Rubric for Sales
Catalog mail-in competition and Sales Catalog Presentation
Virtual Enterprise California
Janet's Web Site -Helpful
forms-Forms and Templates
|