Financial Model: Beef Rearing Business

Background to Assignment We already developed a financial model for 'beef fattening' - this is a straight forward business of buying steers, fattening them over a set period of time and selling for slaughter - see attached model sample titled 'beef fattening template V1 7-1-13. We are happy with this model and would like you to use this format as the template in terms of the new model. We are now seeking to develop a 'beef rearing' model which includes the development of a breeding herd and cattle fattening operations. Scope of Work Using the assumption sheet 'beef rearing assumptions 7-1-13' i'd like you to quote on completing the following tasks: 1) scheduling (linear programming i think) of breeding herd according to assumption inputs so that the revenues and costs in the rest of the model are driven by this. This will require following a certain logic which is a little complex but explained below. This logic along with the rest of the logic to be added below assumption see line 235 and onward; 2) Find and correct error in spreadsheet where cells do not automatically update when data is changed - see lines 69 and 70; 3) Link the assumptions into a series of sheets (estate capex, estate revenues, funds required, financials, highlights summary - no need for headoffice capex or headoffice revenues sheet so leave these out of this model) in the same format as the 'beef fattening template' provided. Logic of Cattle Breading Model Because we are establishing a herd from scratch we will need to purchase cows from the market for the first three or so years. Thereafter from the calves born we will use the heifers to replace the cull cows (old cows that are no longer viable for breeding) taken out of the herd. Basically cows are in either of the following modes - resting waiting to come into season, ready for breeding, pregnant, lactating with calf, resting post weaning. All of these operate within a financial quarter with cows that finish lactating (i.e. calves are weaned) starting the cycle again. Calves are separated into three uses: 1) replacement heifers (kept for future breeding either increasing size of herd or replacing cull cows); 2) Vealers (kept until 9 months and sold for slaughter) & 3) 18 month finishing (mostly steers) which are fattened out ready for slaughter. In addition the business we buy from local villagers young steers and fatten these out for slaughter. Breeding bulls are kept for a set period of time to service the breeding cows. After they finish their productive cycle them are sold for slaughter. So in summary breeding cows either get pregnant which triggers off a chain of event or they don't and remain ready for breeding. Any questions please let me know. happy to answer any questions just list them out in a PM. Looking to assign project on Saturday Morning Dubai time