Master Budget Case

Cordon Ltd. is a company that manufactures and sells a single product, which they call a Bleu. For planning and control purposes they utilize a monthly master budget, which is usually developed at least six months in advance of the budget year. Their fiscal year end is December 31.

Their sales forecast consisted of these few lines:

For the year ended December 31, 2022: 95,000 units at $60.00 each*

For the year ended December 31, 2023: 105,000 units at $60.00 each

For the year ended December 31, 2024: 110,000 units at $60.00 each

*Expected sales for the year ended December 31, 2022 are based on actual sales to date and budgeted sales for the duration of the year.

You are provided with the following information:

1. Sales are seasonal with the peak months being summer and winter holidays. The following table shows expected distribution of sales for each month based on percentage of the total budgeted sales. Months Percentage of sales Jan, Feb, Mar 3% each Apr, Aug, Sept 5% each May, Jun, Jul 10% each Oct 11% Nov 15% Dec 20% 2 2/6
2. From previous experience, management has determined that an ending inventory equal to 20% of the next months sales is required to fit the buyers demands.
3. There are 2 types of raw material used in the production of Bleu:

• Notsoi is the first component, and is purchased in powder form. Each Bleu requires 0.5 kilograms of Notsoi, at a cost of $30.00 per kilogram. Because the supply of Notsoi is unpredictable, Cordon finds it necessary to maintain an inventory balance equal to 40% of the following months production needs as a precaution against stockouts.

• Kanraku is the second component, of which 2 are used in the assembly process per Bleu. In order to ensure this material is always available, Cordon has made a JIT agreement with the supplier which includes on-time and quality assurances. Each Bleu uses 2 Kanraku, which cost $1.00 each.

1. The beginning accounts payable (associated with Notsoi purchases only) will consist of $95,700 arising from the following estimated material purchases for November and December of 2022:

Material purchases in November 2022: $193,150

Material purchases in December 2022: $134,475

Cordon pays for 50% of a months purchases in the month of purchase, 35% in the following month and the remaining 15% two months after the month of purchase. There is no early payment discount.

1. The manufacturing process for Bleu is divided into two activities. The first step is the forming process, during which the Notsoi is heated and moulded into various shapes. During the next stage, Kanraku is fitted into the moulded Notsoi. This step is referred to as the assembly stage.

1. The first two steps of the manufacturing process are highly automated, so the only employees are three supervisors, who are trained to operate the equipment and make repairs as required. The supervisors work shifts, allowing the plant to operate for longer hours during the busier months. They are also responsible for managing the employees who work in the finishing department. There is no labour component to the manufacturing process

1. The combined unit variable overhead manufacturing rate for forming and assembly is $6.50, consisting of: Utilities--$3.00; Indirect Materials--$1.00; Plant maintenance--$1.50; environmental fee--$0.70; and Other--$0.30.

1. Fixed Manufacturing Overhead costs for the entire year are as follows: Training and development $ 43,620

Supervisors salary 269,400

Depreciation on equipment 178,800

Insurance 69,000

Other 117,600

$ 678,420 Total

The annual insurance premium is paid at the beginning of September each year. There should be no change in the premium from last year.

All other cash-related fixed manufacturing overhead costs are incurred evenly over the year and paid as incurred.

Cordon uses the straight-line method of depreciation.

1. Selling and administrative expenses have historically been a mixed cost; Previous years experience has provided the following information: .

Lowest level of sales: 80,000 units

Total Operating Expenses: $876,000

Highest level of sales:120,000 units

Total Operating Expenses: $1,116,000

The annual amount of depreciation on office furniture and equipment is only $21,000and this amount is already included in the fixed portion of the selling and administration expenses. Not included in the above expenses is bad debt expense. Payments for selling and administrative expenses occur in the month in which they are incurred.

1. Sales are on a cash and credit basis, with 70% collected during the month of the sale, 20% the following month, and 9.5% the month thereafter. of 1% of sales are considered uncollectible (bad debt expense).

1. Sales in November and December 2022 are expected to be $712,500 and $950,000 respectively. Based on the above collection pattern this will result in Accounts Receivable of $347,938 at December 31, 2022 which will be collected in January and February, 2023.

1. During the fiscal year ended December 31, 2023, Cordon will be required to make monthly income tax installment payments of $5,000. Outstanding income taxes from the year ended December 31, 2022 must be paid in April 2023. Income tax expense is estimated to be 30% of net income. Income taxes for the year ended December 31, 2023, in excess of installment payments, will be paid in April, 2024.

1. An arrangement has been made with the local bank that if Cordon maintains a minimum balance of $20,000 in their bank account, they will be given a line of credit at a preferred rate of 6% per annum. All borrowing is considered to happen on the first day of the month, repayments are on the last day of the month. All borrowings and repayments from the bank should be in multiples of $1,000 and interest must be paid at the end of each month. Interest is calculated on the balance at the beginning of the month, which includes any amounts borrowed that month.
2. A listing of the estimated balances in the companys ledger accounts as of December 31, 2022 is given below:

Assets

Cash $ 83,214

Accounts receivable 347,938

Inventory-raw materials (Notsoi) 15,750

Inventory-finished goods 22,365

Prepaid Insurance 46,000

Capital assets (net) 724,000

Goodwill 1000

Total assets $1,240,267

Liabilities and Shareholders Equity

Accounts payable $ 96,210

Income taxes payable 22,500

Capital stock 1,000,000

Retained Earnings 121,557

Total liabilities and shareholders equity $1,240,267:

INSTRUCTIONS:

1. Prepare a monthly master budget for Cordon for the year ended December 31, 2023, including the following schedules:

Sales Budget

Schedule of Cash Receipts (cash collections)

Production Budget

Direct Materials Budget

Schedule of Cash Disbursements

Manufacturing Overhead Budget

Ending Finished Goods Inventory Budget

Selling and Administrative Expense Budget

Cash Budget

Conditions:

Project is to be completed in Excel only. It will not be accepted in any other format. If you have not yet downloaded Excel, you can get it on your MyTrent Homepage, by going to Services on the top right, and selecting Microsoft Student Advantage.

If you choose to form a group with people you know in the class, list your names and student numbers at the top of the Document. Max of 5 students per group, zero exceptions.

This project is something that you would produce internally for a manager or executive (possibly even the owner!). Given this, your grade is not just based on getting the right answer, but also on presentation of whatever answer you get. Given this, if you choose to work in a group, you are responsible for making sure there is a consistent quality in your project. Again, treat this like you are presenting it to your boss.

This project is quite large, do not procrastinate it. There is a sample template provided in the assignments tab to give you an idea of a general format that you can follow for this project. Do not use this example as a template, as it will not necessarily line up with your project. It is only an example for you to build your own tables that fit your assignment.

Cheating will result in a 0. This includes pulling answers from online websites, using AI to produce your answers, and getting people outside of the course to do it for you. There are zero exceptions. If you are in a group and one of your group members is caught cheating, this 0 will apply to all group members.