Please show the work in excel Comprehensive Master Budget: ABC Costing Operating & Financial…

 
   

Need your ASSIGNMENT done? Use our paper writing service to score better and meet your deadlines.  

Please show the work in excel Comprehensive Master Budget: ABC Costing Operating & Financial Budgets Sipia is a well-known candyfloss maker in Western Australia and produces candyfloss in two sizes, i.e., regular and large. The company sells their products on several platforms such as stores, fairs, schools for fundraisers and in bulk on the internet. 2019 summer is approaching, and Sipia is preparing its budget for December. All Sipia's candyflosses are hand-made, mostly out of sugar, and attached to wooden sticks. Expected sales are based on past experience. Other information for December 2019 is as follows: Input prices Direct materials: Sugar Sticks Direct manufacturing labour $0.55 per kg $0.35 each $8 per direct manufacturing labour hour (DMLH) Large Input quantities per unit of output Regular Direct materials: Sugar 0.3 kg Sticks Direct manufacturing labour hour (DMLH) 0.2 hour Set-up hours per batch 0.09 hour 0.55 kg 0.35 hour 0.10 hour Inventory data for direct materials Sugar Sticks Beginning inventory 130 kg 375 Target ending inventory 250 kg 500 Cost of beginning inventory $65 $107 Sipia accounts for direct materials using a FIFO cost flow assumption. Sales and inventory data for finished goods? Regular Large Expected sales in units 3,100 2,000 Selling price $4 $5 Target ending inventory in units 310 180 Beginning inventory in units 210 150 Beginning inventory in dollars $510 $480 2 Sipia uses a FIFO cost flow assumption for finished goods inventory. All the candyfloss are made in batches of 10. Sipia incurs manufacturing overhead cost, and marketing and general administration costs, but customers pay for shipping. Other than manufacturing labour costs, monthly processing costs are very small. Sipia uses activity-based costing (ABC) and has classified all overhead costs for December 2019 as follows: Denominator activity Rate Cost type Manufacturing: Set-up Processing Set-up hours Direct manufacturing labour hour (DMLH) $21 per set-up hour $1.8 per DMLH Non-manufacturing: Marketing & general admin Sales revenue 10% Required 1. For December 2019, prepare the following: (a) Revenue budget (4 marks) (b) Production budget in units (8 marks) (c) Direct materials usage budget and direct materials purchases budget (14 marks) (d) Direct manufacturing labour cost budget (8 marks) (e) Manufacturing overhead cost budgets for processing and set-up activities (2 marks) (f) Budgeted unit cost of ending finished goods inventory and ending inventories budget (18 marks) (g) Cost of goods sold budget (4 marks) (h) Marketing and general administration costs budget (2 marks)