The goal of this lab is to evalute whole farm plans using the template below. Lab...

Transcribed Image Text:

The goal of this lab is to evalute whole farm plans using the template below. Lab Assignment 11 Step 1: Answer Question 1 (on the 'Responses' tab) without using the Solver option in Excel. Step 2: Use the Solver option in Excel to solve for the optimal farm plan and produce reports as needed. Step 3: Answer Questions 2 and 3 (on the 'Responses' tab) after using Solver Step 4: Submit your Excel file with the completed responses before the due date. VI VI VI VI VI VI Resource Availability 400 200 Enterprises Class A cropping Class B cropping Livestock Cotton Milo Wheat Milo Wheat Cows Stockers (acres) (acres) (acres) (acres) (acres) (head) (head) Resources Used for Plan Units Resource Use COTTONA MILOA WHEATA MILOB WHEATB COWS STKRS acres Class A cropland acres Class B cropland acres Pasture hours Labor 1 1 1 0 1 1 0.5 0 6 3 0 4 3 2.5 3 2.5 6 1 0 dollars Operating Capital 250 100 80 80 65 470 550 0 acres Cotton rotation req 1 -1 -1 0 Gross Margin Calculation Yield 500 80 52 66 36 Price/Unit 0.8 2.5 3.75 2.5 3.75 Gross income 400 200 195 165 135 675 620 Total Var Cost 250 100 80 80 65 470 550 Gross Margin 150 100 115 85 70 205 70 $0 Select enterprise levels Legend: Resource available (Data to be entered) Resource use per enterprise (Data to be entered) Intermediate calculations (Calculated by Excel) Decision Variables (To be chosen/entered OR or found using Solver) Results of chosen plan (Calculated by Excel) 600 2400 500000 0 Objective to Maximize Total Gross Margin The goal of this lab is to evalute whole farm plans using the template below. Lab Assignment 11 Step 1: Answer Question 1 (on the 'Responses' tab) without using the Solver option in Excel. Step 2: Use the Solver option in Excel to solve for the optimal farm plan and produce reports as needed. Step 3: Answer Questions 2 and 3 (on the 'Responses' tab) after using Solver Step 4: Submit your Excel file with the completed responses before the due date. VI VI VI VI VI VI Resource Availability 400 200 Enterprises Class A cropping Class B cropping Livestock Cotton Milo Wheat Milo Wheat Cows Stockers (acres) (acres) (acres) (acres) (acres) (head) (head) Resources Used for Plan Units Resource Use COTTONA MILOA WHEATA MILOB WHEATB COWS STKRS acres Class A cropland acres Class B cropland acres Pasture hours Labor 1 1 1 0 1 1 0.5 0 6 3 0 4 3 2.5 3 2.5 6 1 0 dollars Operating Capital 250 100 80 80 65 470 550 0 acres Cotton rotation req 1 -1 -1 0 Gross Margin Calculation Yield 500 80 52 66 36 Price/Unit 0.8 2.5 3.75 2.5 3.75 Gross income 400 200 195 165 135 675 620 Total Var Cost 250 100 80 80 65 470 550 Gross Margin 150 100 115 85 70 205 70 $0 Select enterprise levels Legend: Resource available (Data to be entered) Resource use per enterprise (Data to be entered) Intermediate calculations (Calculated by Excel) Decision Variables (To be chosen/entered OR or found using Solver) Results of chosen plan (Calculated by Excel) 600 2400 500000 0 Objective to Maximize Total Gross Margin