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: Quarter 1st 2nd 3rd 4th Year Sales in tons 500 1,500 2.000 5,000 1.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.) 1 392,2020, Faun espects to be 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 1 st 2nd 3rd 4th Unit-level costs Year V Pv VALU UM puuu 5 Ye pure 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. Year Manufacturing-Overhead estimates for the year 2021 are as follows Quarter 1st 2nd 3rd 4th Unit-level costs: Indirect material 24,000 72,000 96,000 48,000 Utilities 5,000 15,000 20,000 10,000 240,000 50,000 Batch-level costs: Production runs Setup Purchasing Quality control 10 4.800 6,000 6,000 30 14,400 18,000 18.000 40 19,200 24,000 24,000 20 9,600 12,000 12,000 100 48,000 60,000 60,000 Product-level costs: Chemical engineering 10,000 10,000 10,000 10,000 40,000 Facility and general-operations-level costs: Supervisory salaries 100,000 Insurance 50,000 Maintenance 50,000 Utilities 80,000 Depreciation 200,000 100,000 50,000 50,000 80,000 200.000 100.000 50,000 50.000 80.000 200.000 100.000 50,000 50,000 80,000 200 000 400,000 200.000 200,000 320,000 800,000 Selling, General & Administrative Expense estimates for the year 2021 are as follows 3rd 4th Year 60,000 30,500 150,000 360,000 200,000 180,000 100.000 Quarter 1st 2nd Unit-level expenses: Sales Commission 15,000 45,000 Customer level expenses Sales Salaries 90,000 270,000 Operator Salaries 50,000 150,000 Facilities and General Operations Expenses Packaging Design 19,200 19,200 Facility and general-operations-level costs Manager salaries 152,500 152,500 Advertising 100,000 100,000 Admin. Salaries 147,500 147,500 900,000 600,000 19,200 19 200 76.800 152,500 100,000 147,500 152,500 100.000 147.500 610,000 400.000 590,000 Following is Investing and Financing estimate for the year 2021 are as follows 2nd 3rd 4th Year Quarter Bank Loan (12%) Loan Repayment Plant Addition 1st 100,000,000 25,000,000 20,000,000 25,000,000 50,000,000 25,000,000 12,000,000 25.000.000 5,000,000 100.000.000 87,000,000 Assuming same opening and ending balance in WIP. prepare all the budget schedules for 2021 except Balance Sheet (12 schedules). Include a column for each quarter and for the Vear This Depreciation 200,000 200,000 0,00 200,000 50,00 200,000 520.00 800,000 Selling. General & Administrative Expense estimates for the year 2021 are as follows 1st 2nd 3rd 4th Year Quarter Unit-level expenses Sales Commission 15,000 45,000 60,000 30,500 150,000 Customer-level expenses Sales Salaries 90.000 Operator Salaries 50,000 270,000 150,000 360,000 200,000 180,000 100,000 900,000 600,000 Facilities and General Operations Expenses : Packaging Design 19,200 19.200 19.200 19,200 76,800 Facility and general-operations-level costs: Manager salaries 152,500 152,500 Advertising 100,000 100,000 Admin. Salaries 147,500 147,500 152,500 100,000 147,500 152,500 100,000 147,500 610,000 400,000 590,000 . Following is Investing and Financing estimate for the year 2021 are as follows 2nd 3rd 4th Year Quarter Bank Loan (12%) Loan Repayment Plant Addition 1 st 100,000,000 25,000,000 20,000,000 25,000,000 50,000,000 25,000,000 12,000,000 25,000,000 5,000,000 100,000,000 87,000,000 Assuming same opening and ending balance in WIP, prepare 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! I Words: 647 Spell Check 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: Quarter 1st 2nd 4th Year Sales in tons 500 1,500 2,000 1,000 5,000 3rd 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.) . ch 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 Have Ius tons of raw material in inventory.) DOLU, A pects to . 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 Unit-level costs: Indirect material 24,000 72,000 96,000 48,000 Utilities 5,000 15,000 20,000 10,000 Year 240,000 50,000 Batch-level costs: Production runs Setup Purchasing Quality control 10 4,800 6,000 6,000 30 14,400 18,000 18,000 40 19,200 24,000 24,000 20 9,600 12,000 12,000 100 48,000 60,000 60,000 Product-level costs: Chemical engineering 10,000 10,000 10,000 10,000 40,000 Facility and general-operations-level costs: Supervisory salaries 100,000 100,000 Insurance 50.000 50,000 Maintenance 50,000 50,000 Utilities 80,000 80,000 Depreciation 200,000 200,000 100,000 50,000 50,000 80,000 200,000 100,000 50,000 50,000 80.000 200,000 400,000 200,000 200,000 320,000 800,000 Selling. General & Administrative Expense estimates for the year 2021 are as follows 1st 2nd 3rd 4th Year Quarter Unit-level expenses Sales Commission 15,000 45,000 60,000 30,500 150.000 Customer level expenses: Sales Salaries 90,000 Operator Salaries 50,000 270,000 150,000 360,000 200,000 180,000 100,000 900,000 600.000 Facilities and General Operations Expenses Packaging Design 19,200 19,200 19,200 19,200 76,800 Facility and general-operations-level costs: Manager salaries 152,500 152,500 Advertising 100,000 100,000 Admin. Salaries 147,500 147.500 152.500 100.000 147,500 152,500 100,000 147.500 610,000 400,000 590,000 Following is Investing and Financing estimate for the year 2021 are as follows 2nd 3rd 4th Year Quarter Bank Loan (12%) Loan Repayment Plant Addition 1st 100,000,000 25,000,000 20,000,000 25,000,000 50,000,000 25,000,000 12,000,000 25,000,000 5.000.000 100,000,000 87,000,000 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: Quarter 1st 2nd 3rd 4th Year Sales in tons 500 1,500 2.000 5,000 1.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.) 1 392,2020, Faun espects to be 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 1 st 2nd 3rd 4th Unit-level costs Year V Pv VALU UM puuu 5 Ye pure 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. Year Manufacturing-Overhead estimates for the year 2021 are as follows Quarter 1st 2nd 3rd 4th Unit-level costs: Indirect material 24,000 72,000 96,000 48,000 Utilities 5,000 15,000 20,000 10,000 240,000 50,000 Batch-level costs: Production runs Setup Purchasing Quality control 10 4.800 6,000 6,000 30 14,400 18,000 18.000 40 19,200 24,000 24,000 20 9,600 12,000 12,000 100 48,000 60,000 60,000 Product-level costs: Chemical engineering 10,000 10,000 10,000 10,000 40,000 Facility and general-operations-level costs: Supervisory salaries 100,000 Insurance 50,000 Maintenance 50,000 Utilities 80,000 Depreciation 200,000 100,000 50,000 50,000 80,000 200.000 100.000 50,000 50.000 80.000 200.000 100.000 50,000 50,000 80,000 200 000 400,000 200.000 200,000 320,000 800,000 Selling, General & Administrative Expense estimates for the year 2021 are as follows 3rd 4th Year 60,000 30,500 150,000 360,000 200,000 180,000 100.000 Quarter 1st 2nd Unit-level expenses: Sales Commission 15,000 45,000 Customer level expenses Sales Salaries 90,000 270,000 Operator Salaries 50,000 150,000 Facilities and General Operations Expenses Packaging Design 19,200 19,200 Facility and general-operations-level costs Manager salaries 152,500 152,500 Advertising 100,000 100,000 Admin. Salaries 147,500 147,500 900,000 600,000 19,200 19 200 76.800 152,500 100,000 147,500 152,500 100.000 147.500 610,000 400.000 590,000 Following is Investing and Financing estimate for the year 2021 are as follows 2nd 3rd 4th Year Quarter Bank Loan (12%) Loan Repayment Plant Addition 1st 100,000,000 25,000,000 20,000,000 25,000,000 50,000,000 25,000,000 12,000,000 25.000.000 5,000,000 100.000.000 87,000,000 Assuming same opening and ending balance in WIP. prepare all the budget schedules for 2021 except Balance Sheet (12 schedules). Include a column for each quarter and for the Vear This Depreciation 200,000 200,000 0,00 200,000 50,00 200,000 520.00 800,000 Selling. General & Administrative Expense estimates for the year 2021 are as follows 1st 2nd 3rd 4th Year Quarter Unit-level expenses Sales Commission 15,000 45,000 60,000 30,500 150,000 Customer-level expenses Sales Salaries 90.000 Operator Salaries 50,000 270,000 150,000 360,000 200,000 180,000 100,000 900,000 600,000 Facilities and General Operations Expenses : Packaging Design 19,200 19.200 19.200 19,200 76,800 Facility and general-operations-level costs: Manager salaries 152,500 152,500 Advertising 100,000 100,000 Admin. Salaries 147,500 147,500 152,500 100,000 147,500 152,500 100,000 147,500 610,000 400,000 590,000 . Following is Investing and Financing estimate for the year 2021 are as follows 2nd 3rd 4th Year Quarter Bank Loan (12%) Loan Repayment Plant Addition 1 st 100,000,000 25,000,000 20,000,000 25,000,000 50,000,000 25,000,000 12,000,000 25,000,000 5,000,000 100,000,000 87,000,000 Assuming same opening and ending balance in WIP, prepare 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! I Words: 647 Spell Check 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: Quarter 1st 2nd 4th Year Sales in tons 500 1,500 2,000 1,000 5,000 3rd 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.) . ch 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 Have Ius tons of raw material in inventory.) DOLU, A pects to . 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 Unit-level costs: Indirect material 24,000 72,000 96,000 48,000 Utilities 5,000 15,000 20,000 10,000 Year 240,000 50,000 Batch-level costs: Production runs Setup Purchasing Quality control 10 4,800 6,000 6,000 30 14,400 18,000 18,000 40 19,200 24,000 24,000 20 9,600 12,000 12,000 100 48,000 60,000 60,000 Product-level costs: Chemical engineering 10,000 10,000 10,000 10,000 40,000 Facility and general-operations-level costs: Supervisory salaries 100,000 100,000 Insurance 50.000 50,000 Maintenance 50,000 50,000 Utilities 80,000 80,000 Depreciation 200,000 200,000 100,000 50,000 50,000 80,000 200,000 100,000 50,000 50,000 80.000 200,000 400,000 200,000 200,000 320,000 800,000 Selling. General & Administrative Expense estimates for the year 2021 are as follows 1st 2nd 3rd 4th Year Quarter Unit-level expenses Sales Commission 15,000 45,000 60,000 30,500 150.000 Customer level expenses: Sales Salaries 90,000 Operator Salaries 50,000 270,000 150,000 360,000 200,000 180,000 100,000 900,000 600.000 Facilities and General Operations Expenses Packaging Design 19,200 19,200 19,200 19,200 76,800 Facility and general-operations-level costs: Manager salaries 152,500 152,500 Advertising 100,000 100,000 Admin. Salaries 147,500 147.500 152.500 100.000 147,500 152,500 100,000 147.500 610,000 400,000 590,000 Following is Investing and Financing estimate for the year 2021 are as follows 2nd 3rd 4th Year Quarter Bank Loan (12%) Loan Repayment Plant Addition 1st 100,000,000 25,000,000 20,000,000 25,000,000 50,000,000 25,000,000 12,000,000 25,000,000 5.000.000 100,000,000 87,000,000