Taking into account data in Exhibit 1, Cost and Production Information, prepare a table that calculates when a hen is considered spent under normal conditions such as CCF Brands contract or that could be used to decide when it is no longer profitable to continue egg production. That is, create a model with formulas for which an individual could easily vary inputs, such as price, profit, or variable costs. Use the output template with the following headings.

Hint: Distinguish between relevant and non-relevant costs. Determine the estimated revenue per dozen and weekly production costs first.

Based on the model that you developed in question 1, and assuming that hens are already laying, how sensitive is the model to changes in expected contract profit? In variable costs? What course of action does the model indicate should be taken when contribution margin is negative?

Please answer this question in Microsoft Excel, and provide the formulas for the answers.

Exhibit 1. Arkansas Egg Company Cost and Production Information Arkansas Egg aimed to collect 26.6 dozen eggs from each hen over its productive laying cycle of 55 weeks. If that happened, based on the contract price, then AEC recovered the costs of bringing the bird to its productive cycle (about 40 cents/dozen) as well as fixed overhead (about 16 cents/dozen). The approximate contribution margin during this time was 7%. After the breakeven point, the only costs incurred were the variable production costs. Information on hen life cycle Approximate life of hen 78 weeks Pre-productive period of hen life cycle First 23 weeks of life Productive period or laying cycle Last 55 weeks of life Average age of laying hens at Summers' barns 43 weeks Average age of laying hens at Thomas' barns 48 weeks Minimum production target (PT) over which to allocate costs 26.6 dozen $0.40 per $0.16 per Approximate costs based on 26.6 dozen production target Total pre-production cost through week 23 Fixed overhead cost (facilities, debt service, depopulation, etc.) Variable production costs (feed, transportation, labor, etc.) Total cost for producing organic cage-free eggs Expected profit at 26.6 dozen (7%) dozen or $10.64 per bird dozen or $4.26 per bird $1.14 per dozen or $30.32 per bird $1.70 per dozen or $45.22 per bird $0.12 per dozen or $3.17 per bird Note: Arkansas Egg Company, as a family business with less than $25 million in revenue, used cash basis accounting for book and tax purposes. For purposes of this case, assume that revenue was produced after eggs are laid. Fixed and variable production costs were incurred evenly across the hen production cycle. # Eggs per Hen/Week Dozen Eggs Laid Marginal Costs Marginal Contribution Revenue to Profit Weeks # Weeks