1a)Use the attached information and the Excel template to complete the balance sheet. Note: Keep your Excel file, because you will build on it for other assignments and the project. In the handouts is a pdf of the correct answers. This will help you to know that you are creating it correctly. The important part is to build the Excel workbook. You can then erase numbers in yellow and use it for another situation. In order to complete the balance sheet, you will need to also complete the deferred tax worksheet.

Financial Statement Homework

FINANCIAL SITUATION ON JANUARY 1, 2015

• Cash on hand
• Farm $113,421
• Family $1,840
• Buildings 30,100 (book value) 5,000 (market value)
• House (personal asset) 98,500 (book value) 156,000 (market value)
• Household goods (furniture, etc.) 76,520 (book value) 66,500 (market value)
• Personal liabilities 5,818
• Machinery 78,935 (book value) 199,500 (market value)
• Accounts receivable 11,835
• Livestock
• · Raised breeding livestock 20 head with a base value of $500 per head and a market value of $500 per head
• · Purchased breeding livestock including 2,000 (book value) $4,000 (market value)
• · Mkt. Heifers (1,000 lbs.) 45 head at $68 per cwt. (cwt. Stands for hundred weight)
• · Mkt. Steers (1,150 lbs.) 35 head at $70 per cwt.
• · Feeder steers (850 lbs.) 30 head at $78 per cwt.
• Crops on hand
• · Grain (29,690 bushel @ $2.30 per bushel.)
• · Other Feed 1,945
• Prepaid expenses 3,500
• Supplies 1,850
• Cash invested in growing crops 21,000
• Mortgage on farm
• · Principle due in 2011 11,632
• · Principle after 2011 178,779
• Note on Machinery
• · Principal due in 2011 9,696
• · Principal after 2011 36,646
• Operating Loan 113,934
• Accrued interest
• · Mortgage 6,706
• · Machinery loan 4,860
• · Operating loan 6,547
• Outstanding Bills none
• Land
• 160 acres purchased in 1963 for $200 per acre with a market value of $1,400 per acre
• 235 acres purchased in 1975 for $650 per acre with a market value of $1,250 per acre.
• Accrued property tax on farm 560
• Income tax payable on farm 4,134
• Investment in cooperative 27,650 (book value and market value)
• There is no contributed capital. Capital that was originally contributed has since been withdrawn.
• The tax rate for deferred tax on current assets is 38% and 35% for deferred tax on non-current assets.
• 
  
• DURING THE CALENDAR YEAR 2015 THE FOLLOWING TRANSACTIONS WERE RECORDED IN THE RECORDS OF THE FARMER (assume them to be cash transactions unless noted otherwise)
• Sale of feeder animals 49,624
• Labor Hired 20,000
• Fertilizer and Chemicals 15,033
• Rent for land and Pasture 8,900
• Supplies 720
• Repairs 4,200
• Freight 620
• Cattle Marketing (an expense) 250
• Veterinary and medicine 1,540
• Patronage dividends 765
• Grain Sales 149,644
• Machinery Purchased during the year 18,790
• Government incentive payments (income) 18,978
• Crop insurance Proceeds 50,000
• Mics Cash Expense 2,419
• Seed 18,000
• Fuel and Lube 19,048
• Utilities 29,603
• Custom Hire Expense 12,709
• Crop Insurance Premiums 15,890
• Insurance and taxes 16,797
• Off farm wages 23,600
• New carpet and drapes for home 4,500
• Income taxes paid 4,134 for farm and 2,361 for off farm income
• Interest paid 33,583
• The current portion of term debt indicated on the balance sheet was paid on time.
• A new operating loan for $111,198 was taken out.
• $122,644 was paid on the operating loan. This included paying off the loan on the beginning balance sheet and paying off part of the new loan.
• Breeding livestock was purchased for 2,800
• Feeder livestock was purchased for 6,505
• Feed purchased for 1,694
• There was a net inflow of $945 in personal borrowings.
• 
  
• FINANCIAL SITUATION ON DECEMBER 31, 2015
• Cash on hand
• Farm $101,743
• Family $5,300
• Buildings 26,000 (book value) 5,000 (market value)
• House (personal asset) 98,500 (book value) 162,000 (market value)
• Household goods (furniture, etc.) 76,520 (book value) 64,900 (market value)
• Personal liabilities 6,763
• Machinery 61,422 (book value) 199,500 (market value)
• Accounts receivable 14,300
• Livestock
• · Raised breeding livestock 25 head with a base value of $500 per head and a market value of $400 per head
• · Purchased breeding livestock 3,200 (book value) $6,000 (market value)
• · Mkt. Heifers (1,000 lbs.) 45 head @ $68 / cwt
• · Mkt. Steers (1,150 lbs.) 57 head @ $70 / cwt
• · Feeder steers (850 lbs.) 40 head @ $78 / cwt
• Crops on hand
• · Grain (32,560 bushels @ 2.25 per bu.)
• · Other Feed 3,030
• Prepaid expenses 3500
• Supplies 975
• Cash invested in growing crops 22,500
• Mortgage on farm
• · Principle due in 2012 12,599
• · Principle after 2012 166,180
• Note on Machinery
• · Principal due in 2012 10,860
• · Principal after 2012 25,786
• Operating Loan 102,488
• Accrued interest
• · Mortgage 6,153
• · Machinery loan 3,843
• · Operating loan 4,111
• Outstanding Bills 6,578
• Land
• 160 acres purchased in 1963 for $200 per acre with a market value of $1,400 per acre
• 235 acres purchased in 1975 for $650 per acre with a market value of $1,250 per acre.
• Accrued property tax on farm 568
• Income tax payable on farm 6,894
• Investment in cooperative 27,650 (book value and market value)
• There is no contributed capital. Capital that was originally contributed has since been withdrawn.
• The tax rate for deferred tax on current assets is 38% and 35% for deferred tax on non-current assets.
• b)Use the information attached to create an income statement. Note use your excel file where you created the balance sheet
• DURING THE CALENDAR YEAR 2015 THE FOLLOWING TRANSACTIONS WERE RECORDED IN THE RECORDS OF THE FARMER (assume them to be cash transactions unless noted otherwise)
• Sale of feeder animals 49,624
• Labor Hired 20,000
• Fertilizer and Chemicals 15,033
• Rent for land and Pasture 8,900
• Supplies 720
• Repairs 4,200
• Freight 620
• Cattle Marketing (an expense) 250
• Veterinary and medicine 1,540
• Patronage dividends 765
• Grain Sales 149,644
• Machinery Purchased during the year 18,790
• Government incentive payments (income) 18,978
• Crop insurance Proceeds 50,000
• Mics Cash Expense 2,419
• Seed 18,000
• Fuel and Lube 19,048
• Utilities 29,603
• Custom Hire Expense 12,709
• Crop insurance Premiums 15,890
• Insurance and taxes 16,797
• Off farm wages 23,600
• New carpet and drapes for home 4,500
• Income taxes paid 4,134 for farm and 2,361 for off farm income
• Interest paid 33,583
• The current portion of term debt indicated on the balance sheet was paid on time.
• A new operating loan for $111,198 was taken out.
• $122,644 was paid on the operating loan. This included paying off the loan on the beginning balance sheet and paying off part of the new loan.
• Breeding livestock was purchased for 2,800
• Feeder livestock was purchased for 6,505
• Feed purchased for 1,694
• There was a net inflow of $945 in personal borrowings.
• c)Construct the statement of cash Flows and Statement of Owner Equity using the Excel file from the previous homework where you constructed the Balance Sheet and Income Statement
• d)calculate all the ratios possible for the financial statements created
•       

Transcribed Image Text:

fs template(2015) (3) [Protected View] - Excel Tell me what you want to do Home Insert Page Layout Formulas Data Review View iPROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. fix C7 ES 70 71 File 43 Accrued interest 44 Current portion form debl 45 Accrued lax 16 Income tax payable 47 Deferred lax on current assets 48 Total Current Liabilities 49 50 Non-Current Liabilities. 51 Notes (Due beyond 12 months) 52 Mortgages 54 Deferred lax on non-current assets 55 Total Non-Current Liabilities 56 57 Total Business Liabilities 58 personal liabilities 59 Total Liabilities 72 73 60 61 Owner Equity 62 retained earnings 63 Contributed capital 64 Personal net worth 65 Valuation equity 66 Total Owner Equity 67 68 Total Liabilities & Owner Equity 69 diff 74 75 A Ready H B C Balance sheet Deffered tax income statement. Zur Suche Text hier eingeben D 대 Help E statement of owner equity 11 F Cashflow G H ratio ...+ - Enable Editing 1 4 J K (? L David Reiß M E N C 1. H 0 DEU HF P 09:46 29.09.2020 X Share Q X A +87% Ę fs template(2015) (3) [Protected View] - Excel Tell me what you want to do Home Insert Page Layout Formulas Data Review View iPROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. fix C7 ES 70 71 File 43 Accrued interest 44 Current portion form debl 45 Accrued lax 16 Income tax payable 47 Deferred lax on current assets 48 Total Current Liabilities 49 50 Non-Current Liabilities. 51 Notes (Due beyond 12 months) 52 Mortgages 54 Deferred lax on non-current assets 55 Total Non-Current Liabilities 56 57 Total Business Liabilities 58 personal liabilities 59 Total Liabilities 72 73 60 61 Owner Equity 62 retained earnings 63 Contributed capital 64 Personal net worth 65 Valuation equity 66 Total Owner Equity 67 68 Total Liabilities & Owner Equity 69 diff 74 75 A Ready H B C Balance sheet Deffered tax income statement. Zur Suche Text hier eingeben D 대 Help E statement of owner equity 11 F Cashflow G H ratio ...+ - Enable Editing 1 4 J K (? L David Reiß M E N C 1. H 0 DEU HF P 09:46 29.09.2020 X Share Q X A +87% Ę

Answer rating: 100% (QA)

To create the balance sheet you will need to use the information provided to determine the values of ... View the full answer