excel

Instruction

Instruction: In this homoework, you will calcuate financial ratios and analyze performance of a farm business.

Liquidity,Solvency

						BALANCE SHEET FOR REGMI FAMILY FARM
						As of December, 2023
						ASSETS		Jan 1		Dec 31		AVERAGE		LIABILITIES AND OWNER EQUITY		Jan 1		Dec 31		AVERAGE
						Cash		$67,000		$88,000		$77,500		Accounts Payable		$70,000		$120,000		$95,000
						Marketable Securities		$20,000		$15,500		$17,750		Income & Social Security Taxes Payable		$5,600		$7,700		$6,650
						Accounts Receivable		$1,500		$1,500		$1,500		Accrued Expenses		$50,000		$25,000		$37,500
						Fertilizer and Supplies		$1,000		$50,000		$25,500		Current Portion: Deferred Taxes		$19,000		$39,000		$29,000
						Investment in Growing Crops		$150,000		$135,000		$142,500		Current Loans Due Within One Year		$67,000		$97,000		$82,000
						Crops Held for Sale and Feed		$50,000		$15,000		$32,500		Current Portion of Term Debt		$38,000		$85,000		$61,500
						Market Livestock		$29,000		$119,000		$74,000		Accrued Interest		$12,000		$18,000		$15,000
						TOTAL CURRENT ASSETS		$318,500		$424,000		$371,250		TOTAL CURRENT LIABILITIES		$261,600		$391,700		$326,650
						Breeding Livestock		$45,000		$55,000		$50,000		Noncurrent Portion: Deferred Taxes		$70,000		$85,000		$77,500
						Machinery and Equipment		$200,000		$240,000		$220,000		Noncurrent Portion: Intermediate Loans		$120,000		$150,000		$135,000
						Buildings		$59,000		$95,000		$77,000		Noncurrent Portion: Long-term Loans		$185,000		$140,000		$162,500
						Investments in Cooperatives		$2,000		$2,000		$2,000		TOTAL NONCURRENT LIABILITIES		$375,000		$375,000		$375,000
						Land		$550,000		$520,000		$535,000
						TOTAL NONCURRENT ASSETS		$856,000		$912,000		$884,000		TOTAL LIABILITES		$636,600		$766,700		$701,650
														Retained Earnings		$317,700		$367,700
														Valuation Equity		$166,540		$177,750
														Contributed Captial		$53,660		$23,850
														TOTAL OWNER EQUITY		$537,900		$569,300
						TOTAL ASSETS		$1,174,500		$1,336,000		$1,255,250		TOTAL LIABILITIES AND OWNER EQUITY		$1,174,500		$1,336,000		$1,255,250
				1-Jan-23	31-Dec-23	Financial Ratios		Jan1		Dec 31
			Investment in Growing Crops	$150,000	$135,000	Liquidity Ratios
			Accounts Payable	$70,000	$120,000	Current Ratio
			Breeding Livestock	$45,000	$55,000	Quick Ratio
			Noncurrent portion: Notes Payable	$120,000	$150,000	Working Capital
			Land	$550,000	$520,000	Working Capital Ratio
			Taxes Payable	$5,600	$7,700	Solvency Ratios
			Market Livestock	$29,000	$119,000	Debt to Asset Ratio
			Current Portion: Deferred Taxes	$19,000	$39,000	Equity to Asset Ratio
			Current Portion of Term Debt	$38,000	$85,000	Debt to Equity Ratio
			Buildings	$59,000	$95,000
			Noncurrent portion: Deferred Taxes	$70,000	$85,000
			Retained Earnings	$317,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	$67,000	$88,000
			Noncurrent portion: Real Estate Debt	$185,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.1. Calculate the liquidity and solvency ratios below (in the yellow cells) using the information from balance sheet and income statement.

Quesiont 1.2. Based on your calculations above, discuss the famer's liquidity and solvency position in detials (a paragaph of about 5-6 sentences). [Hint: make sure to use Dr. Khol's Benchmarks in your discussion].

Answer 1.2.

Profitability,Efficiency,Repay

		Table. Income Statement for Regmi Farm (Farm Business Only)-2023			Financial Ratios
					Profitability Ratios		As of December, 2020
	Farm Business Receipts				Rate of Return on Farm Assets
	Crop Cash Sales	$678,000			Rate of Return on Farm Equity
	Ending Crop Inventory	$15,000			Operating Profit Margin
	Beginning Crop Inventory	$50,000			Efficienty Ratios
	Accrual Gross Revenue from Crops		$643,000		Asset Turnover Ratio
					Operating Expense Ratio
	Livestock and Milk Cash Sales	$243,000			Depreciation Expense Ratio
	Ending Livestock Inventory	$119,000			Interest Expense Ratio
	Beginning Livestock Inventory	$29,000			Total Expense Ratio
	Accrual Gross Revenue from Livestock and Milk		$333,000		Net Farm Income Ratio
	Gain/Loss on Sale of Breding Livestocks		6500		Repayment Capacity
	Agricultural Program Payments		28000		Capital Debt Repayment Capacity
	Crop Insurance Proceeds		6500
	Other Farm Income		0
	Gross Revenue		$1,017,000
	Livestock Purchases		289,000
	Cost of Purchased Feed/Grain		89000
	Value of Farm Production		$639,000
	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		$365,500
	Expense Inventory Adjustment		-$49,000
	Depreciation		$100,000
	Total Operating Expenses		$416,500
	Interest		$50,000
	Total Expenses		$466,500
	Net Farm Income form Operations		$172,500
	Gain/Loss on Sale of Capital Assets		$0
	Net Farm Income		$172,500
	Unpaid Labor		$50,500

Quesiont 2.1. Calculate the profitability ratios, efficiency rations, repayment capacity in the right (in the green cells) using the information from balance sheet and income statement.

Quesiont 2.2. Based on your calculations above, discuss the farm's profitability, efficiency and repayment capacity (a paragaph of about 7-8 sentences). [Hint: make sure to use Dr. Khol's Benchmarks in your discussion].

Answer 2.2.

Quesiont 3. Based on your calculations in Questions 1 and 3, and discussions in Questions 2 and 4, provide an overall assessment of farm financial position (a paragaph of about 4-5 sentences).

Answer 3.

Grade

		Questions	Grade	Grade you Earn	Comments
		Liqudity and Solvency
		Questions 1.1.
		Current Ratio in J35 and K35	2
		Quick Ratio in J36 and K36	2
		Working Capital in J37 and K37	2
		Working Capital Ratio in J38 and K38	2
		DA Ratio in J40and K40	2
		EA Ratio in J41and K41	2
		DE Ratio in J42and K42	2
		Questions 1.2.	5
		Profitability, Efficiency and Repayment Capacity
		Questions 2.1.
		Rate of Return on Farm Assets in G6	2
		Rate of Return on Farm Equity G7	2
		Operating Profit Margin G8	2
		Asset Turnover Ratio G10	2
		Operating Expense Ratio G11	2
		Depreciation Expense Ratio G12	2
		Interest Expense Ratio G13	2
		Total Expense Ratio G14	2
		Net Farm Income Ratio G15	2
		Capital Debt Repayment Capacity	2
		Questions 2.1.	6
		Questions 3.	5
		Total Grade	50	0