Question

I have to make an Inventory excel sheet and I'm having some trouble trying to figure out how to calculate the "Balance on Hand." Here are the instructions:

The Inventory scenario is based on a fictitious custom bike builder. Of course, the data given for the scenario is a small sampling of what a shop’s inventory would be. The data represents an end of month quantity and is being used to determine, the balance on hand and which items need to be reorder, if any. In an Excel file named Inventory create three sheets named Inventory, Bikes_Made, and Items_Used. Input the data given here. (You should use the previous year for the date.) In addition to the columns given in the inventory data table, add three more columns on your sheet named Balance on Hand, Value of Inventory, and Reorder.

Create a simple formula to calculate the Value of Inventory and complex formulas using the using the data from the three separate sheets to calculate the balance on hand whether the balance on hand is enough, or it is time to reorder the item. If it is time to reorder the item, the cell should contain the word REORDER and the cell should be filled with the color red. Enter the data listed below.​​​​​​:

INVENTORY: DATE RECEIVED, ITEM, COST, INVOICE (SHEET 1)

- 4/1/19, 12 H Front Brakes, unit cost $3.75, invoice number RG970563

- 4/4/2019, 22 20”R Rims, unit cost $24.00, invoice number ED99123

- 4/12/2019, 75 front reflectors, unit cost $0.50, invoice number JEO1713

- 4/12/2019, 50 rear reflectors, unit cost $0.50, invoice number JEO1713

- 4/21/2019, 55 20”F Rims, unit cost $15.00, invoice number ED99455

- 4/13/2019, 210 32 teeth sprockets, unit cost $14.25, invoice number SC831

- 4/23/2019, 180 36 teeth sprockets, unit cost $28.00, invoice number SC831

- 4/25/2019, 25 white seats, unit cost $8.00, invoice number WT389

BIKES_MADE (SHEET 2):

- MX100: January 12, February 15, March 20, April 24, May 30

- MX2000: January 11, February 10, March 11, April 12, May 14

Number of Items Used on Each Bike (SHEET 3):

Number of Items used on each bike

MX100:	MX2000:
H. Front Breaks - 1	H. Front Breaks - 1
20”R Rims - 1	20”R Rims - 1
Front Reflectors - 1	Front Reflectors - 1
Rear Reflectors - 1	Rear Reflectors - 1
20” F Rims - 1	20” F Rims - 1
32 Teeth Sprockets - 2	32 Teeth Sprockets - 0
White Seats - 1	White Seats - 1
Nobbie Tires - 0	Nobbie Tires - 2
Regular Tires - 2	Regular Tires - 0

Additional Info:

Reorder Point:

- H. Front Breaks 150

- 20”R Rims 250

- Front Reflectors 500

- Rear Reflectors 500

- 20” F Rims 250

- 32 Teeth Sprockets 250

- 36 Teeth Sprockets 250

- White Seats 150

- Nobbie Tires 100

- Regular Tires 100

So, I calculated the Value of Inventory by multiplying the number of items by their unit cost. However, I don't know what to do to figure out the Balance on Hand. Here are some screenshots of what I've done on my excel sheet so far:

Home + Share Formulas % C Insert Page Layout . A. , Font Alignment Data Review View Conditional Formatting is Format as Table Cells Cell Styles 19 Paste Number Editing B10x fx Reorder 22 1 Date Received Item 2 4/1/2019 H Front Brakes 3 4/4/2019 20"R Rims 4 4/12/2019 Front Reflectors 5 4/12/2019 Rear Reflectors 6 4/21/2019 20"F Rims 7 4/13/2019 32 Teeth Sprockets 8 4/23/2019 36 Teeth Sprockets 9 4/25/2019 White Seats Number of Items Unit Cost 12 $3.75 $24.00 $0.50 $0.50 $15.00 210 $14.25 180 $28.00 $8.00 Invoice Number RG970563 ED99123 JE01713 JE01713 ED99455 SC831 SC831 WT389 Balance on Hand Value of Inventory $45.00 $528.00 $37.50 $25.00 $825.00 $2,992.50 $5,040.00 $200.00 25 Inventory Bikes_Made Items Used +

Answer 1

we can value value our inventory as per AS-2. According to AS-2 , valuation of inventory is done by different methods like weighted average method and FIFO method(preferable).

According to AS-2 , inventory is valued at COST or NRV(net realisable value) whichever is lower. This is applicable in case of finished goods and work in progress.

If we have to value the inventory of RAW MATERIALS,we have to check that our finished product is giving us profit or not.

ILLUSTRATION:-suppose we have a sweets shop, we are selling BARFI(a square shape sweet), and barfi is made from MILK,if

1.) barfi is not giving us a loss,we should record the milk at COST.[selling price of FG is more or = to total cost]

2.) barfi is giving us a loss,we should record the milk at NRV.[selling price of FG is less than total cost]

With the help of this illustration we can easily record the inventory.