DM1 DMZ Cost per kg $15 $25 Raw materials inventories are planned based on the following months usage. For DM1, it is projected to be 20% of the following month's use) and for DMZ, it is estimated as 25% of the following months usage. . Direct Labour cm C02 5 units per 4 units per Direct labour hours hour hour Till June Direct Labour Rate per hour 35 Ination 4% From July 2022, the Direct Labour houry rate will increase with the rate of ination . Indirect Costs The entity uses Direct labour costs to alocate manufacturing overheads. - Manufacturing Overheads are estimated at 10% of direct labour cost (including depreciation on factory building and Machinery). Depreciation on Factory building and Machinery is 30% of Manufacturing Overheads - Selling and administrative expenses are estimated to be 8% of sales - Depreciation on ofce equipment is calculated at $3000 per month . Other information Inventories are planned for each product so that the nished goods inventory s calculated based on the following month's sales (round up to the closest number). For product 0131, the required finished goods inventory until June 2022 is 30% and 10% from July 2022. For product CQ2, the required nished goods inventory until June 2022 is 20% and 40% from July 2022. Actual sales in November 2021 are $68,755 and in December 2021 is $71390 o The Company sells goods on a cash and credit basis. Every month Cash Sales are 30% of total sales) and the remaining sales are on a credit basis. - Customers usually pay 50% in the month of sale) 30% in the month following the sale and 15% in the second month following the sale. - The entity pays the suppliers at 70% in the same month and 30% in the following month of purchases - All Direct labour expenses are paid in the same month 0 Manufacturing overheads, Selling, and administrative expensesare fully paid in the same month. - Accounts Payable balance on 31 December is $18977 o The cash balance on 31 December 2021 is $6771 - The Company has the Policy to maintain a maximum monthly cash balance of 540000 and any excess cash will be paid out as dividends. Regulred: Use an excel spreadsheet to build the following budgets 1. Sales budget for CQ1 and C02 monthly from January 2022 to December 2022 (2 marks) 2. The production budget for CQ1 and CO2 monthly from January 2022 to December 2022 (3 marks) 3. Direct Materials used Budget for CQ1 and CO2 monthly from January 2022 to December 2022 (3 marks) 4. Direct Labour budget for CQ1 and 092 monthly from January 2022 to December 2022 (3 marks) 5. Indirect Cost budget monthly from January 2022 to December 2022 (4 marks) 6. Cost per unit for C01 and 002 (4 marks) 7. Cost of goods sold schedule for 001 and 002 monthly from January 2022 to December 2022 (4 marks) 8. Budgeted Income statement quarterly along with data visualisation (6 marks) 9. Cash Budget monthly from January 2022 to Deoember2022 (5 marks) 10. Use of formulas and excel presentation (6 marks) Aussie Cookies produces cookies for resale at grocery stores throughout Australia. The Company is currently in the process of establishing a monthly master budget for this coming year, which ends in December 2022. Aussie cookies produce and sell CQ1 and CQ2. The Company recently introduced CQ2 and expects to deliver strong sales in the calendar year 2022 The following information is gathered from the accounting records and conversations with the entity's Managers 1. Sales CQ1 is maturing stage and will grow until June; then, the sales decline Estimated sales of CQ1 in January 2022 are 1180 units The increase in CQ1 sales until June 2022 is forecasted at 15% and -10% from July 2022 onwards. The price of CQ1 until September 2022 is $39.99 per unit, and from October 2022 onwards, the price is expected to be $34.99 per unit. CQ2 is in a growth phase, and the growth rate is expected to increase in 2nd half of the year. Estimated sales of CQ2 in January 2022 are 585 units CQ2 sales are expected to increase by 5% until June 2022 and 20% from July 2022 onwards. . The price of CQ2 all year was set at $49.99 per unit. The management decided to offer a 10% discount until June 2022. 2. Direct materials DM 1 and DM2 are the 2 two materials used to manufacture CQ1 and CQ2. Below are the requirements for CQ1 and CQ2 (Use 2 Decimal points) CQ1 CQ2 DM 1 - QTY per unit 0.5 kg 0.4 kg DM 2 - QTY per unit 0.6 kg 0.3 kg