Weighted Average Cost Analysis Using Excel Completing this activity will help you learn to: 1. analyze the cost of inventory Items based on the weighted-average method. 2. create a re-usable spreadsheet by using calculations with cell references 3. create spreadsheets that incorporate conditional logic to facilitate calculations Case scenario: You are a newly hired accountant for Sushi Beeber, Inc., a retail company specializing in apparel for small pets. The company would like to expand its apparel line to include sweaters for cats. Sushi Beeber has found several manufacturers that offer competitive prices to ensure cost of goods sold is low. Because the company receives merchandise from different manufacturers and the cat sweaters will be commoditized, the company needs to develop an internal accounting system to keep track of inventory cost. You think that the weighted average cost method may help the company stay up-to-date with the inventory cost as purchase cost changes over time. Your task is to create a spreadsheet that can calculate the inventory cost based on purchases and sales for the month. Required: X Download spreadsheet Weighted AverageCostAnalysis CaseData-8bd3b1.xlsx You are to build a spreadsheet that can calculate the new inventory cost and the weighted-average cost, when a new purchase or sale record is added to the table. HINT: STEP-BY-STEP WALKTHROUGH 1. To help you get started, the file already has a list of columns for you to fill out Row Z has the beginning inventory. Starting with row 3, you need to fill in each field so that you can find the updated inventory total cost and weighted-average cost. To accomplish this task, complete the following steps: a. Conditional logic: Because a new record can be either a "purchase or a "sale", you need to handle these two situations differently; you need to apply conditional logic through If statements: I. In E3, create an If statement that will be equal to 1 if B3 is a purchase and equal to -1 if B3 is a sale. II. In F3, create an If statement that will be equal to 1 if B3 is a purchase and O otherwise. iii. In G3, create an If statement that will be equal to 1 if B3 is a sale and o otherwise. b. Find units on hand in H3, create an equation that calculates the updated units on hand: if 63 is a purchase, updated units on hand =H2+C3; IF B3 is sale, updated units on hand =H2-C3. c. Find inventory cost: 1. In 13, if B3 is a purchase, the cost of the purchase =C3D3. Otherwise, li. In 33, if B3 is a sale, the cost of the sale =03*12. Otherwise, o Hint: We use LZ as the unit cost because the amount in L2 is the starting weighted-average cost. ili. In K3, the updated inventory cost = 2+13-33. d. Weighted Average cost in 13 = Inventory Total Cost/Units on hand. e. Find the inventory total cost and weighted average cost for the month, i.e., all of the records of data. Enter values for the next eight rows, Row 3 throug 10, in the table below. Do not round intermediate calculations. Round your answers to the nearest cent. Inventory Weighted Purchase/Sale Total Cost Average Cost Purchase Date 1-Oct 1-Oct Sale 3-Oct Purchase 4-Oct Sale 4-Oct Sale 4-Oct Purchase 7-Oct Sale 7-Oct Purchase 2. Explain how conditional logic is used in this spreadsheet and how it might be done differently. The input in the box below will not be graded, but may be reviewed and considered by your instructor Done Weighted AverageCostAnalysisCaseData-8bd3b12 Sign Purchase Unit Cost Purchase SUnits Sign for Con Sign for Cost of Purchase of Sales (sale Units (purchase purchase Hand 1. sale -0) (purchase Co Purchase Date Wechsel Avenge Cou Couro Sales Inven Total Cou .1 NO 10,400 130 132 136 138 140 140 141 143 144 150 1-Oct Purchase 1-Oct Sale 3-Oct Purchase 4-Oct Sale 4.Oct Sale Oct Purchase 7-Oct Sale 7-Oct Purchase 8:0ct Purchase 8-Oct Sale 8-Oct Purchase 9.Oct Salc 9-Oct Purchase 9.Oct Sale 9-Oct Sale 10-Oct Sale 10-Oct Purchase 11-Oct Sale 11-Oct Salc 14-Oct Purchase 14-Oct Sale 15-Oct Purchase 15-Oct Sale 16-Oct Purchase 17-Oct Sale 17-Oct Sale 18-Oct Purchase 18-Oct Sale 21-Oct Sale 21-Oct Purchase 22 Oct Sale 23-Oct Sale 24-Oct Purchase 25.Oct Sale 25-Oct Sale 28-Oct Sale 28-Oct Purchase 29-Oct Purchase 30-Oct Purchase 30-Oct Sale 31-Oct Sale 31-Oct Purchase 88888888888888a sa asa3888888888888888 150 150 150 100 148 100 148 149 152 15 100 156 Weighted Average Cost Analysis Using Excel Completing this activity will help you learn to: 1. analyze the cost of inventory Items based on the weighted-average method. 2. create a re-usable spreadsheet by using calculations with cell references 3. create spreadsheets that incorporate conditional logic to facilitate calculations Case scenario: You are a newly hired accountant for Sushi Beeber, Inc., a retail company specializing in apparel for small pets. The company would like to expand its apparel line to include sweaters for cats. Sushi Beeber has found several manufacturers that offer competitive prices to ensure cost of goods sold is low. Because the company receives merchandise from different manufacturers and the cat sweaters will be commoditized, the company needs to develop an internal accounting system to keep track of inventory cost. You think that the weighted average cost method may help the company stay up-to-date with the inventory cost as purchase cost changes over time. Your task is to create a spreadsheet that can calculate the inventory cost based on purchases and sales for the month. Required: X Download spreadsheet Weighted AverageCostAnalysis CaseData-8bd3b1.xlsx You are to build a spreadsheet that can calculate the new inventory cost and the weighted-average cost, when a new purchase or sale record is added to the table. HINT: STEP-BY-STEP WALKTHROUGH 1. To help you get started, the file already has a list of columns for you to fill out Row Z has the beginning inventory. Starting with row 3, you need to fill in each field so that you can find the updated inventory total cost and weighted-average cost. To accomplish this task, complete the following steps: a. Conditional logic: Because a new record can be either a "purchase or a "sale", you need to handle these two situations differently; you need to apply conditional logic through If statements: I. In E3, create an If statement that will be equal to 1 if B3 is a purchase and equal to -1 if B3 is a sale. II. In F3, create an If statement that will be equal to 1 if B3 is a purchase and O otherwise. iii. In G3, create an If statement that will be equal to 1 if B3 is a sale and o otherwise. b. Find units on hand in H3, create an equation that calculates the updated units on hand: if 63 is a purchase, updated units on hand =H2+C3; IF B3 is sale, updated units on hand =H2-C3. c. Find inventory cost: 1. In 13, if B3 is a purchase, the cost of the purchase =C3D3. Otherwise, li. In 33, if B3 is a sale, the cost of the sale =03*12. Otherwise, o Hint: We use LZ as the unit cost because the amount in L2 is the starting weighted-average cost. ili. In K3, the updated inventory cost = 2+13-33. d. Weighted Average cost in 13 = Inventory Total Cost/Units on hand. e. Find the inventory total cost and weighted average cost for the month, i.e., all of the records of data. Enter values for the next eight rows, Row 3 throug 10, in the table below. Do not round intermediate calculations. Round your answers to the nearest cent. Inventory Weighted Purchase/Sale Total Cost Average Cost Purchase Date 1-Oct 1-Oct Sale 3-Oct Purchase 4-Oct Sale 4-Oct Sale 4-Oct Purchase 7-Oct Sale 7-Oct Purchase 2. Explain how conditional logic is used in this spreadsheet and how it might be done differently. The input in the box below will not be graded, but may be reviewed and considered by your instructor Done Weighted AverageCostAnalysisCaseData-8bd3b12 Sign Purchase Unit Cost Purchase SUnits Sign for Con Sign for Cost of Purchase of Sales (sale Units (purchase purchase Hand 1. sale -0) (purchase Co Purchase Date Wechsel Avenge Cou Couro Sales Inven Total Cou .1 NO 10,400 130 132 136 138 140 140 141 143 144 150 1-Oct Purchase 1-Oct Sale 3-Oct Purchase 4-Oct Sale 4.Oct Sale Oct Purchase 7-Oct Sale 7-Oct Purchase 8:0ct Purchase 8-Oct Sale 8-Oct Purchase 9.Oct Salc 9-Oct Purchase 9.Oct Sale 9-Oct Sale 10-Oct Sale 10-Oct Purchase 11-Oct Sale 11-Oct Salc 14-Oct Purchase 14-Oct Sale 15-Oct Purchase 15-Oct Sale 16-Oct Purchase 17-Oct Sale 17-Oct Sale 18-Oct Purchase 18-Oct Sale 21-Oct Sale 21-Oct Purchase 22 Oct Sale 23-Oct Sale 24-Oct Purchase 25.Oct Sale 25-Oct Sale 28-Oct Sale 28-Oct Purchase 29-Oct Purchase 30-Oct Purchase 30-Oct Sale 31-Oct Sale 31-Oct Purchase 88888888888888a sa asa3888888888888888 150 150 150 100 148 100 148 149 152 15 100 156