80.2K

Verified Solution

Link Copied!

2. Explain how conditional logic is used in this spreadsheot and how it mlabt be done differently. The input in the bex below will not be graded, but mav be reviewed and consldered by your instructor. Sheet gor Weighted Average Cost Analysis Using Excel Completing this activity will belp you leam 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. Eecause the company recelves merchandise from different manufacturers and the cat sweaters will be cammoditized, 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 alyurchase 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 WeightedAverageCostABalysisCaseData-70776axdsx You are to bulld 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: STER gi STEP WAtTTHROUSH 1. To help you pet started, the file already has a list of columns for you to fill out. Row 2 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 nedd to apply conditional logk through If statements! ii. In F3, crute an If statement that wal be equal to 1 if Bs is a purchase and ofherwise iil. In 67 , coute an If statement that will be equal to 1 if B3 is a sale and 0 otherenise. b. Find units on hand: in HB3, oreath an equation that caloulates the updated units on hand: if ez is a purchase, updated units on hard c. Find imentory cost: I. In 13, if Ba is a purchase, the cost of the purchase - CarD3, Otherwise, 0 . tirint: We tise 12 as the unit cost becinuse the amourt in 12 is the starting weighted-arerage cost. d. Weiuhted Average Cost in 23 - Inventoor Total Costuoits on hand. You are to build a spreadsheet that can calculate the new imventory cost and the weighted-average cost, when a new purchase or sale record is added to the table. HINT: STEP-BY-STEP WALTHROUGH 1. To help you got started, the file already has a list of colurns for you to fill out. Row 2 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. Cenditional 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: 1. In E3, create an If statement that will be equal to 1 if 83 is a purchase and equal to -1 if 83 is a sale. if. In F3, ereate an if statement that will be equal to 1 if B3 is a purchase and 0 otherwise. iii. In G3, create an If statement that will be equal to t if 83 is a fale and 0 otherwise. b. Find units on hand: In H3, create an equation that calculates the updated units on hand: if B3 is a purchase, Sh-dated units on hand =12+C3; if 83 is a sale, updated units on hand =42C3. c. Find inventory cost: 1. In 13 , if 83 is a purchase, the cost of the purchase =C3D3, Otherwise, 0 . Hint: We uee 12 as the unit cost because the ampunt in 12 is the startino weighted-average cost. 2. Explain how conditional logic is used in this spreadsheot and how it mlabt be done differently. The input in the bex below will not be graded, but mav be reviewed and consldered by your instructor. Sheet gor Weighted Average Cost Analysis Using Excel Completing this activity will belp you leam 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. Eecause the company recelves merchandise from different manufacturers and the cat sweaters will be cammoditized, 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 alyurchase 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 WeightedAverageCostABalysisCaseData-70776axdsx You are to bulld 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: STER gi STEP WAtTTHROUSH 1. To help you pet started, the file already has a list of columns for you to fill out. Row 2 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 nedd to apply conditional logk through If statements! ii. In F3, crute an If statement that wal be equal to 1 if Bs is a purchase and ofherwise iil. In 67 , coute an If statement that will be equal to 1 if B3 is a sale and 0 otherenise. b. Find units on hand: in HB3, oreath an equation that caloulates the updated units on hand: if ez is a purchase, updated units on hard c. Find imentory cost: I. In 13, if Ba is a purchase, the cost of the purchase - CarD3, Otherwise, 0 . tirint: We tise 12 as the unit cost becinuse the amourt in 12 is the starting weighted-arerage cost. d. Weiuhted Average Cost in 23 - Inventoor Total Costuoits on hand. You are to build a spreadsheet that can calculate the new imventory cost and the weighted-average cost, when a new purchase or sale record is added to the table. HINT: STEP-BY-STEP WALTHROUGH 1. To help you got started, the file already has a list of colurns for you to fill out. Row 2 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. Cenditional 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: 1. In E3, create an If statement that will be equal to 1 if 83 is a purchase and equal to -1 if 83 is a sale. if. In F3, ereate an if statement that will be equal to 1 if B3 is a purchase and 0 otherwise. iii. In G3, create an If statement that will be equal to t if 83 is a fale and 0 otherwise. b. Find units on hand: In H3, create an equation that calculates the updated units on hand: if B3 is a purchase, Sh-dated units on hand =12+C3; if 83 is a sale, updated units on hand =42C3. c. Find inventory cost: 1. In 13 , if 83 is a purchase, the cost of the purchase =C3D3, Otherwise, 0 . Hint: We uee 12 as the unit cost because the ampunt in 12 is the startino weighted-average cost

Answer & Explanation Solved by verified expert

See Answer