excel
Jvaldez9
AEEC425Homework7-Fall2024SV.xlsxInstruction
Instruction: In this homoework, you will complete balance sheet (in question 1), income statement (in question 2) and cash flow statement (in question 3) using the provided information.
BalanceSheet_Work
| BALANCE SHEET FOR REGMI FAMILY FARM | ||||||||||||||||||||
| As of December, 2023 | ||||||||||||||||||||
| ASSETS | Jan 1 | Dec 31 | CHANGE | LIABILITIES AND OWNER EQUITY | Jan 1 | Dec 31 | CHANGE | |||||||||||||
| Cash | Accounts Payable | |||||||||||||||||||
| Marketable Securities | Income & Social Security Taxes Payable | |||||||||||||||||||
| Accounts Receivable | Accrued Expenses | |||||||||||||||||||
| Fertilizer and Supplies | Current Portion: Deferred Taxes | |||||||||||||||||||
| Investment in Growing Crops | Current Loans Due Within One Year | |||||||||||||||||||
| Crops Held for Sale and Feed | Current Portion of Term Debt | |||||||||||||||||||
| Market Livestock | Accrued Interest | |||||||||||||||||||
| TOTAL CURRENT ASSETS | TOTAL CURRENT LIABILITIES | |||||||||||||||||||
| Breeding Livestock | Noncurrent Portion: Deferred Taxes | |||||||||||||||||||
| Machinery and Equipment | Noncurrent Portion: Intermediate Loans | |||||||||||||||||||
| Buildings | Noncurrent Portion: Long-term Loans | |||||||||||||||||||
| Investments in Cooperatives | TOTAL NONCURRENT LIABILITIES | |||||||||||||||||||
| Land | ||||||||||||||||||||
| TOTAL NONCURRENT ASSETS | TOTAL LIABILITES | |||||||||||||||||||
| Retained Earnings | ||||||||||||||||||||
| Valuation Equity | ||||||||||||||||||||
| Contributed Captial | ||||||||||||||||||||
| TOTAL OWNER EQUITY | ||||||||||||||||||||
| TOTAL ASSETS | TOTAL LIABILITIES AND OWNER EQUITY | |||||||||||||||||||
| 1-Jan-23 | 12-Dec-23 | |||||||||||||||||||
| Investment in Growing Crops | $150,000 | $135,000 | ||||||||||||||||||
| Accounts Payable | $70,000 | $120,000 | ||||||||||||||||||
| Breeding Livestock | $45,000 | $55,000 | ||||||||||||||||||
| Noncurrent portion: Notes Payable | $120,000 | $150,000 | ||||||||||||||||||
| Land | $550,000 | $520,000 | ||||||||||||||||||
| Taxes Payable | $5,600 | $7,700 | ||||||||||||||||||
| Market Livestock | $29,000 | $119,000 | ||||||||||||||||||
| Current Portion: Deferred Taxes | $19,000 | $39,000 | ||||||||||||||||||
| Current Portion of Term Debt | $38,000 | $85,000 | ||||||||||||||||||
| Buildings | $69,000 | $95,000 | ||||||||||||||||||
| Noncurrent portion: Deferred Taxes | $70,000 | $85,000 | ||||||||||||||||||
| Retained Earnings | $287,700 | $367,700 | ||||||||||||||||||
| Contributed Capital | $53,660 | $23,850 | ||||||||||||||||||
| Fertilizer and Supplies | $1,000 | $50,000 | ||||||||||||||||||
| Accounts Receivable | $1,500 | $1,500 | ||||||||||||||||||
| Investments in Cooperatives | $2,000 | $2,000 | ||||||||||||||||||
| Cash | $37,000 | $88,000 | ||||||||||||||||||
| Noncurrent portion: Real Estate Debt | $195,000 | $140,000 | ||||||||||||||||||
| Crops held for Sale and Feed | $50,000 | $15,000 | ||||||||||||||||||
| Accrued Interest | $12,000 | $18,000 | ||||||||||||||||||
| Notes due within one year | $67,000 | $97,000 | ||||||||||||||||||
| Marketable Securities | $20,000 | $15,500 | ||||||||||||||||||
| Machinery and Equipment | $200,000 | $240,000 | ||||||||||||||||||
| Accrued Expenses | $50,000 | $25,000 | ||||||||||||||||||
| Valuation Equity | $166,540 | $177,750 | ||||||||||||||||||
Quesiont 1. Complete the balance sheet above using the information below. All items below are either a current asset, non‐current asset, current liability or non‐current liability.
Income Statement_Work
| Table. Income Statement for Regmi Farm (Farm Business Only)-2023 | |||
| Farm Business Receipts | |||
| Crop Cash Sales | $678,000 | ||
| Ending Crop Inventory | |||
| Beginning Crop Inventory | |||
| Accrual Gross Revenue from Crops | |||
| Livestock and Milk Cash Sales | $243,000 | ||
| Ending Livestock Inventory | |||
| Beginning Livestock Inventory | |||
| Accrual Gross Revenue from Livestock and Milk | |||
| Gain/Loss on Sale of Breding Livestocks | 6500 | ||
| Agricultural Program Payments | 28000 | ||
| Crop Insurance Proceeds | 6500 | ||
| Other Farm Income | 0 | ||
| Gross Revenue | |||
| Livestock Purchases | 289,000 | ||
| Cost of Purchased Feed/Grain | 89000 | ||
| Value of Farm Production | |||
| Farm Business Expenses | |||
| Labor Hired | $46,000 | ||
| Repairs | $67,000 | ||
| Seed | $58,000 | ||
| Fertilizer | $51,000 | ||
| Machine Hire | $3,500 | ||
| Veterinarian Expense | $9,500 | ||
| Marketing | $25,000 | ||
| Fuel and Utilies | $40,000 | ||
| Property Tax | $15,500 | ||
| General Farm Insurance | $15,500 | ||
| Cash Rent | $3,500 | ||
| Herbicide and Insecticide | $26,500 | ||
| Miscellaneous | $4,500 | ||
| Total Cash Operating Expenses | |||
| Expense Inventory Adjustment | |||
| Depreciation | $100,000 | ||
| Total Operating Expenses | |||
| Interest | $50,000 | ||
| Total Expenses | |||
| Net Farm Income form Operations | |||
| Gain/Loss on Sale of Capital Assets | $0 | ||
| Net Farm Income | |||
| Unpaid Labor | $50,500 |
Quesiont 2. Complete the income statement in the left filling out the highligthed cells. Please use the information from balance sheet (BalanceSheet_Work) as needed.
Cash Flow Statement_Work
| CASH FLOW PROJECTION FOR OPERATING LOAN DETERMINATION | |||||||||||||||||||
| Annual Estimate | Jan. | Feb. | Mar. | April | May | June | July | Aug. | Sep. | Oct. | Nov. | Dec. | |||||||
| CASH INFLOW ITEMS | |||||||||||||||||||
| Livestock: | |||||||||||||||||||
| Beef | 137,015 | 68,500 | 68,515 | ||||||||||||||||
| Swine | 17,490 | 4,247 | 4,547 | 4,248 | 4,448 | ||||||||||||||
| Dairy | 24,000 | 2,000 | 2,000 | 2,000 | 2,000 | 2,000 | 2,000 | 2,000 | 2,000 | 2,000 | 2,000 | 2,000 | 2,000 | ||||||
| Crops | |||||||||||||||||||
| Wheat, Corn, and Sorghum | 154,697 | 39,674 | 39,674 | 37,674 | 37,675 | ||||||||||||||
| Soybeans | 71,600 | 35,800 | 35,800 | ||||||||||||||||
| Hay and Forage | 10,647 | 5,323 | 5,324 | ||||||||||||||||
| Agricultural Program Payments | 32,000 | 16,000 | 16,000 | ||||||||||||||||
| Crop Insurance Proceeds | 15,592 | 15,592 | |||||||||||||||||
| Miscellaneous Income | 45,586 | 25,000 | 20,586 | ||||||||||||||||
| Capital Asset Sales | 5,521 | 5,521 | |||||||||||||||||
| Off-Farm Income | 18,000 | 1,500 | 1,500 | 1,500 | 1,500 | 1,500 | 1,500 | 1,500 | 1,500 | 1,500 | 1,500 | 1,500 | 1,500 | ||||||
| TOTAL CASH INFLOW | |||||||||||||||||||
| CASH OUTFLOW ITEMS | |||||||||||||||||||
| Feed | 48,000 | 4,000 | 4,000 | 4,000 | 4,000 | 4,000 | 4,000 | 4,000 | 4,000 | 4,000 | 4,000 | 4,000 | 4,000 | ||||||
| Hired Labor | 16,000 | 8,000 | 8,000 | ||||||||||||||||
| Repairs | 30,459 | 900 | 900 | 900 | 4,950 | 2,850 | 2,450 | 2,025 | 2,025 | 3,875 | 2,975 | 1,775 | 4,834 | ||||||
| Seed | 30,337 | 30,337 | |||||||||||||||||
| Fertilizer | 49,500 | 40,000 | 9,500 | ||||||||||||||||
| Machine Hire | 13,189 | 7,000 | 6,189 | ||||||||||||||||
| Veterinarian Expense | 4,791 | 1,550 | 1,550 | 1,691 | |||||||||||||||
| Marketing | 5,930 | 1,482 | 1,482 | 1,483 | 1,483 | ||||||||||||||
| Fuel and Utilities | 31,278 | 4,350 | 14,000 | 5,100 | 7,828 | ||||||||||||||
| Property Tax | 6,242 | 3,121 | 3,121 | ||||||||||||||||
| General Farm Insurance | 5,635 | 2,817 | 2,818 | ||||||||||||||||
| Cash Rent | 22,012 | 22,012 | |||||||||||||||||
| Herbicide and Insecticide | 23,704 | 11,852 | 11,852 | ||||||||||||||||
| Miscellaneous Expense | 16,025 | 1,335 | 1,335 | 1,335 | 1,335 | 1,335 | 1,335 | 1,335 | 1,336 | 1,336 | 1,336 | 1,336 | 1,336 | ||||||
| Interest | 16,403 | 4,100 | 4,101 | 4,101 | 4,101 | ||||||||||||||
| Beef Purchases | 71,299 | 13,399 | 13,400 | 44,500 | |||||||||||||||
| Swine Purchases | 1,804 | 1,804 | |||||||||||||||||
| Dairy Purchases | 1,091 | 1,091 | |||||||||||||||||
| Capital Asset Purchases | 80,000 | 80,000 | |||||||||||||||||
| Family Living Withdrawals | 120,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | ||||||
| Estimated Taxes | 7,719 | 7,719 | |||||||||||||||||
| TOTAL CASH OUTFLOWS | |||||||||||||||||||
| NET CASH FLOW | |||||||||||||||||||
| PROJECTED OPERATING LOAN BALANCE (Operating Loan Carried Over from Last Period = 120,000) | 130000 | ||||||||||||||||||
Quesiont 3. Complete the projected cash (in the right) flow to determine operating loan. You need to calculate numbers in the Column H as well as in Rows 22, 46, and 48. Calculated the projected operating loans in row 51 assuming that operating loan carried over from previous year is $130,000.
Grade
| Questions | Grade | Grade you Earn | Comments | ||
| Questions 1. Balance Sheet | |||||
| Work in Colum J (each correct work in green cells 0.25 points) | 3.75 | ||||
| Work in Colum L (each correct work in green cells worth 0.25 points) | 3.75 | ||||
| Work in Colum N (each correct work in green cells worth 0.25 points) | 3.75 | ||||
| Work in Colum L (each correct work in green cells worth 0.25 points) | 4.5 | ||||
| Work in Colum J (each correct work in green cells worth 0.25 points) | 4.5 | ||||
| Work in Colum L (each correct work in green cells worth 0.25 points) | 4.5 | ||||
| Questions 2. Income Statement | |||||
| Work in C8 | 1 | ||||
| Work in C9 | 1 | ||||
| Work in D10 | 1 | ||||
| Work in C13 | 1 | ||||
| Work in C14 | 1 | ||||
| Work in D15 | 1 | ||||
| Work in D20 | 1 | ||||
| Work in D23 | 1 | ||||
| Work in D39 | 1 | ||||
| Work in D40 | 1 | ||||
| Work in D42 | 1 | ||||
| Work in D44 | 1 | ||||
| Work in D45 | 1 | ||||
| Work in D47 | 1.25 | ||||
| Questions 3. Cash Flow Statement | |||||
| Annual Estimates in H10 through H48 | 2 | ||||
| Total Cash Flow in Row 21 | 2 | ||||
| Total Cash Flow in Row 46 | 2 | ||||
| Net Cash Flow in Row 48 | 2 | ||||
| Projected Loan in Row 50 | 3 | ||||
| Total Grade | 50 | 0 |
