EXCEL BUDGET ASSIGNMENT 5% (35 marks possible)

Due date: Sunday, October 30 at 11:59pm (Submit in D2L Assignment Dropbox)

The overall purpose of this assignment is to enhance your skills with Excel relating to linking raw data into a schedule that will be useful to managers. This assignment can be completed in a group of 2. Only 1 project needs to be submitted per group, but please ensure BOTH names are included on the name of the file.

REQUIRED PART 1:

In Excel, create a solution with all of the following items. ALL RAW DATA SHOULD BE TYPED AT THE BEGINNING OF YOUR SCHEDULE. ALL cells that can be linked need to be linked. If I change any number in your raw data, the schedules should update appropriately. Your solution needs to have the following items labelled in the following order:

Revenue budget (2 marks)

Production budget (2 marks)

Direct materials purchase budget (2 marks)

Direct materials usage budget (2 marks)

Direct manufacturing labour budget (2 marks)

Manufacturing overhead budget (2 marks)

Computation of Unit Costs of Finished Goods (2 marks)

Ending inventory budget (2 marks)

Cost of goods sold budget (2 marks)

Budgeted income statement (2 marks)

Cash budget (10 marks)

REQUIRED PART 2:

In a word document, answer the following questions:

Define zero-based budgeting (1 mark)

If you were creating a zero-based budget for your personal life, how would you approach it? (2 marks)

What are 10 possible budget accounts that you would use to develop your personal budget? (2 marks)

PLEASE NOTE: You are NOT allowed to use the excel spreadsheet I created in class with you. You will need to build your own.

BUDGET DATA:

Sun Chaise Ltd. produces and sells outdoor lounge chairs. They currently sell the chairs for $325 each. They estimate they will sell 250 chairs in the month of April 2022.

They had 20 chairs completed and not sold at March 31, 2022, with a total cost of $2,100. The target ending inventory at the end of April is 40 chairs.

They track the wood required for the chairs directly. The current purchase price of wood is $12 per metre. It takes 3 metres of wood to complete each chair. On March 31, 2022, Sun Chaise has 50 metres of wood on hand and they plan to have 75 metres of wood on hand at the end of April.

The workers spend 2.5 hours to complete each chair. The rate of pay for the laborers is $20 per hour.

Sun Chaise uses a traditional overhead allocation approach. The manufacturing overhead costs are allocated at 70% of direct labour costs.

The following are the selling, general and administrative costs per month:

Depreciation $1,500

Insurance 275

Sales salaries 5,500

Administrative salaries 3,100

Utilities 350

Other 840

Sun Chaise also pays the salespeople a sales commission of 4% of sales.

The customer sales are either cash or credit card. Based on past trends, customers pay cash 40% of the time and use a credit card the other 60% of the time. The bank charges Sun Chaise a fee of 2% of the credit card sales amount.

90% of the wood purchases are paid for in the current month. The other 10% is paid for the following month. The March 31, 2022 accounts payable is $1,500 for the March wood purchases.

Labour costs as well as all the selling, general and administrative costs are paid as they are incurred.

Sun Chaise also makes income tax installments of $450 per month.

The cash balance at April 1, 2022 is $750.