Your task is to effectively use Excel to create a spreadsheet and prepare a Master Budget and Provide a report to senior management which uses your understanding of management accounting.

Assessment Description

Please refer to the case scenario below

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 managementdecided to offer a 10% discount until June 2022.

1. 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
	DM 1	DM 2
Cost per kg	$15	$ 25

Raw materials inventories are planned based on the following month's usage. For DM1, it is projected to be 20% of the following month's use, and for DM2, it is estimated as 25% of the following month's usage.

	CQ1	CQ2
Direct labour hours	5 units per hour	4 units per hour

	Till June
Direct Labour Rate per hour	35
Inflation	4%

From July 2022, the Direct Labour hourly rate will increase with the rate of inflation

4. Indirect Costs

The entity uses Direct labour costs to allocate 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 office equipment is calculated at $3000 per month

1. 5. Otherinformation
   • Inventories are planned for each product so that the finished goods inventory s calculated based on the following month's sales (round up to the closest number). For product CQ1, the required finished goods inventory until June 2022 is 30% and 10% from July 2022. For product CQ2, the required finished 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$71,998
   • 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
   • Manufacturing overheads, Selling, and administrative expenses are fully paid in the same month.
   • Accounts Payable balance on 31 December is $18977
   • The cash balance on 31 December 2021 is $6771
   • The Company has the Policy to maintain a maximum monthly cash balance of $40000, and any excess cash will be paid out as dividends.

1. Use an excel spreadsheet to build the following budgets
   1. Sales budget for CQ1 and CQ2 monthly from January 2022 to December 2022(2 marks)
   2. The production budget for CQ1 and CQ2 monthly from January 2022 to December 2022(3 marks)
   3. Direct Materials used Budget for CQ1 and CQ2 monthly from January 2022 to December 2022(3 marks)
   4. Direct.Labour.budget.for.CQ1.and.CQ2.monthly.from.January.2022.toDecember.2022(3marks)
   5. Indirect Cost budget monthly from January 2022 to December 2022(4 marks)
   6. Cost per unit for CQ1 and CQ2(4 marks)
   7. Cost of goods sold schedule for CQ1 and CQ2 monthly from January 2022 to December 2022(4 marks)
   8. Budgeted Income statement quarterly along with data visualisation (6marks)
   9. Cash Budget monthly from January 2022 to December 2022(5 marks)

10.Use of formulas and excel presentation(6 marks)