ACTIVITY BASED BUDGETING Fauji Fertilizer Ltd. manufactures Nitro-phosphorus Fertilizer. Sales are seasonal due to different crops. The
Question:
ACTIVITY BASED BUDGETING
Fauji Fertilizer Ltd. manufactures Nitro-phosphorus Fertilizer.
Sales are seasonal due to different crops. The expected pattern of sales for the next year (2021) is as follows:
Quarter1st 2nd 3rd 4th Year
Sales in tons500 1,500 2,000 1,000 5,000
Each ton sells for Rs.25,000. All sales are on account, and Fauji's experience with cash collections is that 55% of each quarter's sales are collected during the same quarter as the sale. The remaining 45% of sales is collected in the quarter after the sale. Fauji experiences negligible bad debts, and so this is ignored in the budgeting process.
Sales in the fourth quarter of 2020 are Rs25,000,000 (1000 tons).
Fauji desires to have 10% of the following quarter's sales needs in finished-goods inventory at the end of each quarter. In first quarter of 2022, Fauji expects to have sales of 600 tons. (On December 31, 2020, Fauji expects to have 80 tons fertilizer in inventory.)
Each ton Fertilizer requires two tons of raw material. Fauji desires to have 10% of the next quarter's raw material in inventory at the end of each quarter. In first quarter of 2022, Fauji expects to have production of 650 tons. (On December 31, 2020, Fauji expects to have 105 tons of raw material in inventory.)
The raw material price is Rs.5,000 per ton. The company buys its raw material on account and pays 65% of the resulting accounts payable during the quarter of the purchase. The remaining 35% is paid during the following quarter. (The raw-material purchases in the fourth quarter of 2020 are expected to be Rs.10,000,000.)
Each ton of fertilizer requires 5 labor hours, whereas one labor hour costs Rs.200.
Manufacturing-Overhead estimates for the year 2021 are as follows
Quarter 1st 2nd 3rd 4th Year
Unit-level costs:
Indirect material 24,000 72,00096,00048,000240,000 Utilities 5,00015,00020,00010,00050,000
Batch-level costs:
Production runs10 30 40 20 100
Setup4,800 14,40019,2009,60048,000 Purchasing 6,000 18,000 24,000 12,000 60,000
Quality control6,000 18,000 24,00012,00060,000
Product-level costs:
Chemical engineering10,00010,00010,00010,00040,000
Facility- and general-operations-level costs:
Supervisory salaries100,000100,000100,000100,000400,000 Insurance 50,000 50,00050,00050,000 200,000 Maintenance50,000 50,000 50,000 50,000 200,000 Utilities80,000 80,000 80,000 80,000 320,000 Depreciation200,000 200,000 200,000200,000800,000
Selling, General & Administrative Expense estimates for the year 2021 are as follows
Quarter 1st 2nd 3rd 4th Year
Unit-level expenses:
Sales Commission15,000 45,00060,00030,000150,000
Customer-level expenses:
Sales Salaries90,000270,000360,000180,000900,000
Operator Salaries50,000 150,000 200,000 100,000 600,000
Facilities and General Operations Expenses:
Packaging Design19,20019,20019,20019,20076,800
Facility- and general-operations-level costs:
Manager salaries152,500152,500152,500152,500610,000 Advertising 100,000 100,000100,000100,000 400,000
Admin. Salaries147,500 147,500147,500 147,500 590,000
Following is Investing and Financing estimate for the year 2021 are as follows
Quarter 1st 2nd 3rd 4th Year
Bank Loan (12%)100,000,000
Loan Repayment25,000,00025,000,00025,000,00025,000,000100,000,000
Plant Addition20,000,00050,000,00012,000,0005,000,00087,000,000
Assuming same opening and ending balance in WIP, Make all the budget schedules for 2021 except Balance Sheet (12 schedules). Include a column for each quarter, and for the year. This project should be done in MS Excel. Best of Luck!