Question

Company BVEX, headquartered in Toronto, Canada, operates seven double-trailer trucks for commercial long-distance hauling of cattle in Ontario, Quebec, Manitoba, New York, Vermont, Massachusetts, New Jersey, and Maine. Each truck averages one completed load per week, picking up the cattle from various farms across the aforementioned states and provinces. The cattle are driven to a large farm near Milton, Ontario.
BVEX maintains an office in each of the 8 states and provinces it operates in. Staffing in each of these offices includes a manager, a secretary, and a veterinarian.
BVEX’s CEO is seriously considering dropping New Jersey as a source of business. Last year, only 25 truckloads of cattle were handled in that state. BVEX’s CEO wants to determine if it is profitable to retain an office and do business in New Jersey’s farms.
To analyze the New Jersey market, BVEX’s CEO gathers data on last year's cattle shipments and revenues. Each of the 25 trucks that were loaded in New Jersey last year carried between 26 and 50 cows. The income generated per cow differed significantly (ranging from 50 to 80 dollars) based on the weight of the cows to be shipped. (See the table below for details.) BVEX’s CEO decided that if she were to simulate 25 truckloads out of New Jersey, she could determine if it would be profitable to continue to operate there next year. She estimates that each shipment to the Milton farm costs $900, including the driver, gasoline, and truck expenses; other cargo and loading and unloading costs average $120 per shipment. In addition, it costs $41,000 per year to operate the New Jersey office, including salaries and indirect overhead costs from the home office in Toronto.

Number of Cows Loaded	Probability	Revenue per cow	Probability
26-30 (28)	0.12
31-35 (33)	0.16	$50	0.20
36-40 (38)	0.24	$60	0.44
41-45 (43)	0.36	$70	0.28
46-50 (48)	0.12	$80	0.08
	1.00		1.00

Here is the crucial question that the BVEX’s CEO wants to address: Will the shipments of cattle out of New Jersey next year generate enough revenues to cover BVEX costs there?

This is the main question above. Can I please get the excel solution?

Answer 1

SOLUTION:-

Whether the Shipments of cattle out of New Jersey will generate enough revenues next year to cover BVEX costs

Cash Inflows (Revenues)
No. of Cows Loaded	Probability	Revenue per Cow	Total
28	0.12	$ -	$ -
33	0.16	$ 50.00	$ 264.00
38	0.24	$ 60.00	$ 547.20
43	0.36	$ 70.00	$ 1,083.60
48	0.12	$ 80.00	$ 460.80
109	1		$ 2,355.60

Total Revenue from 25 Trucks $ 58,890.00

Cash Outflows (Costs)	Per Truck	25 Trucks
Costs including driver, gasoline, truck expenses	$ 900.00	$ 22,500.00
Loading, Unloading Charges	$ 120.00	$ 3,000.00
Cost of Operating ltalian Office		$ 41,000.00
Total Costs		$ 66,500.00

BVEX will generate revenues only if

Expected Cash Inflows i.e., the Revenues shall be more than the Cash Outflows i.e., costs

Cash Inflows (Revenues)    -   Cash Outflows (costs)$(7,610.00)

Therefore, BVEX will not be able to generate revenues next year, due to loss $7610.00

Hope your query is resolved. Feel free toask any fruther queries in the comment section.

THANK YOU, if any queries please leave your valuable comment on comment box..............

If possible then rate the answer as well