Cartwright Inc produces wiring harnesses used in semi-trailers. The wiring harnesses sales for the next 4 months is projected to be:

January 14,000

February 18,000

March 22,000

April 24,000

The following information pertains to the budget assumptions.

Finished goods inventory on January 1 is expected to be 5000 units. The desired ending FGI for any month (except for the Dec 31 inventory as noted previously) is expected to be 25% of the following months sales.

Data for materials used are:

Part A78 4 parts per unit $5 per part

Part D22 3 parts per unit $6 per part

Raw materials inventory is always budgeted to equal 45% of that months production

needs.

On March 31, the ending inventory of A78 is expected to be 40,000 parts and for D22 is

expected to be 30,000 parts.

Payments for Raw materials purchases are on the following schedules:

90% paid in the month of purchase and

10% in the month following purchase.

Accounts payable as of January 1 is $0.00.

Direct labor used per harness is 1.25 hours at a rate of $18/hr.

Overhead each month is estimated at:

Selling and Admin is estimated each to be:

Selling price per harness is $105.00.

Sales are all on account. 25% of sales are collected in the month of sale and 75% in the month following the sale. Accounts receivable on January 1 is $400,000.

February is a planned purchase of land for $237,500.

Borrowings are at 6% per year interest, and borrowings are assumed to be at the beginning of the month required and at the end of the month of repayment. Beginning cash is $100,000. Interest on borrowings is paid before any principal can be paid. Minimum cash balance at the end of any month is $10,000.

REQUIRED:

Please prepare the following budgets in an Excel spreadsheet for each month of the first quarter and for the quarter in total. Each budget is to be in a separate tab and there should be no numbers directly keyed into any budget. Please ensure each budget is properly formated so it can be pasted into excel.

Sales budget

Production budget

Direct materials purchases budget

Manufacturing cost budget

Selling and Admin budget

Cash Budget

Income Statement