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. To help you get 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 the following steps: 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. In F3, create an If statement that will be equal to 1 if B3 is a purchase and 0 otherwise. In G3, create an If statement that will be equal to 1 if B3 is a sale and 0 otherwise. 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: Find units on hand: in H3, create an equation that calculates the updated units on hand: if B3 is a purchase, updated units on hand =H2+C3; if B3 is a sale, updated units on hand =H2-C3. Find inventory cost: In I3, if B3 is a purchase, the cost of the purchase =C3*D3. Otherwise, 0. In J3, if B3 is a sale, the cost of the sale =C3*L2. Otherwise, 0. Hint: We use L2 as the unit cost because the amount in L2 is the starting weighted-average cost. In K3, the updated inventory cost =K2+I3-J3. Weighted Average Cost in L3 = Inventory Total Cost/Units on hand. 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 through 10, in the table below. Do not round intermediate calculations. Round your answers to the nearest cent. Inventory Weighted Date Purchase/Sale Total Cost Average Cost 1-Oct Purchase 1-Oct Sale 3-Oct Purchase 4-Oct Sale 4-Oct Sale 4-Oct Purchase 7-Oct Sale 7-Oct Purchase
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.
-
To help you get 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 the following steps:
-
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.
-
In F3, create an If statement that will be equal to 1 if B3 is a purchase and 0 otherwise.
-
In G3, create an If statement that will be equal to 1 if B3 is a sale and 0 otherwise.
-
-
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:
-
Find units on hand: in H3, create an equation that calculates the updated units on hand: if B3 is a purchase, updated units on hand =H2+C3; if B3 is a sale, updated units on hand =H2-C3.
-
Find inventory cost:
-
In I3, if B3 is a purchase, the cost of the purchase =C3*D3. Otherwise, 0.
-
In J3, if B3 is a sale, the cost of the sale =C3*L2. Otherwise, 0.
Hint: We use L2 as the unit cost because the amount in L2 is the starting weighted-average cost.
-
In K3, the updated inventory cost =K2+I3-J3.
-
-
Weighted Average Cost in L3 = Inventory Total Cost/Units on hand.
-
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 through 10, in the table below. Do not round intermediate calculations. Round your answers to the nearest cent.
Inventory Weighted Date Purchase/Sale Total Cost Average Cost 1-Oct Purchase 1-Oct Sale 3-Oct Purchase 4-Oct Sale 4-Oct Sale 4-Oct Purchase 7-Oct Sale 7-Oct Purchase
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 2 images
