Hi, I need help with this assignment. Thank you for your help!

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 beable 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 allGREENcells should be populated whileGREYcells are not necessary

INSTRUCTIONS:First you must understand the product you're trying to cost. Dormant bench graft vinestake 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 blockslook 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 theparticulargrape 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 tohelp 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 asthe 'Take Rate'. For example, if you plant 100 vines andthe 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 attentionto each years' take rate.

Direct Labor:The first labor activity for a given year will start in August and is called 'Pre-seasongeneral'. 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 expensesin 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'llstarting 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 professionalservices 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.

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

o8,765,978vinesplanted

o62%takerate

o4,864,800 vines shipped in 2016o26,616 vines shipped in 2017

o543,490 vines disposed in 2017

?2017 Season

o9,425,345vinesplanted

o65%takerate

o5,260,870 vines shipped in 2017o554,000 vines shipped in 2018o131,604 vines disposed in 2018o180,000 vines in cold storage

?2018 Season

o11,546,875vinesplanted

o52%takerate

o4,250,950 vines shipped in 2018o1,503,425vinesincoldstorageo250,000 vines disposed in 2018

?2019 Season

oAll costs considered WIP (Work in process) Inventory

2016 Crop Year 2016 2017 2018 2019 Total Production Cost Planted Vines Finished Vines Shipped Vines Disposed Vines $/Vine Rate Total P&L Cost of Goods Solo Total Write-of Balance Sheet Inventory 2017 Crop Year 2016 2017 2018 2019 Total Production Cos Planted Vine Finished Vines Shipped Vines Disposed Vines $/Vine Rate Total P&L Cost of Goods Sold Total Write-off Balance Sheet Inventory 2018 Crop Year 2016 2017 2018 2019 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 Year 2016 2017 2018 2019 Total Production Cos Planted Vines Finished Vines Shipped Vines Disposed Vines $/Vine Rate Total P&L Cost of Goods Sold Total Write-off Balance Sheet Inventory CURRENT VINE BALANCE SHEET INVENTORY: P&L WRITE-OFF ADJUSTMENT