Master budget

The Columbo Company manufactures a single model trench coat. Projected sales in units for the first 5 months of fiscal 2024 are as follows.

January 18,000

February 20,000

March 15,000

April 8,000

May 6,000

Each trench coat sells for $145.

All sales are on account. The Accounts Receivable balance on January 1, 2024 will be $45,000 and is considered 100% collectible in the month of January. The Cash balance on January 1, 2024 will be $48,000. 90% of Sales are collected in cash during the month of sale and 8% in the month after sale. The remaining 2% of Sales are considered Bad Debts. Bad Debt expense is recognized each month based on recorded Sales for that month. Bad Debts are considered Variable Costs.

Finished Goods inventory shall be maintained at 80 percent of the following month's sales. Finished Goods inventory on January 1, 2024 will be 14,400 units.

There are two Raw Materials utilized in the production of each trench coat as follows:

Polyester: 5 yards per unit at a standard cost of $17 per yard.

Lining: 3 yards per unit at a standard cost of $2 per yard.

Raw Materials will be maintained at 10% of the material needed to fulfill the following month's production requirements. Inventory on hand January 1, 2024 will be 9,800 yards of Polyester and 5,880 yards of lining. All material purchases are paid in the month following the month of purchase. Accounts Payable at January 1, 2024 will be $938,400 all of which is related to material purchases to be paid in January.

Direct Labor required to produce a single trench coat is 2 direct labor hours.The standard pay rate is $11 per hour.Labor is paid in the month the hours are worked.

Overhead each month consists of both variable and fixed costs. Variable overhead is based on Direct Labor Hours. A summary of overhead costs is as follows:

Monthly Fixed Component Variable Component ($ per Direct Labor Hour)

Supplies $1.00

Utilities .75

Maintenance $3,000 .50

Wages & Benefits 52,000

Depreciation 12,000

Taxes 5,000

Other 10,000 2.00

Total $82,000 $4.25

All Overhead costs will be paid in the month they are incurred.

Selling, General and Administrative (SG&A) expenses have both variable and fixed components. The variable component is based on the number of units sold.The cost behavior of SG&A is as follows:

Monthly Fixed Component Variable Component($ per unit sold)

Salaries& Benefits $38,000

Quality costs $3.00

Depreciation 2,000

Advertising .75

Other 10,000 1.50

Total $50,000 $5.25

All SG&A costs will be paid in the month incurred.

The Company has a $1 million line of credit with the bank. The Company is required to maintain a minimum balance of $50,000 at the bank at all times. Any bank borrowings must be made in increments of $1,000. Repayment of any borrowings will be budgeted when there is available cash. Interest on the line of credit is 5% paid monthly. A full month of interest will be budgeted to be paid for any month in which there is a loan balance. Borrowings are assumed to occur on the first of the month and repayments on the last day of the month. There will be no amounts drawn against the line on January 1, 2024.

Bank fees of $150 per month are expected. This amount is NOT included in the SG&A budget as outlined above and are considered a fixed expense.

Other specific cash expenditures are planned as follows:

January: Dividends of $500,000.

February: Tax payment of $655,000.

March: The Company will purchase a new sewing system at a cost of $150,000 paid for in cash.

 

Required:

 

1. Prepare the following budgets in good form for each of the first three months of 2024 and show first quarter totals for each budget:

Sales Budget

Production budget

Material requirements budget

Direct Labor budget

Overhead budget

Selling, General & Administrative expense budget

Cash budget

1. How to make a pro-forma income statement for the first quarter of 2024 in good form. (Income statement in the contribution format.) Disregard income taxes.

CHECK FIGURES

Total sales for the quarter	$ 7,402,300
Units to produce in March	9,400
Total material needs in February	$ 1,395,940
Total labor hours needed for the quarter	90,000
Total variable overhead in January	$ 166,600
Total variable SG&A for February	$ 105,000
Cash disbursements for January	$ 2,248,850
Ending cash balance in February	$ 50,310
Contribution margin for March	$ 230,250
Net Income for March	$ 97,450

TEMPLATE FOR TABLE

Columbo Company
Sales budget	Jan	Feb	Mar	Total	April	May
For the three months ended March 31, 2024
Sales in units	18,000	20,000	15,000	53,000
Selling price	$ 145.00	$ 145.00	$ 145.00	$ 145.00
Total	$ 2,610,000	$ 2,900,000	$ 2,175,000	$ 7,685,000	$ -	$ -
Collections of sales:
Month of sale		$ 2,610,000		$ 2,610,000
Month after sale		208,800		208,800
Total	$ -	$ 2,818,800	$ -	$ 2,818,800
Columbo Company
Production budget	Jan		Mar	Total	April	May
For the three months ended March 31, 2021
Sales in units	18,000	20,000	15,000	53,000
Add: ending inventory	14,400	16,000	12,000	26,400
Less: beginning inventory	=
Units to produce			27,000
Columbo Company
Material requirements budget	Jan	Feb	Mar	Total	April
For the three months ended March 31, 2021
Polyester:
Material requirements	-				-
Add: ending inventory
Less: beginning inventory
Material requirements				-
Cost per unit
Total cost requirements	$ -	$ -	$ -	$ -
Lining:
Material requirements	-				-
Add: ending inventory	-
Less: beginning inventory
Material requirements	-	-	-	-
Cost per unit
Total cost requirements	$ -	$ -	$ -	$ -
Total material needs	$ -	$ -	$ -	$ -
Columbo Company
Direct labor budget	Jan	Feb	Mar	Total
For the three months ended March 31, 2021
Units to produce			27,000	-
Required hours per unit
Total hours needed				-
Labor rate per hour
Total Labor cost	$ -	$ -	$ -	$ -
Columbo Company
Overhead budget	Jan	Feb	Mar	Total
For the three months ended March 31, 2021
Variable overhead:
Supplies		$ -
Utilities		-
Maintenance		-
Other		-
Total variable OH costs	$ -
Fixed overhead:
Maintenance
Wages & Benefits
Depreciation
Taxes
Other
Total Fixed OH Costs
Columbo Company
SG&A Budget	Jan	Feb	Mar	Total
For the three months ended March 31, 2021
Variable SG&A:
Quality	$ 54,000
Advertising	13,500
Other	27,000
Total variable SG&A costs		$ -
Fixed SG&A:
Salaries & Benefits
Depreciation
Other
Total fixed SG&A costs
Columbo Company
Cash Budget	Jan	Feb	Mar	Total
For the three months ended March 31, 2021
Cash receipts	$ -	$ 2,818,800	$ -	$ 2,818,800
Cash disbursements:
Cash required for materials				-
Cash required for Labor				-
Cash required for Fixed and Variable Overhead				-
Cash required for Fixed and Variable SG&A				-
Bank fees				-
Dividends				-
Taxes				-
Capital Equipment purchase				-
Total Disbursements	-	-	-	-
Cash excess/(deficiency)	-	2,818,800	-	2,818,800
Beginning cash				-
Bank borrowings				-
Bank repayments				-
Interest on bank borrowings				-
Ending cash	$ -	$ 2,818,800	$ -	$ 2,818,800
Columbo Company
Income statement	Jan	Feb	Mar	Total
For the three months ended March 31, 2021
Sales Revenue	$ 2,610,000	$ 2,900,000	$ 2,175,000	$ 7,685,000
Variable Costs:
Materials COGS				-
Labor COGS				-
Variable Overhead COGS				-
Variable SG&A				-
Bad Debt Expense				-
Contribution Margin	2,610,000	2,900,000	2,175,000	7,685,000
Contribution Margin %	100.00%	100.00%	100.00%	100.00%
Fixed costs:
Fixed Overhead				-
Fixed SG&A				-
Bank fees				-
Interest Expense				-
Net Income	$ 2,610,000	$ 2,900,000	$ 2,175,000	$ 7,685,000