need help , thank you

SCENARIO: You were recently hired at a commercial vine nursery. When you arrive your boss informs you that they haven't been able to properly cost a vine and therefore has no idea how much inventory should have been expensed through cost of Goods Sold, how much inventory should have been written off and how much inventory should still be in inventory on the Balance Sheet for future shipments. Your boss has given you the spend data by account going back to November 2014. Luckily, they do have the spend by account through June 2018. Your boss has asked you to determine the COGS rates for vines for the 2016, 2017 and 2018 growing years. Using the per vine ($/vine) rate for a vine, you can then calculate what should have been expensed on the P&L as cost of goods sold, what should have been written off on the P&L and what should still be on the balance sheet. You should be able to answer all of those questions on the 'Results' tab on the 'Ag Acct Final Project Data' workbook. Your current balance sheet has an inventory balance of $2,754,648. Students must show work (including formulas) in excel to receive full credit. If you simply populate the answers you will not receive credit for this assignment. REQUIREMENTS: The following will need to be answered correctly to receive full credit. 1. 2016, 2017 & 2018 $/vine cost 2. The allocated dollars that belong in 2016, 2017, 2018 & 2019 3. 2016, 2017 & 2018 Total cost of goods expense 4. 2016, 2017 & 2018 Total write off 5. Current balance sheet value of inventory 6. P&L Write-off Adjustment (if needed) Note that in the excel file all GREEN cells should be populated while GREY cells are not necessary INSTRUCTIONS: First you must understand the product you're trying to cost. Dormant bench graft vines take up to three years from the start of the process until shipment to a grower Year 1 is spent growing larger more mature vines called a motherblock or 'increase block. These blocks look similar to a regular vineyard except for the purpose is not to grow fruit, but rather long tendrils that can be harvested and cut to make new vines. The motherblock will start growing in the spring and can grow long tendrils reaching over 20 ft long by the time the vine goes dormant in November/December. Once the vine has gone dormant crews will enter the field to harvest the wood to bring back to the facility. Depending on the variety each motherblock vine can produce upwards of 225 new smaller 12" vines Grafted vines are made up of two components. There is an approximate 11" rootstock (the part of the vine that will grow roots in the ground) and a 1" scion (the part of the vine that will produce the particular grape desired). The rootstock cuttings are brought into the facility, cut into 11" lengths, disbudded (removing all buds) and dipped in hot water to clean the vines and remove bugs. Scion cuttings are brought in and dipped in hot water as well. Afterwards they are cut into 1" pieces. Crews will then take a rootstock cutting and a scion cutting and graft them together making a complete vine. After the vine is grafted, it is put in a special room that has regulated light, temperature and humidity to help the vine's grafting wound to callous. This process takes approximately two weeks. Afterward the vine is removed and the graft union is dipped in wax to help protect it and prepare it to be outside to start the growing process The vines are then planted outside in dormant blocks. These blocks have been setup and prepped with special fertilizers and drip plastic, Crews will plant approximately 55,000 dormant vines per acre. Typically, dormant vines are planted between March and June. Once planted, crews will make sure to keep them watered, fertilized and weed free during the growing season. This is essentially year 2 of the process Later in the fall, the dormant vine will start to shut down. Starting in December the vine will have gone into dormancy and ready to be pulled out of the ground. Crews will dig up the vines and deliver them to a processing facility to be cleaned, trimmed, and cut. However, not all vines will survive. Some will die and the percentage that live will be expressed as the 'Take Rate'. For example, if you plant 100 vines and the take rate is 65%, then 65 vines out of the 100 vines survived. The vines are then bundled into groups of 25 and placed into a box typically containing 400 vines. The vines are then put into cold storage in preparation for shipment to the grower. This represents year 3. These dormant vines can stay viable in cold storage for approximately 12-18 months. After that the vines are no longer viable and will need to be disposed Use the information below to help you separate out the costs of the dormant vine. Keep in mind to maintain consistency all accounts need to be classified to a crop year and contain 12 months. Once you determine the 12 months that belong to a crop year, the next crop year is simply the next 12 months and so on. It is important to note that costs are only spread over the vines that survive, so pay attention to each years' take rate. Direct Labor: The first labor activity for a given year will start in August and is called "Pre-season general. For example, for a 2016 vine the pre-season general spend would start in August 2014. The next activities would start in November of that same year. These activities would include: Disbudding, scion bud cutting, and planting. Soon after, in December, we'll start to capture expenses for planting, irrigation, tractor, weeding and general/estimating labor. Then, the next year we'll start to see expenses in November for Digging, sorting & packing. For example, for a 2016 vine the digging spending would start in December 2015. Direct Expense: Just like labor, different spend accounts should have different 12 months periods in which we capture cost for a crop year. In the year of planting (2014 for a 2016 vine) we would start with Fertilizer - pre planting in March (2014 for a 2016 vine). As we continue to prep the ground, we'll starting incurring costs for Fumigation in July while Equipment Rental - preseason and water- preseason start in August. We would then see expenses for Irrigation supplies, field preparation, general supplies, land R&M, electric, water, miscellaneous expense, compost, chemical application, gypsum, herbicide and insecticide, starting in November. A few months later you'll see charges for professional services starting in February. A month later in March you should start capturing costs for Equipment R&M, Fertilizer - in season and fungicide. In April you'll also want to start capturing equipment rental. Finally, upon harvest in November, packaging and natural gas will need to be added in. O o O o o Overhead & Other: Land leases should start in January the year before shipment (essentially the growing year). Hence a 2016 vine will incur land lease expense from January 2015 through December 2015. Additionally, the overhead allocation (which luckily is already calculated and expensed monthly) should be assigned to a vine in April the year prior to shipment. Hence, for a 2016 vine, the overhead allocation assigned should include costs from April 2015 through March 2016. Grafting Expense: Grafting expense occurs before planting. Hence for a 2016 vine all grafting expense types should start in November 2014. Relevant Data: 2016 Season 8,765,978 vines planted 62% take rate 4,864,800 vines shipped in 2016 26,616 vines shipped in 2017 543,490 vines disposed in 2017 2017 Season 9,425,345 vines planted 65% take rate o 5,260,870 vines shipped in 2017 554,000 vines shipped in 2018 131,604 vines disposed in 2018 o 180,000 vines in cold storage 2018 Season o 11,546,875 vines planted 52% take rate o 4,250,950 vines shipped in 2018 o 1,503,425 vines in cold storage o 250,000 vines disposed in 2018 2019 Season O All costs considered WIP (Work in process) Inventory o o . o 2016 Crop 2017 2018 2016 Year Total Production Cost Planted Vines Finished Vines Shipped Vines Disposed Vines $/Vine Rate Total P&L Cost of Goods Sold Total Write-off Balance Sheet Inventory 2017 Crop 2017 2018 2016 Year Total Production Cost Planted Vines Finished Vines Shipped Vines Disposed Vines S/Vine Rate Total P&L Cost of Goods Sold Total Write-off Balance Sheet Inventory 2018 Crop 2017 2018 2016 Year Total Production Cost Planted Vines Finished Vines Shipped Vines Disposed Vines $/Vine Rate Total P&L Cost of Goods Sold Total Write-off Balance Sheet Inventory 2019 Crop 2017 2018 Year 2016 Total Production Cost Planted Vines Finished Vines Shipped Vines Disposed Vines $/Vine Rate 2019 2019 2019 2019 Total P&L Cost of Goods Sold Total Write-off Balance Sheet Inventory CURRENT VINE BALANCE SHEET INVENTORY: P&L WRITE-OFF ADJUSTMENT: SCENARIO: You were recently hired at a commercial vine nursery. When you arrive your boss informs you that they haven't been able to properly cost a vine and therefore has no idea how much inventory should have been expensed through cost of Goods Sold, how much inventory should have been written off and how much inventory should still be in inventory on the Balance Sheet for future shipments. Your boss has given you the spend data by account going back to November 2014. Luckily, they do have the spend by account through June 2018. Your boss has asked you to determine the COGS rates for vines for the 2016, 2017 and 2018 growing years. Using the per vine ($/vine) rate for a vine, you can then calculate what should have been expensed on the P&L as cost of goods sold, what should have been written off on the P&L and what should still be on the balance sheet. You should be able to answer all of those questions on the 'Results' tab on the 'Ag Acct Final Project Data' workbook. Your current balance sheet has an inventory balance of $2,754,648. Students must show work (including formulas) in excel to receive full credit. If you simply populate the answers you will not receive credit for this assignment. REQUIREMENTS: The following will need to be answered correctly to receive full credit. 1. 2016, 2017 & 2018 $/vine cost 2. The allocated dollars that belong in 2016, 2017, 2018 & 2019 3. 2016, 2017 & 2018 Total cost of goods expense 4. 2016, 2017 & 2018 Total write off 5. Current balance sheet value of inventory 6. P&L Write-off Adjustment (if needed) Note that in the excel file all GREEN cells should be populated while GREY cells are not necessary INSTRUCTIONS: First you must understand the product you're trying to cost. Dormant bench graft vines take up to three years from the start of the process until shipment to a grower Year 1 is spent growing larger more mature vines called a motherblock or 'increase block. These blocks look similar to a regular vineyard except for the purpose is not to grow fruit, but rather long tendrils that can be harvested and cut to make new vines. The motherblock will start growing in the spring and can grow long tendrils reaching over 20 ft long by the time the vine goes dormant in November/December. Once the vine has gone dormant crews will enter the field to harvest the wood to bring back to the facility. Depending on the variety each motherblock vine can produce upwards of 225 new smaller 12" vines Grafted vines are made up of two components. There is an approximate 11" rootstock (the part of the vine that will grow roots in the ground) and a 1" scion (the part of the vine that will produce the particular grape desired). The rootstock cuttings are brought into the facility, cut into 11" lengths, disbudded (removing all buds) and dipped in hot water to clean the vines and remove bugs. Scion cuttings are brought in and dipped in hot water as well. Afterwards they are cut into 1" pieces. Crews will then take a rootstock cutting and a scion cutting and graft them together making a complete vine. After the vine is grafted, it is put in a special room that has regulated light, temperature and humidity to help the vine's grafting wound to callous. This process takes approximately two weeks. Afterward the vine is removed and the graft union is dipped in wax to help protect it and prepare it to be outside to start the growing process The vines are then planted outside in dormant blocks. These blocks have been setup and prepped with special fertilizers and drip plastic, Crews will plant approximately 55,000 dormant vines per acre. Typically, dormant vines are planted between March and June. Once planted, crews will make sure to keep them watered, fertilized and weed free during the growing season. This is essentially year 2 of the process Later in the fall, the dormant vine will start to shut down. Starting in December the vine will have gone into dormancy and ready to be pulled out of the ground. Crews will dig up the vines and deliver them to a processing facility to be cleaned, trimmed, and cut. However, not all vines will survive. Some will die and the percentage that live will be expressed as the 'Take Rate'. For example, if you plant 100 vines and the take rate is 65%, then 65 vines out of the 100 vines survived. The vines are then bundled into groups of 25 and placed into a box typically containing 400 vines. The vines are then put into cold storage in preparation for shipment to the grower. This represents year 3. These dormant vines can stay viable in cold storage for approximately 12-18 months. After that the vines are no longer viable and will need to be disposed Use the information below to help you separate out the costs of the dormant vine. Keep in mind to maintain consistency all accounts need to be classified to a crop year and contain 12 months. Once you determine the 12 months that belong to a crop year, the next crop year is simply the next 12 months and so on. It is important to note that costs are only spread over the vines that survive, so pay attention to each years' take rate. Direct Labor: The first labor activity for a given year will start in August and is called "Pre-season general. For example, for a 2016 vine the pre-season general spend would start in August 2014. The next activities would start in November of that same year. These activities would include: Disbudding, scion bud cutting, and planting. Soon after, in December, we'll start to capture expenses for planting, irrigation, tractor, weeding and general/estimating labor. Then, the next year we'll start to see expenses in November for Digging, sorting & packing. For example, for a 2016 vine the digging spending would start in December 2015. Direct Expense: Just like labor, different spend accounts should have different 12 months periods in which we capture cost for a crop year. In the year of planting (2014 for a 2016 vine) we would start with Fertilizer - pre planting in March (2014 for a 2016 vine). As we continue to prep the ground, we'll starting incurring costs for Fumigation in July while Equipment Rental - preseason and water- preseason start in August. We would then see expenses for Irrigation supplies, field preparation, general supplies, land R&M, electric, water, miscellaneous expense, compost, chemical application, gypsum, herbicide and insecticide, starting in November. A few months later you'll see charges for professional services starting in February. A month later in March you should start capturing costs for Equipment R&M, Fertilizer - in season and fungicide. In April you'll also want to start capturing equipment rental. Finally, upon harvest in November, packaging and natural gas will need to be added in. O o O o o Overhead & Other: Land leases should start in January the year before shipment (essentially the growing year). Hence a 2016 vine will incur land lease expense from January 2015 through December 2015. Additionally, the overhead allocation (which luckily is already calculated and expensed monthly) should be assigned to a vine in April the year prior to shipment. Hence, for a 2016 vine, the overhead allocation assigned should include costs from April 2015 through March 2016. Grafting Expense: Grafting expense occurs before planting. Hence for a 2016 vine all grafting expense types should start in November 2014. Relevant Data: 2016 Season 8,765,978 vines planted 62% take rate 4,864,800 vines shipped in 2016 26,616 vines shipped in 2017 543,490 vines disposed in 2017 2017 Season 9,425,345 vines planted 65% take rate o 5,260,870 vines shipped in 2017 554,000 vines shipped in 2018 131,604 vines disposed in 2018 o 180,000 vines in cold storage 2018 Season o 11,546,875 vines planted 52% take rate o 4,250,950 vines shipped in 2018 o 1,503,425 vines in cold storage o 250,000 vines disposed in 2018 2019 Season O All costs considered WIP (Work in process) Inventory o o . o 2016 Crop 2017 2018 2016 Year Total Production Cost Planted Vines Finished Vines Shipped Vines Disposed Vines $/Vine Rate Total P&L Cost of Goods Sold Total Write-off Balance Sheet Inventory 2017 Crop 2017 2018 2016 Year Total Production Cost Planted Vines Finished Vines Shipped Vines Disposed Vines S/Vine Rate Total P&L Cost of Goods Sold Total Write-off Balance Sheet Inventory 2018 Crop 2017 2018 2016 Year Total Production Cost Planted Vines Finished Vines Shipped Vines Disposed Vines $/Vine Rate Total P&L Cost of Goods Sold Total Write-off Balance Sheet Inventory 2019 Crop 2017 2018 Year 2016 Total Production Cost Planted Vines Finished Vines Shipped Vines Disposed Vines $/Vine Rate 2019 2019 2019 2019 Total P&L Cost of Goods Sold Total Write-off Balance Sheet Inventory CURRENT VINE BALANCE SHEET INVENTORY: P&L WRITE-OFF ADJUSTMENT