I need Help putting the notes from the word document into the excel spreadsheet
AutoSave OFF os 5- w Lab 6 BS STUDENT - Saved to my Mac Home Insert Draw Design Layout References Mailings Review View Tell me Share o Comments Calibri (Bo... v 14 V A cDde AaBbCcDdEe " AI Aav A X APA AaBbCcDc AaBbCcDdEt AaBbc Paste B I U v ab v ale X Normal V Title 15 No Spacing Dictate Heading 1 V Heading 2 Styles Pane Sensitivity explanations you would like of the balance sheet schedules Note, not all information will be used. Ultimately your goal is to complete a balance sheet for the case farm for the year 2014 Inventory of raised crops intended for sale o 22,750 bushels of corn worth $3.69 6,000 bushels of soybeans worth $10.00 o 263 bushels of wheat valued at $5.00/bu Inventory of raised livestock intended for sale 10 head of feeder cattle weighing 400 lbs each and current price is $1.60 per lb. Raised feed Inventory o 1,600 tons of hay worth $113 per ton Sold $27,518 of livestock to John Smith. He has not paid for it yet. 14 days old. It is due in 30 days. - Cash held at United Savings for farm checking and savings accounts: $1,503 Have prepaid expenses to: ABC Coop for anhydrous fertilizer, 30 tons at $500 per ton ABC Coop for seed, 70 bags for $294.28/bag o John's AgriSupply for 28% liquid fertilizer, 40 tons at $250/ton Purchases on credit with local suppliers include: Argies Diesel shop: $700 ABC Coop: $356 O Farm & Fleet: $748 32 acres of established growing hay ground has $220 of direct expenses per acre Rented the chisel plow to the Jones family for $20,000, which has not been paid for yet. The payment from them is due next month. Page 2 of 6 1340 words English (United States) O Focus E + 90% AutoSave OFF os 5- w Lab 6 BS STUDENT - Saved to my Mac Home Insert Draw Design Layout References Mailings Review View Tell me Share o Comments Calibri (Bo... v 14 V A AaBbCcDdEe AaBbCcDdEe " AI Aav A X APA AaBbCcDc AaBbCcDdEt AaBbc Paste B I U v ab v ale X Normal No Spacing Heading 1 V Title Heading 2 Dictate Styles Pane Sensitivity Credit card balances Venture One for $1,025 208 acres of new growing hay ground has $170 per acre of direct expenses Purchased breeding livestock on hand. These were recently purchased and market values are the same as the purchase price. o 1 Beef Bull that cost $1,500 9 Beef Cows costing $1,800 each o 1 Bred heifer that cost $1,400 o 4 open heifers that cost $600 each There are several buildings and many improvements, but in total the original cost of buildings and improvements was $1,225,643 with accumulated depreciation of $855,268. Based on recent sales of nearby farms, estimated market value today is $824,350. Inventory of raised breeding livestock, base value per head, and current market value information is below. Beginning of Year Base Mrkt Value/Head Value/Head Head Breeding Stock Dairy Calves Head End of Year Base Mrkt Value/Head Value/Head 150 400 10 150 400 10 Dairy Bull 1 800 1,050 1 800 1,050 185 1,200 1,780 199 1,200 1,625 62 1,000 1,175 65 1,000 1,175 Dairy Cows Dairy Bred Heifers Dairy Open Heifers 80 700 800 87 700 800 Land 390.5 acres purchased in 2004 for $2,875 per acre. Now worth $3,433 per acre 50 acres purchased in 2014 for $9,000. Now worth $9,000 per acre Page 3 of 6 1340 words English (United States) Focus E + 90% 100% G2 Mon 12:47 PM E Excel File Edit View Insert Format Tools Data Window Help AutoSave HESU- OFF Lab 6 BS STUDENT a Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments B C D E F H K AJ AK AL AM AN AP 1 End of Year Balance Sheet Schedules For: Whisenant Farms Year Ending: 2014 User input in yellow-shaded areas Values in blue-shaded areas need to be transferred to the balance sheet. There is no automatic transfers. 2 3 4 5 Schedule A: Cash - Checking, Savings, Money Market Accounts, Hedging Accounts Description Balance Use for both Cost & Market Basis 6 7 United Savings 8 9 10 11 12 13 14 15 16 17 Total cost and market value (transfer to BS): Hedging Account Balances Hedging Account Balances Total Hedging Account Balances, cost and market value (transfer to BS): 18 Schedule B: Marketable Securities # of shares or units Acquisition price per share/unit Description Cost Basis BS Use Cost Value 0 Current share/unit price Mrkt Basis BS Use Mrkt Value 19 20 0 0 0 0 0 0 0 21 22 23 24 25 26 27 28 0 0 0 ol 0 0 Total cost and market value (transfer to BS): 0 General Farm & Financial Info BS Schedules Beginning & Ending BS Deferred Taxes + 3 + 100% OCT M 26 0 (tv 4 W 100% G2 Mon 12:47 PM E OFF Excel File Edit View Insert Format Tools Data Window Help AutoSave HESUS = Home Insert Draw Page Layout Formulas Data Review View Tell me Lab 6 BS STUDENT a Share 0 Comments B D E F H K AJ AK AL AM AN AP End of Year Balance Sheet Schedules For: Whisenant Farms 1 2014 Year Ending: 28 29 Schedule C: Accounts, Notes, and Contracts Receivable! Written documentation, oral agreement or some other arrangement? Day in Arrears Amount Due in Amount Due in Description of Accounts, Notes, and Contracts Accrued Interest Amount Due Next the Intermediate Long-term, 30 Receivable Interest Rate Total Balance Due 12 Months term (2-10 years) Beyond 10 Years 31 32 33 34 35 36 37 38 Total Cost and Market Values (transfer to BS): 39 Current Asset Intermediate Fixed Asset Includes anything earned and owed to you, but not yet paid (sales of products, crop insurance /other insurance indemnities, govt. program payments, etc.). 40 Also include any accrued interest owed. Totals are the same for Cost and Market Basis balance sheet. 41 42 Schedule D: Market Inventory for Sale and Raised Feed? - Products (crops, livestock, milk etc.) raised/harvested for sale Products (feed) raised for use as an input 43 - Products (crops, feed, livestock, etc.) purchased for later resale Cost Basis BS Use Market Units Units in Market Value per Value (see Mrkt Basis BS 44 Products raised for sale description Inventory unit footnote) Use Market Value 45 46 47 48 General Farm & Financial Info BS Schedules Beginning & Ending BS Deferred Taxes + 3 + 100% OCT AM 26 0 tvm W Excel File Edit View Insert Format Tools Data Window Help 100% 12 Mon 12:47 PM E AutoSave OFF HESu = Lab 6 BS STUDENT a Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments F . J KAJ AK AL AM AN AP Year Ending: 2014 B D E End of Year Balance Sheet Schedules For: Whisenant Farms 1 Schedule D: Market Inventory for Sale and Raised Feed - Products (crops, livestock, milk etc.) raised/harvested for sale Products (feed) raised for use as an input 43 - Products (crops, feed, livestock, etc.) purchased for later resale Cost Basis BS Use Market Units Units in Market Value per Value (see 44 Products raised for sale description Inventory unit footnote) 45 46 47 48 48 49 50 Mrkt Basis BS Use Market Value 51 52 53 54 Total Crops and Feed Inventory: Lower of Cost or Cost Basis BS Market Value per Use Lower of Cost unit or Mrkt Units in (Market Value Value (see Inventory recommended) footnote) Mrkt Basis BS Use Lower of Cost or Market Value Units description 55 Products raised for use as an input? 56 57 58 59 60 61 62 63 64 Total Market Livestock Inventory: Lower of Costor Cost Basis BS BS Schedules Beginning & Ending BS Deferred Taxes General Farm & Financial Info + 3 + 100% OCT AM 26 0 (tv 4 W 100% 12 Mon 12:47 PM E OFF Excel File Edit View Insert Format Tools Data Window Help AutoSave HES 5 = Home Insert Draw Page Layout Formulas Data Review View Tell me Lab 6 BS STUDENT a Share 0 Comments B D E F G . J KAJ AK AL AM AN AP End of Year Balance Sheet Schedules For: Whisenant Farms Year Ending: 2014 1 or Mrkt Lower of Cost or Cost Basis BS Market Value Use Lower of Cost Mrkt Basis BS per unit or Mrkt Use Lower of Cost Units Units in (Cost Value Value (see 65 Products purchased for later resale description Inventory recommended) footnote] Value 66 67 68 69 70 71 72 Total Milk and/or Other Raised Products Inventory: 73 Total Raised Inventory (transfer to BS) Note, the FFSC refers to "net realizable value", which is market value less selling costs. The term "market value" will be used in the footnotes below and it refers to net realizable value (market value less selling costs). Products raised for the intent and purpose of selling as a source of farm revenues are valued at market value for both cost and market basis balance sheets. The FFSC states that products raised for the intent and purpose of using as an input (e.g., feed) can be valued at market value, but the lower of cost or market value is preferred. Deriving a cost value is difficult record-keeping for a raised product, but using a market value can distort the use of the product as an input and effect financial outcomes accordingly. This spreadsheet system allows the user to use cost or market value, but users should stay consistent from one year to the next. Products purchased for the intent and purpose of later resale should be valued at the lower of cost or market value for the cost and market basis balance sheet. However, the recommendation is to use cost value for both balance sheets. 74 75 76 77 Schedule E: Purchased Inventories (Supplies, Purchased Feed, Prepaid Expenses, and Investment in Growing Crops) Units Units in Cost Basis BS Mrkt Basis BS 78 Supplies in inventory description Inventory S per unit Use Cost Value Use Cost Value 79 80 81 02 General Farm & Financial Info BS Schedules Beginning & Ending BS Deferred Taxes + 3 + 100% OCT AM 26 0 tvm W AutoSave OFF os 5- w Lab 6 BS STUDENT - Saved to my Mac Home Insert Draw Design Layout References Mailings Review View Tell me Share o Comments Calibri (Bo... v 14 V AL cDde AaBbCcDdEe " AI Aav A X APA AaBbCcDc AaBbCcDdEt AaBbc Paste B I U v ab v ale X Normal 19 No Spacing V Heading 1 Title Heading 2 Dictate Styles Pane Sensitivity Machinery and Equipment Tractor 1 purchased in 2012 (remember this is 2014) for $225,000. Depreciation is based on straight-line, zero salvage value and 7 years of useful life. "Blue Book" (market value) today is $180,000. Assume the year of purchase was a full year of depreciation. o Tractor 2 purchased in 2010 for $140,000. Depreciation based on straight-line, zero salvage value and 7 years useful life. "Blue Book" = $75,000. Skid loader purchased in 2011 for $24,000. Depreciation straight-line, $4,000 salvage value and 5 years useful life. Blue Book market value = $15,000 All other machinery and equipment have total purchase cost of $650,000, accumulated depreciation of $475,764 and market value today of $521,325. Loans Non-term and Operating loans Operating line of credit: Current balance of $192,879, accrued interest of $488 and 3.04% interest rate (no information on date received or next payment) Risk line of credit for marketing: 3.04% rate, $37 accrued interest and a balance of $14,649 Term Debt FPP bldg./land Improvements: 3.28% interest, $1,702 accrued interest, principal due beyond this year of $531,061 and principal due this year of $91,918 FPP Machinery Note: principal due beyond this year of $113,363, principal payment this year of $45,664,2.65% interest rate and $145 of accrued interest o Mortgage and Land contracts Just made a payment on both loans so interest is paid to date, that is, no accrued interest. REM: 3.25% interest rate, long-term balance of $1,253,809 and payment this year of $67,268 Land Contract: 2.85% interest rate, long-term balance of $35,361, principal payment this year of $4,655 Accrued Expenses/Liabilities Employer withholding due but not paid: $213 Other assets o Vehicles with cost and market value of $19,500 (Intermediate Asset) Page 4 of 6 1340 words English (United States) Focus E + 90% AutoSave OFF os 5- w Lab 6 BS STUDENT - Saved to my Mac Home Insert Draw Design Layout References Mailings Review View Tell me Share o Comments Calibri (Bo... v 14 V A cDde AaBbCcDdEe " AI Aav A X APA AaBbCcDc AaBbCcDdEt AaBbc Paste B I U v ab v ale X Normal V Title 15 No Spacing Dictate Heading 1 V Heading 2 Styles Pane Sensitivity explanations you would like of the balance sheet schedules Note, not all information will be used. Ultimately your goal is to complete a balance sheet for the case farm for the year 2014 Inventory of raised crops intended for sale o 22,750 bushels of corn worth $3.69 6,000 bushels of soybeans worth $10.00 o 263 bushels of wheat valued at $5.00/bu Inventory of raised livestock intended for sale 10 head of feeder cattle weighing 400 lbs each and current price is $1.60 per lb. Raised feed Inventory o 1,600 tons of hay worth $113 per ton Sold $27,518 of livestock to John Smith. He has not paid for it yet. 14 days old. It is due in 30 days. - Cash held at United Savings for farm checking and savings accounts: $1,503 Have prepaid expenses to: ABC Coop for anhydrous fertilizer, 30 tons at $500 per ton ABC Coop for seed, 70 bags for $294.28/bag o John's AgriSupply for 28% liquid fertilizer, 40 tons at $250/ton Purchases on credit with local suppliers include: Argies Diesel shop: $700 ABC Coop: $356 O Farm & Fleet: $748 32 acres of established growing hay ground has $220 of direct expenses per acre Rented the chisel plow to the Jones family for $20,000, which has not been paid for yet. The payment from them is due next month. Page 2 of 6 1340 words English (United States) O Focus E + 90% AutoSave OFF os 5- w Lab 6 BS STUDENT - Saved to my Mac Home Insert Draw Design Layout References Mailings Review View Tell me Share o Comments Calibri (Bo... v 14 V A AaBbCcDdEe AaBbCcDdEe " AI Aav A X APA AaBbCcDc AaBbCcDdEt AaBbc Paste B I U v ab v ale X Normal No Spacing Heading 1 V Title Heading 2 Dictate Styles Pane Sensitivity Credit card balances Venture One for $1,025 208 acres of new growing hay ground has $170 per acre of direct expenses Purchased breeding livestock on hand. These were recently purchased and market values are the same as the purchase price. o 1 Beef Bull that cost $1,500 9 Beef Cows costing $1,800 each o 1 Bred heifer that cost $1,400 o 4 open heifers that cost $600 each There are several buildings and many improvements, but in total the original cost of buildings and improvements was $1,225,643 with accumulated depreciation of $855,268. Based on recent sales of nearby farms, estimated market value today is $824,350. Inventory of raised breeding livestock, base value per head, and current market value information is below. Beginning of Year Base Mrkt Value/Head Value/Head Head Breeding Stock Dairy Calves Head End of Year Base Mrkt Value/Head Value/Head 150 400 10 150 400 10 Dairy Bull 1 800 1,050 1 800 1,050 185 1,200 1,780 199 1,200 1,625 62 1,000 1,175 65 1,000 1,175 Dairy Cows Dairy Bred Heifers Dairy Open Heifers 80 700 800 87 700 800 Land 390.5 acres purchased in 2004 for $2,875 per acre. Now worth $3,433 per acre 50 acres purchased in 2014 for $9,000. Now worth $9,000 per acre Page 3 of 6 1340 words English (United States) Focus E + 90% 100% G2 Mon 12:47 PM E Excel File Edit View Insert Format Tools Data Window Help AutoSave HESU- OFF Lab 6 BS STUDENT a Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments B C D E F H K AJ AK AL AM AN AP 1 End of Year Balance Sheet Schedules For: Whisenant Farms Year Ending: 2014 User input in yellow-shaded areas Values in blue-shaded areas need to be transferred to the balance sheet. There is no automatic transfers. 2 3 4 5 Schedule A: Cash - Checking, Savings, Money Market Accounts, Hedging Accounts Description Balance Use for both Cost & Market Basis 6 7 United Savings 8 9 10 11 12 13 14 15 16 17 Total cost and market value (transfer to BS): Hedging Account Balances Hedging Account Balances Total Hedging Account Balances, cost and market value (transfer to BS): 18 Schedule B: Marketable Securities # of shares or units Acquisition price per share/unit Description Cost Basis BS Use Cost Value 0 Current share/unit price Mrkt Basis BS Use Mrkt Value 19 20 0 0 0 0 0 0 0 21 22 23 24 25 26 27 28 0 0 0 ol 0 0 Total cost and market value (transfer to BS): 0 General Farm & Financial Info BS Schedules Beginning & Ending BS Deferred Taxes + 3 + 100% OCT M 26 0 (tv 4 W 100% G2 Mon 12:47 PM E OFF Excel File Edit View Insert Format Tools Data Window Help AutoSave HESUS = Home Insert Draw Page Layout Formulas Data Review View Tell me Lab 6 BS STUDENT a Share 0 Comments B D E F H K AJ AK AL AM AN AP End of Year Balance Sheet Schedules For: Whisenant Farms 1 2014 Year Ending: 28 29 Schedule C: Accounts, Notes, and Contracts Receivable! Written documentation, oral agreement or some other arrangement? Day in Arrears Amount Due in Amount Due in Description of Accounts, Notes, and Contracts Accrued Interest Amount Due Next the Intermediate Long-term, 30 Receivable Interest Rate Total Balance Due 12 Months term (2-10 years) Beyond 10 Years 31 32 33 34 35 36 37 38 Total Cost and Market Values (transfer to BS): 39 Current Asset Intermediate Fixed Asset Includes anything earned and owed to you, but not yet paid (sales of products, crop insurance /other insurance indemnities, govt. program payments, etc.). 40 Also include any accrued interest owed. Totals are the same for Cost and Market Basis balance sheet. 41 42 Schedule D: Market Inventory for Sale and Raised Feed? - Products (crops, livestock, milk etc.) raised/harvested for sale Products (feed) raised for use as an input 43 - Products (crops, feed, livestock, etc.) purchased for later resale Cost Basis BS Use Market Units Units in Market Value per Value (see Mrkt Basis BS 44 Products raised for sale description Inventory unit footnote) Use Market Value 45 46 47 48 General Farm & Financial Info BS Schedules Beginning & Ending BS Deferred Taxes + 3 + 100% OCT AM 26 0 tvm W Excel File Edit View Insert Format Tools Data Window Help 100% 12 Mon 12:47 PM E AutoSave OFF HESu = Lab 6 BS STUDENT a Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments F . J KAJ AK AL AM AN AP Year Ending: 2014 B D E End of Year Balance Sheet Schedules For: Whisenant Farms 1 Schedule D: Market Inventory for Sale and Raised Feed - Products (crops, livestock, milk etc.) raised/harvested for sale Products (feed) raised for use as an input 43 - Products (crops, feed, livestock, etc.) purchased for later resale Cost Basis BS Use Market Units Units in Market Value per Value (see 44 Products raised for sale description Inventory unit footnote) 45 46 47 48 48 49 50 Mrkt Basis BS Use Market Value 51 52 53 54 Total Crops and Feed Inventory: Lower of Cost or Cost Basis BS Market Value per Use Lower of Cost unit or Mrkt Units in (Market Value Value (see Inventory recommended) footnote) Mrkt Basis BS Use Lower of Cost or Market Value Units description 55 Products raised for use as an input? 56 57 58 59 60 61 62 63 64 Total Market Livestock Inventory: Lower of Costor Cost Basis BS BS Schedules Beginning & Ending BS Deferred Taxes General Farm & Financial Info + 3 + 100% OCT AM 26 0 (tv 4 W 100% 12 Mon 12:47 PM E OFF Excel File Edit View Insert Format Tools Data Window Help AutoSave HES 5 = Home Insert Draw Page Layout Formulas Data Review View Tell me Lab 6 BS STUDENT a Share 0 Comments B D E F G . J KAJ AK AL AM AN AP End of Year Balance Sheet Schedules For: Whisenant Farms Year Ending: 2014 1 or Mrkt Lower of Cost or Cost Basis BS Market Value Use Lower of Cost Mrkt Basis BS per unit or Mrkt Use Lower of Cost Units Units in (Cost Value Value (see 65 Products purchased for later resale description Inventory recommended) footnote] Value 66 67 68 69 70 71 72 Total Milk and/or Other Raised Products Inventory: 73 Total Raised Inventory (transfer to BS) Note, the FFSC refers to "net realizable value", which is market value less selling costs. The term "market value" will be used in the footnotes below and it refers to net realizable value (market value less selling costs). Products raised for the intent and purpose of selling as a source of farm revenues are valued at market value for both cost and market basis balance sheets. The FFSC states that products raised for the intent and purpose of using as an input (e.g., feed) can be valued at market value, but the lower of cost or market value is preferred. Deriving a cost value is difficult record-keeping for a raised product, but using a market value can distort the use of the product as an input and effect financial outcomes accordingly. This spreadsheet system allows the user to use cost or market value, but users should stay consistent from one year to the next. Products purchased for the intent and purpose of later resale should be valued at the lower of cost or market value for the cost and market basis balance sheet. However, the recommendation is to use cost value for both balance sheets. 74 75 76 77 Schedule E: Purchased Inventories (Supplies, Purchased Feed, Prepaid Expenses, and Investment in Growing Crops) Units Units in Cost Basis BS Mrkt Basis BS 78 Supplies in inventory description Inventory S per unit Use Cost Value Use Cost Value 79 80 81 02 General Farm & Financial Info BS Schedules Beginning & Ending BS Deferred Taxes + 3 + 100% OCT AM 26 0 tvm W AutoSave OFF os 5- w Lab 6 BS STUDENT - Saved to my Mac Home Insert Draw Design Layout References Mailings Review View Tell me Share o Comments Calibri (Bo... v 14 V AL cDde AaBbCcDdEe " AI Aav A X APA AaBbCcDc AaBbCcDdEt AaBbc Paste B I U v ab v ale X Normal 19 No Spacing V Heading 1 Title Heading 2 Dictate Styles Pane Sensitivity Machinery and Equipment Tractor 1 purchased in 2012 (remember this is 2014) for $225,000. Depreciation is based on straight-line, zero salvage value and 7 years of useful life. "Blue Book" (market value) today is $180,000. Assume the year of purchase was a full year of depreciation. o Tractor 2 purchased in 2010 for $140,000. Depreciation based on straight-line, zero salvage value and 7 years useful life. "Blue Book" = $75,000. Skid loader purchased in 2011 for $24,000. Depreciation straight-line, $4,000 salvage value and 5 years useful life. Blue Book market value = $15,000 All other machinery and equipment have total purchase cost of $650,000, accumulated depreciation of $475,764 and market value today of $521,325. Loans Non-term and Operating loans Operating line of credit: Current balance of $192,879, accrued interest of $488 and 3.04% interest rate (no information on date received or next payment) Risk line of credit for marketing: 3.04% rate, $37 accrued interest and a balance of $14,649 Term Debt FPP bldg./land Improvements: 3.28% interest, $1,702 accrued interest, principal due beyond this year of $531,061 and principal due this year of $91,918 FPP Machinery Note: principal due beyond this year of $113,363, principal payment this year of $45,664,2.65% interest rate and $145 of accrued interest o Mortgage and Land contracts Just made a payment on both loans so interest is paid to date, that is, no accrued interest. REM: 3.25% interest rate, long-term balance of $1,253,809 and payment this year of $67,268 Land Contract: 2.85% interest rate, long-term balance of $35,361, principal payment this year of $4,655 Accrued Expenses/Liabilities Employer withholding due but not paid: $213 Other assets o Vehicles with cost and market value of $19,500 (Intermediate Asset) Page 4 of 6 1340 words English (United States) Focus E + 90%
