Worksheet 5: Purchasing Calculations (36 points; Due 5/25) This worksheet requires you to: 1. Read the text that follows! This provides an overview needed to work on the problem sets. 2. Complete the problem sets that follow each text explanation. Most purchasing managers will become more familiar with these processes as they gain experience. This unit will provide an overview and some techniques to use in learning some basic forecasting and purchasing. Purchasing involves several steps in the process of placing an order: Checking the inventory on hand: setting par Forecasting the demand; Determining how much to order Determining inventory value Projecting yields of food Setting Par: Mini- Max Level- used in computerized perpetual Inventory system; aka: Fixed Order Amount (see image below) Some purchases are made on the basis of a building or maintaining an established par. Those par levels are set from observation and documentation of past A par represents the amount of food that is considered to be adequate that will be consistently used or consumed at the most for a period of time. The par level is set is based on knowing that use will continue while the ordering and delivery of the product will take place, and those time frames are known. A daily par represents the minimum amount of the item to have on hand. Coffee can be an example. Ten pounds of coffee a day is the typical maximum use (having 10 on hand is the daily par), and therefore a minimum of 10 pounds/day should be on hand. If an order takes three days, then 30 pounds should be available at order time. The amount that will be ordered will depend upon covering that time and use before it is re-stocked. A minimum use par is the minimally acceptable amount to have on hand for an item at order time; an amount that is on hand for staple foods for the most part. Flour, salt, and a variety of other staples have a given minimum par level that is established based on history. Let's say for flour, you want 200 pounds on hand. Since it comes in 50# bags, orders placed will bring minimum pars up to the level needed to meet the minimum. 10 Maximum level to maintain When the number of units reaches the minimum level, order the amount needed to bring the stock level back to the maximum level. This number to order will always to be the same! In a computerized perpetual system, the computer will indicate when it is time to order. The minimum level covers the usage during the lead time. Minimum level-safetv stock 9 8 7 5 4 3 2. 1 Setting Par Level - in a Fixed Order Period used in Physical Inventory System This is another method for keeping inventory and uses a physical inventory system, aka. Fixed Order Period. Which means that at each order point in time; a physical inventory is taken and an established amount is refill the amount on hand to fixed par level 10 Par level amount to maintain There is no minimum. Orders are placed on a regular time period. Each time an order is placed enough stock is purchased to maintain par level. The quantity you order will vary each time. Amount on hand at inventory For either method, the quantity to order will be influenced by the lead time (days to get an order) and the amount used each day. For example if this is coffee, and it takes two days to get the item, and you use one unit a day, you will need to order extra to compensate for the usage during the lead time. Lead time=2 Daily Use=1.5 2 Use this formula to calculate par level order quantity: (Par Level minus number on hand at Inventory) + (lead time times use/day) Problem Set 1: Estimating Par (7 pts) 1. You are conducting a physical inventory system and placing a weekly order for frozen juice concentrate. Upon taking inventory, you realize that you have the following on hand: Vendor On hand Par Daily Use Juice Type Apple Cider Lead Time 2 Sysco 6 12 2 Orange Sysco 7 15 3 2 Crap Apple Vitality 9 10 1 4 Grape Sysco 10 8 2 2 Prune Vitality 3 10 1 4 How much should you order of each juice? (1 pt) Apple Orange Crap-Apple Grape Prune Is there a problem with any of the par levels? (1 pt) What formula could be devised to make this simple? (1 pt) 9 8 7 6 5 4 3 Methods to Cost of food sold (COGS Formula) We have reviewed that food operations use three methods to calculate their food costs as discussed in lecture, including: Sales: recipe cost x number sold (doesn't account for waste, losses, not following recipes and costs not in the recipe) Invoices: adding all bills for the month (doesn't include inventory) Inventory - Cost of Goods Sold (COGS; calculations are based on having a perpetual inventory and a mechanism to determine the VALUE of that inventory). This is done for individual items, not the entire volume of that inventory The inventory is the value of the merchandise that is in the storerooms, production area, and dining room. There are several methods to calculate the value inventory. Inventory valuation is necessary to calculate COGS. Food Cost Control through Inventory The COGS formula is used to calculate how much was spent on food for a given time period (e.g., a month) based on having an inventory system that allows you to calculate the dollar value of the items that are in the inventory. The basic formula for COGS is: Beginning Inventory + Purchases Total - Ending Inventory = COGS Example: Merlot Wine Inventory for April, 20XX Date Received Issued Balance Unit Cost April 1 2* $18.00 *Beginning Inventory ** Ending Inventory April 2 6 8 20.00 April 5 3 5 Total Order for Month: 22 units April 12 3 2 April 15 10 12 22.00 April 20 9 April 24 3 6 April 28 6 12 19.00 April 30 2 10** Inventory Valuation Methods Whichever method you select the most important thing to remember is to be consistent. Here is a sample inventory of one item, Merlot Wine shown being calculated by each of the four methods. Let's refer to this inventory when discussing the various methods of evaluating the worth of your goods. Can you see that 14 bottles were used in April? Method one: Actual Cost or Specific Item Cost In using this method you would record the price that you paid for each item times the number of products you have on your shelves. From the inventory card on Merlot Wine, you would have 10 items in stock at the end of the month. Let's say you knew that 3 were from the purchase of April 2 and 7 were from the purchase of April 15 3 x $20 = $60 7 x $22= $154 Total 10 value = $214 This method is very time consuming and is usually used with very expensive items like fine wines and liquor. It can be made easier if cost on the item is written on each unit as it is received or there are computerized wands that may be used with a bar code inventory system 3 Method two: First-in. First-out FIFO FIFO is probably the easiest method, especially for manual physical inventories. Just as FIFO is the proper stock rotation in food safety, it calculates value based on the more recent deliveries which are still on hand, as the earlier ones were first out. You can use this method for evaluating your inventory. By using this method the value of your inventory on April 30 would be: 6 x $19 = 4 x $22 = $114 $88 $202 Total 10 value = FIFO produces a higher net income (and higher inventory value) when food prices are increasing (this assumes that prices are always increasing) and a lower net income when cost prices are declining. This is because FIFO allows the earliest costs to become part of the cost of sales first. Method three: Last-in, first-out LIFO This method assumes (on paper only) that the last items received are the first items used. This method is favored in inflationary times since the most recent item costs higher than earlier ones, becomes part of the period or end cost of sales. The higher costs match with the higher revenues. Profit and tax payments are reduced and inventory value is lower than FIFO. So using your Merlot Wine inventory value on April 30: 2 x $18= $36 6 x $20= 120 2 x $22= 44 Total 10 value = $200 Method four: Weighted Average Cost Under this method a new average cost of items in stock is calculated each time a new delivery of that item is made. You would add up all the costs of the same item left in inventory and divide by the number. Before the new delivery on April 3, there were two items on hand at $18 for a total value of $36 and then six new items were received at $20 each for a total of $120. The total value of the eight items = $156. At that point, the weighted average cost of each item is $156/8 = $19.50. You continue that process until then end of the month, getting an average for the inventory on hand and purchased for that month. Weighted Average is calculated as follows: Total received = 24 units (22 bought this month and +2 cases on hand). Total cost for all received = $490. 2 cases @ $18 = 36 6 cases @ 20 = 120 10 cases @ $22 = 220 = 114 6 cases @ $19 Total $490/24 = $20.416 or $20.40/case FIFO LIFO How do the four methods to calculate inventory value compare? Actual Cost $214 Weighted Ave. $202.40 $202 $200 Although the differences do not seem large, remember that this is only one item. You need to calculate your entire inventory! However when a single method is followed consistently the effect on the inventory, cost of sales, and profit when averaged out over a period of time, will be relatively minor. How does this change COGS? Actual Cost FIFO LIFO Weighted Average $36.00 $36.00 $36.00 $36.00 Beg Inventory + Purchases 454.00 454.00 454.00 454.00 = Total Available 490.00 490.00 490.00 490.00 214.00 202.00 200.00 202.40 --Closing Inventory = COGS 276.00 288.00 290.00 287.10 LIFO should be the highest COGS/food cost, but the lowest closing inventory. Worksheet 5: Purchasing Calculations (36 points; Due 5/25) This worksheet requires you to: 1. Read the text that follows! This provides an overview needed to work on the problem sets. 2. Complete the problem sets that follow each text explanation. Most purchasing managers will become more familiar with these processes as they gain experience. This unit will provide an overview and some techniques to use in learning some basic forecasting and purchasing. Purchasing involves several steps in the process of placing an order: Checking the inventory on hand: setting par Forecasting the demand; Determining how much to order Determining inventory value Projecting yields of food Setting Par: Mini- Max Level- used in computerized perpetual Inventory system; aka: Fixed Order Amount (see image below) Some purchases are made on the basis of a building or maintaining an established par. Those par levels are set from observation and documentation of past A par represents the amount of food that is considered to be adequate that will be consistently used or consumed at the most for a period of time. The par level is set is based on knowing that use will continue while the ordering and delivery of the product will take place, and those time frames are known. A daily par represents the minimum amount of the item to have on hand. Coffee can be an example. Ten pounds of coffee a day is the typical maximum use (having 10 on hand is the daily par), and therefore a minimum of 10 pounds/day should be on hand. If an order takes three days, then 30 pounds should be available at order time. The amount that will be ordered will depend upon covering that time and use before it is re-stocked. A minimum use par is the minimally acceptable amount to have on hand for an item at order time; an amount that is on hand for staple foods for the most part. Flour, salt, and a variety of other staples have a given minimum par level that is established based on history. Let's say for flour, you want 200 pounds on hand. Since it comes in 50# bags, orders placed will bring minimum pars up to the level needed to meet the minimum. 10 Maximum level to maintain When the number of units reaches the minimum level, order the amount needed to bring the stock level back to the maximum level. This number to order will always to be the same! In a computerized perpetual system, the computer will indicate when it is time to order. The minimum level covers the usage during the lead time. Minimum level-safetv stock 9 8 7 5 4 3 2. 1 Setting Par Level - in a Fixed Order Period used in Physical Inventory System This is another method for keeping inventory and uses a physical inventory system, aka. Fixed Order Period. Which means that at each order point in time; a physical inventory is taken and an established amount is refill the amount on hand to fixed par level 10 Par level amount to maintain There is no minimum. Orders are placed on a regular time period. Each time an order is placed enough stock is purchased to maintain par level. The quantity you order will vary each time. Amount on hand at inventory For either method, the quantity to order will be influenced by the lead time (days to get an order) and the amount used each day. For example if this is coffee, and it takes two days to get the item, and you use one unit a day, you will need to order extra to compensate for the usage during the lead time. Lead time=2 Daily Use=1.5 2 Use this formula to calculate par level order quantity: (Par Level minus number on hand at Inventory) + (lead time times use/day) Problem Set 1: Estimating Par (7 pts) 1. You are conducting a physical inventory system and placing a weekly order for frozen juice concentrate. Upon taking inventory, you realize that you have the following on hand: Vendor On hand Par Daily Use Juice Type Apple Cider Lead Time 2 Sysco 6 12 2 Orange Sysco 7 15 3 2 Crap Apple Vitality 9 10 1 4 Grape Sysco 10 8 2 2 Prune Vitality 3 10 1 4 How much should you order of each juice? (1 pt) Apple Orange Crap-Apple Grape Prune Is there a problem with any of the par levels? (1 pt) What formula could be devised to make this simple? (1 pt) 9 8 7 6 5 4 3 Methods to Cost of food sold (COGS Formula) We have reviewed that food operations use three methods to calculate their food costs as discussed in lecture, including: Sales: recipe cost x number sold (doesn't account for waste, losses, not following recipes and costs not in the recipe) Invoices: adding all bills for the month (doesn't include inventory) Inventory - Cost of Goods Sold (COGS; calculations are based on having a perpetual inventory and a mechanism to determine the VALUE of that inventory). This is done for individual items, not the entire volume of that inventory The inventory is the value of the merchandise that is in the storerooms, production area, and dining room. There are several methods to calculate the value inventory. Inventory valuation is necessary to calculate COGS. Food Cost Control through Inventory The COGS formula is used to calculate how much was spent on food for a given time period (e.g., a month) based on having an inventory system that allows you to calculate the dollar value of the items that are in the inventory. The basic formula for COGS is: Beginning Inventory + Purchases Total - Ending Inventory = COGS Example: Merlot Wine Inventory for April, 20XX Date Received Issued Balance Unit Cost April 1 2* $18.00 *Beginning Inventory ** Ending Inventory April 2 6 8 20.00 April 5 3 5 Total Order for Month: 22 units April 12 3 2 April 15 10 12 22.00 April 20 9 April 24 3 6 April 28 6 12 19.00 April 30 2 10** Inventory Valuation Methods Whichever method you select the most important thing to remember is to be consistent. Here is a sample inventory of one item, Merlot Wine shown being calculated by each of the four methods. Let's refer to this inventory when discussing the various methods of evaluating the worth of your goods. Can you see that 14 bottles were used in April? Method one: Actual Cost or Specific Item Cost In using this method you would record the price that you paid for each item times the number of products you have on your shelves. From the inventory card on Merlot Wine, you would have 10 items in stock at the end of the month. Let's say you knew that 3 were from the purchase of April 2 and 7 were from the purchase of April 15 3 x $20 = $60 7 x $22= $154 Total 10 value = $214 This method is very time consuming and is usually used with very expensive items like fine wines and liquor. It can be made easier if cost on the item is written on each unit as it is received or there are computerized wands that may be used with a bar code inventory system 3 Method two: First-in. First-out FIFO FIFO is probably the easiest method, especially for manual physical inventories. Just as FIFO is the proper stock rotation in food safety, it calculates value based on the more recent deliveries which are still on hand, as the earlier ones were first out. You can use this method for evaluating your inventory. By using this method the value of your inventory on April 30 would be: 6 x $19 = 4 x $22 = $114 $88 $202 Total 10 value = FIFO produces a higher net income (and higher inventory value) when food prices are increasing (this assumes that prices are always increasing) and a lower net income when cost prices are declining. This is because FIFO allows the earliest costs to become part of the cost of sales first. Method three: Last-in, first-out LIFO This method assumes (on paper only) that the last items received are the first items used. This method is favored in inflationary times since the most recent item costs higher than earlier ones, becomes part of the period or end cost of sales. The higher costs match with the higher revenues. Profit and tax payments are reduced and inventory value is lower than FIFO. So using your Merlot Wine inventory value on April 30: 2 x $18= $36 6 x $20= 120 2 x $22= 44 Total 10 value = $200 Method four: Weighted Average Cost Under this method a new average cost of items in stock is calculated each time a new delivery of that item is made. You would add up all the costs of the same item left in inventory and divide by the number. Before the new delivery on April 3, there were two items on hand at $18 for a total value of $36 and then six new items were received at $20 each for a total of $120. The total value of the eight items = $156. At that point, the weighted average cost of each item is $156/8 = $19.50. You continue that process until then end of the month, getting an average for the inventory on hand and purchased for that month. Weighted Average is calculated as follows: Total received = 24 units (22 bought this month and +2 cases on hand). Total cost for all received = $490. 2 cases @ $18 = 36 6 cases @ 20 = 120 10 cases @ $22 = 220 = 114 6 cases @ $19 Total $490/24 = $20.416 or $20.40/case FIFO LIFO How do the four methods to calculate inventory value compare? Actual Cost $214 Weighted Ave. $202.40 $202 $200 Although the differences do not seem large, remember that this is only one item. You need to calculate your entire inventory! However when a single method is followed consistently the effect on the inventory, cost of sales, and profit when averaged out over a period of time, will be relatively minor. How does this change COGS? Actual Cost FIFO LIFO Weighted Average $36.00 $36.00 $36.00 $36.00 Beg Inventory + Purchases 454.00 454.00 454.00 454.00 = Total Available 490.00 490.00 490.00 490.00 214.00 202.00 200.00 202.40 --Closing Inventory = COGS 276.00 288.00 290.00 287.10 LIFO should be the highest COGS/food cost, but the lowest closing inventory