In your other/second role as Budget Analyst for the rodenticide manufacturing company ("manufacturer") that supplies anticoagulant rodenticide baits ("rodenticides") to the Public Health Department, you will prepare a Statistical Budget, a Revenue Budget, and an Expense Budget on Excel.

The anticoagulant baits of the manufacturer are of superior-grade and rated the most effective for reservoir extermination. There are 3 types of baits that are meant for specific infested areas or localities, which your customer (the Public Health Department) has budgeted for and identified to pilot/test the rodenticide solution to leptospirosis for the coming fiscal year. Your customer has adopted the conclusion of the Mendoza study that "shared environmental issues and socio-demographic characteristics of infected populations appear to support the need for ex ante containment of rat/ rodent reservoir populations in high-risk tropical and subtropical countries." !

The Public Health Department will be purchasing and using your 3 types of commercial anticoagulant baits listed below with the number of areas or localities to be covered by each bait type (1 rodenticide per locality): !

Rodenticide A (pellet bait) =20 localities

Rodenticide B (place packs) = 30 localities! Rodenticide

C (block baits) = 60 localities!

Total = 110 localities!

The net revenue of the manufacturer for each area or locality treated with any of the three rodenticides is $200. But for the manufacturer to sell and apply any of the three products, it also has to spend for bait stations, which are enclosed boxes designed to hold baits while protecting them from moisture, spillage, dust, and dirt. There are two distinct kinds of bait stations: Non-Tamper-Resistant (NTR) which costs $10 each, and Tamper-Resistant (TR) which costs $20 each. The NTR baits are used together with Rodenticides A and B. The TR baits are used with Rodenticide C. Lastly, the total cost to the manufacturer of producing each rodenticide (whether A, B, or C) is $50 (don't forget this expense). At this point, perform the following steps in chronological order:

1. Create first your Statistical Budget to forecast total volume (number of localities) covered/served by the 3 types of rodenticides. A sample table is provided below.

Statistical Budget!

Rodenticide Type

Rodenticide A

Rodenticide B

Rodenticide C

Total:

Volume:

2.Then create your Revenue Budget by multiplying the statistical volume (from the Statistical Budget) and the manufacturer's net revenue. Total all revenue.

3.Create now your Expense Budget. List down each expense item and multiplying the price of each type of rodenticide by the corresponding volume. Determine the volume corresponding to each expense item and multiply it by the cost to the manu- facturer. Total all expenses. Then subtract total expenses from total revenue (from your Revenue Budget) to generate the budgeted manufacturer's profit/operating gain (or loss).

AFTER ALMOST A YEAR LATER, you discover that actual volumes and service mix had changed... As in real life, after almost 12 months into the budgeted fiscal year, you realized that the number of pilot localities being treated with the 3 types of rodenticides and matched with the 2 types of bait stations (i.e., the service mix) had changed due to the level of rodent infestation in these test areas. Net manufacturer's revenue for each treated locality re- mains the same at $200. Total manufacturer cost of producing each rodenticide re- mains the same at $50. NTR ($10) and TR ($20) baits cost to the manufacturer are also the same. The new distribution of localities (after almost a year) is shown below:! 

Rodenticide A (pellet bait) !10 localities Rodenticide B (place packs) ! 50 localities Rodenticide C (block baits) ! 70 localities New Total = 130 localities! 4. Now, create a Cash Balance table comparing the Actual (almost a year later) ver- sus the Budgeted Revenue, Expense, and Cash Balance to determine the new manufacturer's profit/operating gain (or loss) based on the changed volume and service mix. Show the totals for actual revenue and for budgeted revenue based on volumes covered. Show totals for actual expenses and for budgeted expenses based on vol- umes and service mix. Indicate the resulting variance in your table. And then subtract actual expense from actual revenue, and budgeted expense from budgeted revenue, to generate their respective manufacturer's profit/operating margin (or loss), which repre- sent your actual and budgeted cash balances, respectively.! 5. Finally, 

determine if the manufacturer is now better off given growth equal to 20 more localities served almost a year later? To do this, first divide the budgeted man- ufacturer's profit/operating margin by the budgeted total volume (from your Statistical Budget or Cash Balance table). Then, divide the actual manufacturer's profit/operating margin (almost a year later) by the actual total volume almost a year later (from your Cash Balance table). Compare the two quotients and you can now answer the final question. The variance column will indicate the $ change from the budgeted to the ac- tual manufacturer's profit/operating margin.