Subject Code: ACCM4100

Subject Name: Management Accounting

Assessment Title: Report

Assessment Type: Report to management

Your Task

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 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

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.

3

Direct Labour

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

5. Other information

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.

Required:

Use an excel spreadsheet to build the following budgets

1. Sales budget for CQ1 and CQ2 monthly from January 2022 to December 2022

2. The production budget for CQ1 and CQ2 monthly from January 2022 to December 2022

3. Direct Materials used the Budget for CQ1 and CQ2 monthly from January 2022 to December 2022

4. Direct Labour budget for CQ1 and CQ2 monthly from January 2022 to December 2022

5. Indirect Cost budget monthly from January 2022 to December 2022

6. Cost per unit for CQ1 and CQ2

7. Cost of goods sold schedule for CQ1 and CQ2 monthly from January 2022 to December 2022

8. Budgeted Income statement quarterly along with data visualisation

9. Cash Budget monthly from January 2022 to December 2022

10. Use of formulas and excel presentation

No word limit for this assessment