Steps in Determining Beginning Inventory
Creating a Perpetual Inventory System

© Jerry Belch

Overview of Project
  • The objective here is to for the Marketing/Sales Department to determine the quantity of each item in the product line to be included in the beginning inventory and to create an inventory control program utilizing a spreadsheet. so perpetual inventory system is in place. Students should have completed the Sales Catalog Lesson to determine the product line, descriptions and pricing before beginning this lesson In this lesson we will determine how many of each item to buy and determine the beginning inventory for our company. . For purposes of this simulation, we are going to use the product line found in the Web Page Assignment.
    Products Page
  • Print these pages for use in the lesson
  • The spreadsheet used in this lesson is located at
    Beginning Inventory Calculation Template
  • Print out this spreadsheet to be used in this lesson.
  • In the second week we will make a spreadsheet template to keep track of our inventory
  • In this lesson our inventory included 24 items. The more items the longer it will take
    Inventory Spreadsheet Program

  • Tutorial

  • Print out the pages mentioned above so we can see the items in our product line: board shorts, men's and women's, sandals, men's and women's, T-shirts and hoodies.
  • We only have a short period of time for the first week's assignment since the beginning inventory number needs to go to Accounting as part of the business plan and business loan applications.
  • We are going to determine quantity to purchase of each of item in our product line
  • The total of all these items at cost will determine our beginning inventory calculation for business loan and business plan.
  • This beginning inventory part of this assignment should take about 5 hours to complete and the inventory template should take about 5 hours as well.
  • If you have more than 24 items the assignment will take a little longer based on number of items in your product line
  • Our Accounting Department needs a number for beginning inventory. This number appears on the balance sheet in the business plan and is also needed in the business loan.
  • Every merchandising business has available for sale during an accounting period the merchandise on hand at the beginning of the period plus the merchandise purchased during the period.
  • If all these goods were sold during the period, there would be no ending inventory, and cost of goods sold would be equal to the cost of goods available for sale
  • Cost of goods sold is then equal to the cost of goods available for sale minus the ending inventory
  • If we are a new business just starting out, the cost of goods sold number is our beginning inventory number
  • The Acccounting Department calculates the amount by multiplying the breakeven point + additional sales to cover open house times the cost of goods sold percentage for these types of products
  • The amount of cost of goods sold for our product line is 55.27% .
  • 250,000 (Break even point) + 50,000 (additional sales needed for open house and profit) = $300,000
  • $300,000 Times .5527 = $165,810
  • The Marketing Department needs to use this number, to determine how many Hang-Ten, Blue T-shirts size small medium, large, Xl and XXL to order.

    Perpetual Inventory Spreadsheet Template

  • We are also going to create a spreasheet template to keep track of our inventory
  • When completed we will enter all sales and purchses of items into the template.
  • With this perpetual inventory system in place we will now know the value of the ending inventory at all times.
  • This system makes for much more accurate financial statements
  • .

    Materials to Collect

    1. Sales and Marketing Curriculum
    2. Printout the Workflows for Sales and Marketing,Workflows, catalog,product development Workflows

    3. Print out Cathy's Department Template From Janet's Web Site Helpful Forms-Forms, Templates and Letters

    4. Printout VE Progress Logs from Janet's Web Site Helpful Forms-Forms, Templates and Letters

      Teacher's Responsibility

    5. Fill out the Department Template for the Marketing/Sales department. -What do you want them to accomplish each week. These Department Templates appear on the right-hand side of your screen if you choose to use these plans.

    6. Make a copy of the Department Template for the department manager and give the Department Templates to the CEO.

    7. CEO gives out Department Template to each Marketing Department head.

    8. Department head gives out Progress Logs to each employee and from the Department template assigns each employee the tasks for the week.

    9. Employee accomplishes the work assigned and fills in information on the Progress Log.

    10. Completed Progress Logs for each employee are given to the department VP at end of week.

    11. Department heads turn progress logs over to CEO.

    12. CEO turns over progress logs to teacher.

    13. Set up a filing cabinet drawer for these materials, so you won't have to go through this process next year.

    14. Evaluate each week's assignment to see if it is completed. You may need to adjust the amount of time for each week's assignment.

    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