The screen shot you see is the heart of the Testing Technique assignment. Using the Egg Production Simulation XLSX file, your task is to use the Solver Tool (see text book Chapter 5, Evaluating Scenarios with Goal Seek, Pp 224-226) to find a way to make our egg farm a profitable operation. Your assignment is to suggest 2 solutions that you think will work -- there is no real 'book' answer here because parts of the simulation model have random number generators that change the revenue conditions of the farm. To truly solve the problem, you must change some of the underlying assumptions of the simulation and have some rational reasoning. Please submit your modified XLSX file and a new worksheet called "My Notes" in which you've entered text in cells to lay out your assumptions. (Hint: to 'freeze' cells first copy them and then paste them in the same cell using the special Paste Values function. This will stop the random number generators from disturbing your assumptions.)

Let's take a tour of the attachments: Egg Production Simulation is the file you'll be using to solve the problem. The other files are background reference files you can use to justify changing the assumptions. Take the time to read these before jumping in to the Simulation file.

The Simulation file costs of 15 worksheets. The Summary Worksheet is the master sheet that pulls data from all the other sheets. Some of the pulls are formulas in the cells directing data from other worksheets or cells. You should review the cell references to see what's coming from where. On the Summary Worksheet you'll find a red-framed collection of cells that are the heart of a cash flow model. The column of Original Data pulls operating revenues and then subtracts operating expenses to produce a Net Operating Cashflow. In the real world, net profits are important for reporting to the owners and tax authorities but businesses live and die on their cash flow. If a business is profitable on paper, but doesn't have the cash to pay its people, bills or debts then it is INSOLVENT and turns to the bankruptcy court for protection or liquidation. Our little egg farm is losing ~$21,000 and you task is to make it break even or possibly have positive cashflow.

Using "Solver" you can set the Net Operating Cashflow cell to a $1.00 (breakeven) or some higher number. Solver will then examine the cell formulas and then recommend a solution. I ran a sample solution for you to see, but Solver reduced the Labor expense to ZERO. Solver created a solution worksheet which I renamed as "Answer Farmer Starves." I'd like to see you generate two more of those worksheets with Solver.

The DEVIL IS IN THE DETAILS!!!! All of the data on the Summary Worksheet comes from the month worksheets and a pricing worksheet called Egg Prices Graph. What you pay for eggs in the store isn't what the farmer gets. All the egg processors and retailers all get a slice of the price; that is called "The Spread." Our egg farm's revenue are AFTER the spread comes out. Spreads and prices change during the course of the year. Chickens lay fewer eggs in cold months. Spikes in demand can constrain inventories so the spread reduces to pay farmers more for what they have. Flocks produce more or less as they age. Feed can effect how much and the quality of eggs. Prices and quantities are the tail that wags this dog!

The MONTH worksheets show the number of laying hens, what they produce, what prices were, the amount of feed and its costs are. This is the real operating core of the egg farm. It's here that you might want to focus your assumptions and see how they play through to the Summary Sheet, and eventually get presented to Solver. And it's here that all the background files will help.

Facts about our farm: we have 1,000 white-shell laying hens, a 1,500 square foot barn, a small manure facility, a farmer and a part time helper who put in 100 hours of labor a month (probably unrealistically low.) We buy our feed from Tractor Supply (Purina Layena Premium 16% protein). We're paying $7 per bale for shavings.

YES THIS IS A LOT OF WORK! But it is truly representative of real world data analytical problems. No small farm would do this, but a Cal-Maine Food Inc (36.4 MILLION hens), Rose Acre Farms (26 million hens) or a Michael Foods (13.4 million hens) all use models like our Simulation.

Instructions AutoSave Of Egg Production Simulation.xlsx Search Thomas Marshall File Home Share Comments Insert Page Layout Formulas Data Review View Developer Help Power Pivot FRED Queries & Connections & & Clear e Properties Reapply Refresh Stocks Geography Filter Text to Edit Links Advanced Columns Queries & Connections Data Types Sort & Fiter Data Tools Data Analysis 2 Solver Sant Outline Get Data e Get & Transform Data What If Forecast Analysis Sheet Forecast Analyze C38 f Solver Parameters A B D E G F Annual Totals 22 Set Objective: SC530 To: Supply O Min Flock 1 value of Non-! : 23 Eggs Collected Feed (pounds) Feed Cost Income Expenses Purchases By Changing Variable Cells: 24 Year to Date 276,882 75,438 S 22,933.00 S 27,767.01 S 22,933.00 $ $ SC529 $C$37 $C29: 25 26 Subject to the Constraints: Sample Solution: 58532 > 100 $D$ 31 > 15000 Add 27 Farmer Starves Solution 1 1 Solution 2 Original Data 28 Change 29 Operating Gross Profit $ 10,001.58 S 4,810.14 $ 4,810.14 $ 4,810.14 Delete 30 Operating & Fixed Costs 31 Family & Hired Labor (1,200 hrs) $ $ 12,000.00 $ 12,000.00 $ 12,000.00 Reset All 32 Shavings (Bales) 300 $ 1,660.09 $ 2,100.00 $ 2,100.00 $ 2,100.00 33 Pullets (1,000 @$5) $ 2,506.16 $ 5,000.00 $ 5,000.00 $ 5,000.00 Load/Save 34 Carton Packaging ($.20 per doz) $ 2,488.39 $ 4,614.70 $ 4,614.70 $ 4,614.70 Make Unconstrained variables Non-Negative Utilities ($100 monthly) $ 1,056.36 $ 1,200.00 $ 1,200.00 $ 1,200.00 Building & Equipment (1,500SF) Select a Solving $ 1,275.56 $ 1,500.00 $ 1,500.00 $ 1,500.00 GRG Nonlinear Options Method: 37 General & Administrative (5% Supp $ 1,014.03 $ 1,146.65 $ 1,146.65 $ 1,146.65 38 Net Operating Cashflow S 1.00 S (22,751.21) S (22,751.21) S (21,349.48) Solving Method 39 Select the GRG Nonlinear engine for Solver problems that are smooth nonlinear. Select the LP Simplex 40 engine for linear Solver problems, and select the Evolutionary engine for Solver problems that are non-smooth. 41 42 43 Help Solve Close Answer Farmer Starves Summary January January February March April May June July August September October Novembe... Point 1009 Instructions AutoSave Of Egg Production Simulation.xlsx Search Thomas Marshall File Home Share Comments Insert Page Layout Formulas Data Review View Developer Help Power Pivot FRED Queries & Connections & & Clear e Properties Reapply Refresh Stocks Geography Filter Text to Edit Links Advanced Columns Queries & Connections Data Types Sort & Fiter Data Tools Data Analysis 2 Solver Sant Outline Get Data e Get & Transform Data What If Forecast Analysis Sheet Forecast Analyze C38 f Solver Parameters A B D E G F Annual Totals 22 Set Objective: SC530 To: Supply O Min Flock 1 value of Non-! : 23 Eggs Collected Feed (pounds) Feed Cost Income Expenses Purchases By Changing Variable Cells: 24 Year to Date 276,882 75,438 S 22,933.00 S 27,767.01 S 22,933.00 $ $ SC529 $C$37 $C29: 25 26 Subject to the Constraints: Sample Solution: 58532 > 100 $D$ 31 > 15000 Add 27 Farmer Starves Solution 1 1 Solution 2 Original Data 28 Change 29 Operating Gross Profit $ 10,001.58 S 4,810.14 $ 4,810.14 $ 4,810.14 Delete 30 Operating & Fixed Costs 31 Family & Hired Labor (1,200 hrs) $ $ 12,000.00 $ 12,000.00 $ 12,000.00 Reset All 32 Shavings (Bales) 300 $ 1,660.09 $ 2,100.00 $ 2,100.00 $ 2,100.00 33 Pullets (1,000 @$5) $ 2,506.16 $ 5,000.00 $ 5,000.00 $ 5,000.00 Load/Save 34 Carton Packaging ($.20 per doz) $ 2,488.39 $ 4,614.70 $ 4,614.70 $ 4,614.70 Make Unconstrained variables Non-Negative Utilities ($100 monthly) $ 1,056.36 $ 1,200.00 $ 1,200.00 $ 1,200.00 Building & Equipment (1,500SF) Select a Solving $ 1,275.56 $ 1,500.00 $ 1,500.00 $ 1,500.00 GRG Nonlinear Options Method: 37 General & Administrative (5% Supp $ 1,014.03 $ 1,146.65 $ 1,146.65 $ 1,146.65 38 Net Operating Cashflow S 1.00 S (22,751.21) S (22,751.21) S (21,349.48) Solving Method 39 Select the GRG Nonlinear engine for Solver problems that are smooth nonlinear. Select the LP Simplex 40 engine for linear Solver problems, and select the Evolutionary engine for Solver problems that are non-smooth. 41 42 43 Help Solve Close Answer Farmer Starves Summary January January February March April May June July August September October Novembe... Point 1009