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.

fs template(2015) (3) (Protected View) - Excel David Rei File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Share PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing CZ v B C E F H J K 1 M N 0 P A 43 Accrued interest 44 Current portion term debt 45 Accrued Tax 16 Income tax payable 47 Deferred lax on current assols 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 57 Total Business Liabilities 58 personal liabilities 59 Total Liabilities 50 61 Owner Equity 62 retained garnings 63 Contributed capital 54 Personal net worth 65 Valuation equity 68 Total Owner Equity 67 58 Total Liabilities & Owner Equity 69 diff 70 71 72 73 74 75 Balance sheet Deffered tax income statement statement of owner equity Cashflow ratio ... Ready E 1 + 87% 09:46 29.09.2020 HE Zur Suche Text hier eingeben ? DEU ts template(2015) (3) (Protected View] - Excel David Rei Ga File Share Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing E26 fr 31.12.2015 E H H 1 J K L M N 0 R S . w X Y Dezember 31, 2014 Dezember 31, 2015 1 Schedule 13 page 43 2 Defferred tax current assets 3 4 Marketable securitiesmit val 5 less cost or basis 6 Accounts receivable 7 Feeder ivestock mkt. Val 0 less cost of purchases 9 Crops and leed inventory 10 Prepaid exp. 11 supplies 12 Investment growing crops 13 Total current assets to be taxed 15 Accounts payable 16 CCCorts 17 Accrued interest 19 Accrued tax 19 Total current liabilities that could be deducted 20 21 Net current assets that could be taxed 22 Income tax rate 22 Dettered tax on current assets 24 25 Deffered tax on Non-current Assets (Business Only) 26 Dezember 31, 2014 Dezember 31, 2015 27 Cost Market Taxable Gain Cost Market Taxable Gain 28 Rased breed Lysick 29 Pur Brd Lystk 30 Machinery & Equip 31 Investment in cooperatives 32 Buildings and improvements 33 Land 34 35 Total taxable gains on non-current assets 36 Minus capital gains exclusion 37 Minus business loss carry forward 38 Net taxable non-current assets 39 income tax rale 10 Deferred tax on non-current assets Balance sheet Deffered tax income statement statement of owner equity Cashflow ratio ... + Ready + 69 % 09:47 29.09.2020 HE Zur Suche Text hier eingeben ? DEU ts template(2015) (3) (Protected View] - Excel David Rei Ga File Share Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing A4 fr . www w Rinaldi 2. W w Balance sheet Deffered tax income statement statement of owner equity Cashflow ratio ... Ready E 1 + 34% 09:48 HE Zur Suche Text hier eingeben ? DEU 29.09.2020 fs template(2015) (3) (Protected View] - Excel David Rei Ga File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Share PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing A4 fr V F H J K 1 M N P R 4 A B 1 Statement of Owner Equity 2 2 Homework 3 Year ending December 31, 2015 4. 5 Cost 6 Farm 7 Beginning owners equity 8 9 Changes in contributed capital and retained earnings 10 Net Income 11 Withdrawls for family living Market Farm Both 13 Total change in contributed Capital & Retained Earnings 14 15 Changes in Martket valuation 18 Raised breeding livestock 17 Purchased breeding livestock 18 Machinery & Equipment 19 Investment in cooperative 20 Buildings & improvements 21 Land 22 Personal assets 23 Total change in market value 24 Change in Non-current deffered taxes 25 Total change in valuation Equity 28 27 Total Change in owner equity 20 Ending owner equity calculated) 29 End of period owner equity (from BIS) 31 32 33 34 Balance sheet Deffered tax income statement statement of owner equity Cashflow ratio ... Ready E + 82% 09:48 29.09.2020 HE Zur Suche Text hier eingeben ? DEU fs template(2015) (3) (Protected View] - Excel David Rei Ga File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Share PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing C52 fr v F N P R u X 2 AR AR AD AC AB AF AG AH N AN AN AN Homework For the year ending December 31, 2015 Fun : PR 7 Caths OR 10. Lad 12. DECISI ta Parrogade 14 Mantan 15 Cash oudio Tre TER ta Feed 20 Farm 29 24 h 29 CM 27 Bed 28 Feb 30. Cantome 3. CREW 2 Ice and was 15 cadas Coling 20 ITER 35 mm 23 Cash 43 41 Cah Duttons SCH 12 Puding 44 Purch 45 + tra nai PNS serar 99 : Cahulowa Print 5 Opening 90 Cah Equr. Dez. Oyear (Tron's CathFord Vivaro 98 Check trane Balance Sheet 90 DI Balance sheet Deffered tax income statement statement of owner equity Cashflow ratio Ready E + 46% 09:48 29.09.2020 HE Zur Suche Text hier eingeben ? Ka 4 DEU ts template(2015) (3) (Protected View] - Excel David Rei Ga Share File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing D1 fx L M M T AB AE AH A Ratio Analysis 2 putnumbers in low spaces Ralance sheet iemais ! Insane Stainentina Cer TalHE| 2 Interest Cape Valve Unsalder Curren | Totale Carrer Liebe Total La Nal worth 1D TH AY 10 il F puting 19 3. Ratio Analysis muabet 20h 2nd 38 miname before 30 Liquidity 22 Current 30 W Capita CM Maths 2. Salvaney ST DH 5 -- en-1 12 app and Reputy 44 Casament are welcomentan 48 Ceska replacement and debt magis 46 Tam dat seassa AT 40 Tiareisi Chciency ver Coat Market 30 Aut 31 Operating up 2 Deration 6 pero Nummer melon Deffered tax income statement statement of owner equity Cashflow ratio worksheet She ... Ready E + 50% 09:48 29.09.2020 Zur Suche Text hier eingeben ? DEU fs template(2015) (3) (Protected View) - Excel David Rei File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Share PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing CZ v B C E F H J K 1 M N 0 P A 43 Accrued interest 44 Current portion term debt 45 Accrued Tax 16 Income tax payable 47 Deferred lax on current assols 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 57 Total Business Liabilities 58 personal liabilities 59 Total Liabilities 50 61 Owner Equity 62 retained garnings 63 Contributed capital 54 Personal net worth 65 Valuation equity 68 Total Owner Equity 67 58 Total Liabilities & Owner Equity 69 diff 70 71 72 73 74 75 Balance sheet Deffered tax income statement statement of owner equity Cashflow ratio ... Ready E 1 + 87% 09:46 29.09.2020 HE Zur Suche Text hier eingeben ? DEU ts template(2015) (3) (Protected View] - Excel David Rei Ga File Share Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing E26 fr 31.12.2015 E H H 1 J K L M N 0 R S . w X Y Dezember 31, 2014 Dezember 31, 2015 1 Schedule 13 page 43 2 Defferred tax current assets 3 4 Marketable securitiesmit val 5 less cost or basis 6 Accounts receivable 7 Feeder ivestock mkt. Val 0 less cost of purchases 9 Crops and leed inventory 10 Prepaid exp. 11 supplies 12 Investment growing crops 13 Total current assets to be taxed 15 Accounts payable 16 CCCorts 17 Accrued interest 19 Accrued tax 19 Total current liabilities that could be deducted 20 21 Net current assets that could be taxed 22 Income tax rate 22 Dettered tax on current assets 24 25 Deffered tax on Non-current Assets (Business Only) 26 Dezember 31, 2014 Dezember 31, 2015 27 Cost Market Taxable Gain Cost Market Taxable Gain 28 Rased breed Lysick 29 Pur Brd Lystk 30 Machinery & Equip 31 Investment in cooperatives 32 Buildings and improvements 33 Land 34 35 Total taxable gains on non-current assets 36 Minus capital gains exclusion 37 Minus business loss carry forward 38 Net taxable non-current assets 39 income tax rale 10 Deferred tax on non-current assets Balance sheet Deffered tax income statement statement of owner equity Cashflow ratio ... + Ready + 69 % 09:47 29.09.2020 HE Zur Suche Text hier eingeben ? DEU ts template(2015) (3) (Protected View] - Excel David Rei Ga File Share Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing A4 fr . www w Rinaldi 2. W w Balance sheet Deffered tax income statement statement of owner equity Cashflow ratio ... Ready E 1 + 34% 09:48 HE Zur Suche Text hier eingeben ? DEU 29.09.2020 fs template(2015) (3) (Protected View] - Excel David Rei Ga File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Share PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing A4 fr V F H J K 1 M N P R 4 A B 1 Statement of Owner Equity 2 2 Homework 3 Year ending December 31, 2015 4. 5 Cost 6 Farm 7 Beginning owners equity 8 9 Changes in contributed capital and retained earnings 10 Net Income 11 Withdrawls for family living Market Farm Both 13 Total change in contributed Capital & Retained Earnings 14 15 Changes in Martket valuation 18 Raised breeding livestock 17 Purchased breeding livestock 18 Machinery & Equipment 19 Investment in cooperative 20 Buildings & improvements 21 Land 22 Personal assets 23 Total change in market value 24 Change in Non-current deffered taxes 25 Total change in valuation Equity 28 27 Total Change in owner equity 20 Ending owner equity calculated) 29 End of period owner equity (from BIS) 31 32 33 34 Balance sheet Deffered tax income statement statement of owner equity Cashflow ratio ... Ready E + 82% 09:48 29.09.2020 HE Zur Suche Text hier eingeben ? DEU fs template(2015) (3) (Protected View] - Excel David Rei Ga File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Share PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing C52 fr v F N P R u X 2 AR AR AD AC AB AF AG AH N AN AN AN Homework For the year ending December 31, 2015 Fun : PR 7 Caths OR 10. Lad 12. DECISI ta Parrogade 14 Mantan 15 Cash oudio Tre TER ta Feed 20 Farm 29 24 h 29 CM 27 Bed 28 Feb 30. Cantome 3. CREW 2 Ice and was 15 cadas Coling 20 ITER 35 mm 23 Cash 43 41 Cah Duttons SCH 12 Puding 44 Purch 45 + tra nai PNS serar 99 : Cahulowa Print 5 Opening 90 Cah Equr. Dez. Oyear (Tron's CathFord Vivaro 98 Check trane Balance Sheet 90 DI Balance sheet Deffered tax income statement statement of owner equity Cashflow ratio Ready E + 46% 09:48 29.09.2020 HE Zur Suche Text hier eingeben ? Ka 4 DEU ts template(2015) (3) (Protected View] - Excel David Rei Ga Share File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing D1 fx L M M T AB AE AH A Ratio Analysis 2 putnumbers in low spaces Ralance sheet iemais ! Insane Stainentina Cer TalHE| 2 Interest Cape Valve Unsalder Curren | Totale Carrer Liebe Total La Nal worth 1D TH AY 10 il F puting 19 3. Ratio Analysis muabet 20h 2nd 38 miname before 30 Liquidity 22 Current 30 W Capita CM Maths 2. Salvaney ST DH 5 -- en-1 12 app and Reputy 44 Casament are welcomentan 48 Ceska replacement and debt magis 46 Tam dat seassa AT 40 Tiareisi Chciency ver Coat Market 30 Aut 31 Operating up 2 Deration 6 pero Nummer melon Deffered tax income statement statement of owner equity Cashflow ratio worksheet She ... Ready E + 50% 09:48 29.09.2020 Zur Suche Text hier eingeben ? DEU