Mr George owns a manufacturing company called Sunshine Cookies. The company produces cookies for resale at grocery stores throughout Australia. The accounting department is currently in the process of preparing a monthly master budget for the year ending December 2023. Sunshine cookies started producing MACARON cookies in 2022 and later introduced BISCOTTI cookies. The introduction of BISCOTTI is expected to boost sales revenue in the year 2023. The following information is collected from the accounting records and market research data compiled by the sales department. 1. Sales data It is expected that sales of MACARON cookies will grow until June; then, the sales will decline. Estimated sales of MACARON in January 2023 are 1600 units. Monthly sales of MACARON are predicted to increase consecutively at a rate of 12% until June 2023 and then sales will start to drop by 10% every month from July until December 2023. The price of MACARON cookies from January until September 2023 is predicted to be $60 per unit, and from October 2023 onwards, the price is expected to be $50 per unit. Sales of BISCOTTI is expected to grow especially in 2nd half of 2023. Estimated sales of BISCOTTI cookies in January 2023 are 600 units. Sales of BISCOTTI are expected to increase monthly by 6% until June 2023 and by 15% from July to December 2023. The price of BISCOTTI is set as $55 per unit for 2023. However, Mr George decided to offer a 10% discount from January to June 2023 to help increase market share. 2. Direct materials data The company uses two types of materials (i.e., D-MAT A and D-MAT B) to manufacture MACARON and BISCOTTI cookies. Below are the budgeted standards for MACARON and BISCOTTI. MACARON BISCOTTI D-MAT A - QTY per unit 0.6 kg 0.5 kg D-MAT B - QTY per unit 0.8 kg 0.4 kg D-MAT A D-MAT B Cost per kg $14 $22 Page 2 of 3 The desired closing raw materials inventories are planned based on the following month's usage. For D-MAT A, it is predicted to be 20% of the following month's usage, and for DMAT B, it is estimated as 24% of the following month's usage. 3. Direct Labour data MACARON BISCOTTI Direct labour hours 4 units per hour 3 units per hour The average hourly pay rate is budgeted to be $30 from January to June. Inflation is expected to kick in starting from July 2023 and will increase the Direct Labour Rate per hour by 5% and will remain at that level for the rest of 2023. 4. Indirect Costs related data The company uses Direct labour costs as a cost driver to allocate manufacturing overhead costs (MOH). The monthly total MOH costs are estimated at 20% of direct labour costs. Depreciation on Factory Buildings and Machinery is estimated as 25% of the monthly total MOH costs. Variable Selling and Administrative expenses are estimated to be 6% of sales revenue. Depreciation on office equipment is budgeted to be $2,500 per month. 5. Additional information The company wants to keep desired ending finished inventories for both products computed from the following months sales as per its policy. For MACARON cookies, the required finished goods inventory is computed as 20% from next months sales units until June 2023 and 15% from July 2023 onwards. For product BISCOTTI, the required monthly finished goods inventory is 20% of next months sales until June 2023 and 30% from July 2023 onwards. Actual sales revenue for November and December 2022 are $65,000 and $70,000 respectively. Sales of the company are 30% on cash and the remaining balance on credit. Collections from customers are 50% in the month of sale, 30% in the month following the sale and 20% in the second month following the sale. The company pays suppliers 60% in cash in the month of purchase and the remaining balance is paid off in the month following purchases. The company pays in cash for all direct labour costs, MOH costs, Selling and administrative expenses in the same month. On 31 December 2022, the balances for Accounts Payable and Cash are $18,400 and $7,200 respectively. The Company has the Policy to maintain a maximum monthly cash balance of $30,000, and any excess cash will be paid out as dividends

question

prepare Monthly Cash Budget from January to December 2023 in excel spreadsheet with all the workings