MooLah A Cash Flow Forecast for Fighting R Farm Mike joined the U.S. Army as a flight medic after completing his Associates degree. During a deployment in Iraq he suffered a traumatic brain injury and was ultimately medically retired after 18 years of service. Unable to find a new normal in the regular world, Mike eventually found his way back to purpose through farming with a mission. Fighting R farms raises non -GMO, antibiotic free and hormone free beef. But more than that, Fighting R helps other veterans regain purpose through re-engagement with the land. If a veteran wants to learn to farm, Fighting R is there to help, train and teach. Fighting R generates his main revenue through raising and selling cattle. Additional services include veteran training programs, corporate teaming programs and fundraising. Mike needs help managing his cash flow and has asked you to develop a cash flow forecast tool for him in Excel. Develop a twelve-month, monthly cash flow forecast tool based upon the following information and requirements. Mike has $5,000 in cash in the bank as of December 31st. Your spreadsheet needs to have an input sections that enables Mike to easily make changes: Farmer Mike needs to forecast in which months hes going to purchase a new steer. Lets assume that he buys five new steers at auction. You can purchase all five in one month or spread them out, but Farmer Mike needs to be able to change these assumptions. Mike buys steer calves when they are 500 lbs and currently pays $1.25 per pound. He wants to be able to adjust these values as the market changes. He likes to keep his herd at about 40 animals. Assume Farmer Mike slaughters one steer per month, but make a provision for him to enter the number that he wants to do in any given month. Each steer slaughtered weighs on average 1,250 lbs and generates $3,500 in income. Mike wants to be able to change the price received per steer for market conditions. He also needs to be able to change the cost of slaughtering, which is currently $250 in transportation costs and $0.50 per pound in processing fee. Farmer Mike has hay and food expenses, which well greatly simplify for this exercise. Cows eat Hay and grain. Mike likes to feed with a 50/50 mix of hay vs grain. Lets assume Farmer Mike has 35 steers and that hell add five more. Create an input where Farmer Mike can input how many rolls of hay and how much grain he needs to purchase. Lets assume that of the 40 cows in the herd that the average weight is 850 pounds. Cows eat about 2.5% of their body weight a day. Gain costs $0.14 per pound. A bale of hay weights about 100 pounds and costs about $6 per bale. Calculate for the year the amount of grain and hay needed for the entire herd. Budget grain purchases quarterly, meaning take the annual amount and forecast a quarterly food purchase. Assume all hay rolls for a year are purchased at once in September. Assume that Farmer Mike takes on two capital projects during the year, one that costs $15,000 and a second that costs $20,000. Hell get grants that reimburse 90% of these amounts. The grants will be received the month following the expense. Farmer Mike needs to be able to schedule these projects in any month of the year, and also be able to change the 90% input in case the reimbursement program changes. At this point we know Farmer Mikes income and simplified outflows. Your cash flow budget needs to show an ending cash balance as of this point that takes into account the beginning cash balance, adds in revenue and deducts expenses (steer purchases, food purchases, and capital projects). We want Farmer Mike to always have $5,000 in the bank, so youll need to create a financing section that calculates how much Farmer Mike needs to borrow in any given month to cover operations. In a month where he has a surplus hell pay down the financing. He may not pay off everything he borrows in a given year, thats exactly why were building a forecasting tool for him. He can borrow at an interest cost of 4% per year.

Have to Complete a Cashflow Spreadsheet

January

February

March

April

May

June

July

August

September

October

November

December

Total

Beginning Cash Balance

$ 5,000.00

Operating Inflows

Operating Outflows

Capital

Cash On Hand at Month End

Financing

Borrowing

Interest

Loan repayment

Net Financing

Ending Cash Balance