Assignment: Assume Sooner Crops needs to create a cash flow budget for the 2024 calendar year....

$\mathrm{}\mathrm{}\mathrm{}\mathrm{}$

Transcribed Image Text:

Assignment: Assume Sooner Crops needs to create a cash flow budget for the 2024 calendar year. 1) Use the following information to create a cash flow budget for 2024 in Excel. 2) Use the completed cash flow budget to answer capital budgeting, profitability, and management analysis questions. 1. Hired labor 2024 Estimated Expenses Amount Frequency/ Transaction Month $20,000 Per year, equal amount each month. $5,000 Per year, 1/2 in March, 1/2 in December $14,400 Per year, 1/2 in April, 1/2 in August Transaction 2. Feed and grain 3. Chemicals 4. Machinery hire $2,800 October 5. Machinery repairs $6,800 6. Building repairs $4,000 Per year, 1/2 in May, 1/2 in September November 7. Livestock expenses $4,800 Per year, equal amount each month 8. Fertilizer $14,000 February 9. Gas, fuel, oil $7,200 Per year, 1/2 in April, 1/2 September 10. Property taxes $2,000 Per year, 1/2 in June, 1/2 in November Name: Farmer ID: 11. General farm insurance $3,000 March 12. Farm share of Auto and Pickup Insurance $4,800 13. Utilities $3,800 Per year, equal amount each month Per year, equal amount cach month 14. Miscellaneous $2,500 15. Seed $5,400 Per year, equal amount each month February Debt Situation as of 1/1/2024 Balance: Current Operating Loan $20,000 Beginning inventory (January 1, 2024): Crop Quantity Wheat (Bushels) Beef Cows (Head) Cotton (Lbs/Acre) 15,000 200 400 Yield/ Prices $3.40 per Bushel 90% Calf Crop, 90 cents per pound 50 lbs per acre, 72 cents per pound Additional Information 1. Sells all calves in August at average weight of 450 lbs. (Assume no replacements kept.) 2. Plans to trade for a new pickup in March, paying $18,500 cash difference. 3. Will sell a bull in April for $800 and buy a replacement in May for $2,000. 4. Income and Social Security tax of $15,200 due in June. 5. Family living expenses of $3,500 per month. 6. Personal life insurance premium of $2,000 due in July. 7. Cash on hand January 1, 2024, $15,000. 8. Assume all 2023 wheat and all cotton is sold in October 9. Spouse's non-farm job nets $2,000 per month after all deductions. 10, Maintain $1500 monthly balance. Analysis Questions: 1) Does it look like a profitable year? Why? How can you tell or cannot tell? 2) If you were a banker would you approve a new $100,000 noncurrent loan in August, for the purchase of a 150,000 combine? Why or Why not? Assignment: Assume Sooner Crops needs to create a cash flow budget for the 2024 calendar year. 1) Use the following information to create a cash flow budget for 2024 in Excel. 2) Use the completed cash flow budget to answer capital budgeting, profitability, and management analysis questions. 1. Hired labor 2024 Estimated Expenses Amount Frequency/ Transaction Month $20,000 Per year, equal amount each month. $5,000 Per year, 1/2 in March, 1/2 in December $14,400 Per year, 1/2 in April, 1/2 in August Transaction 2. Feed and grain 3. Chemicals 4. Machinery hire $2,800 October 5. Machinery repairs $6,800 6. Building repairs $4,000 Per year, 1/2 in May, 1/2 in September November 7. Livestock expenses $4,800 Per year, equal amount each month 8. Fertilizer $14,000 February 9. Gas, fuel, oil $7,200 Per year, 1/2 in April, 1/2 September 10. Property taxes $2,000 Per year, 1/2 in June, 1/2 in November Name: Farmer ID: 11. General farm insurance $3,000 March 12. Farm share of Auto and Pickup Insurance $4,800 13. Utilities $3,800 Per year, equal amount each month Per year, equal amount cach month 14. Miscellaneous $2,500 15. Seed $5,400 Per year, equal amount each month February Debt Situation as of 1/1/2024 Balance: Current Operating Loan $20,000 Beginning inventory (January 1, 2024): Crop Quantity Wheat (Bushels) Beef Cows (Head) Cotton (Lbs/Acre) 15,000 200 400 Yield/ Prices $3.40 per Bushel 90% Calf Crop, 90 cents per pound 50 lbs per acre, 72 cents per pound Additional Information 1. Sells all calves in August at average weight of 450 lbs. (Assume no replacements kept.) 2. Plans to trade for a new pickup in March, paying $18,500 cash difference. 3. Will sell a bull in April for $800 and buy a replacement in May for $2,000. 4. Income and Social Security tax of $15,200 due in June. 5. Family living expenses of $3,500 per month. 6. Personal life insurance premium of $2,000 due in July. 7. Cash on hand January 1, 2024, $15,000. 8. Assume all 2023 wheat and all cotton is sold in October 9. Spouse's non-farm job nets $2,000 per month after all deductions. 10, Maintain $1500 monthly balance. Analysis Questions: 1) Does it look like a profitable year? Why? How can you tell or cannot tell? 2) If you were a banker would you approve a new $100,000 noncurrent loan in August, for the purchase of a 150,000 combine? Why or Why not?