solve all parts, use the relevant information from previous questions to solve following questions, please do so in excel

clear workings please

Assignment Problem 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 S0.55 per kg $0.35 each $8 per direct manufacturing labour hour (DMLH) Input quantities per unit of output Regular Large Direct materials: 0.55 kg 0.3 kg Sugar Sticks Direct manufacturing labour hour (DMLH) Set-up hours per batch 1 1 0.2 hour 0.35 hour 0.09 hour 0.10 hour Inventory data for direct materials Sugar 130 kg 250 kg $65 Sticks Beginning inventory Target ending inventory Cost of beginning inventory 375 500 $107 Sipia accounts for direct materials using a FIFO cost flow assumption 1 Sales and inventory data for finished goods Large Regular 3,100 2,000 $4 $5 Expected sales in units Selling price Target ending inventory in units Beginning inventory in units Beginning inventory in dollars 2 Sipia uses a FIFO cost flow assumption for finished goods inventory 310 180 210 150 $510 $480 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 Cost type Manufacturing: Set-up Processing Rate 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) (e) 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) (2 marks) (h) Marketing and general administration costs budget 2. Sipia's balance sheet for 30 November 2019 follows. Use it and the following information to prepare a cash budget for Sipia for December 2019 (10 marks): 80% of sales are on account. Out of these sales on account, 50% are collected in the month of the sale, 49% are collected the following month and 1% are never collected and written off as bad debts All purchases of materials are on account. Sipia pays for 70% of purchases in the month of purchase and 30% in the following month All other costs are paid in the month incurred Sipia is making monthly interest payments of 1% (12% per year) on a $20,000 long-term loan Sipia plans to pay the $500 of taxes owed as of 30 November 2019 in December 2019. Income tax expense for December is zero. 40% of processing and set-up costs, and 30% of marketing and general administration costs, are depreciation Sipia Balance Sheet as of 30 November 2019 Assets Cash $2,000 $4,800 Account receivable Less: Allowance for bad debts 4,704 96 Inventories: Direct materials 172 Finished goods 990 $190,000 55,759 Fixed assets Less: Accumulated depreciation Total assets Liabilities and equity Account payable Taxes payable Interest payable Long-term debt Ordinary shares Retained earnings Total liabilities and equity 134,241 $142,107 $696 500 200 20,000 10,000 110,711 $142,107 3. Prepare a budgeted income statement for December 2019 and a budgeted balance sheet for Sipia as of 31 December 2019. (10 marks)