Information for Inforcart Budgets Infocart produces technology software The software sales for the next 5 months are projected to be January 15,000 February 18,000 March 21,000 April 24,000 May 26,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 17 of the following months sales Data for materials used are Part A87 4 parts per unit $5 60 per part Part D33 3 parts per unit $4 75 per part Raw materials ending inventory is always budgeted to equal 22 of the following months production needs On March 31, the ending inventory of A87 is expected to be 40,000 parts and for D33 is expected to be 30,000 parts Payments for Raw materials purchases are on the following schedules 20 paid in the month of purchase and 80 in the month following purchase Accounts payable as of January 1 is $0 00 Direct labor used per harness is 1 5 hours at a rate of $15 hr Overhead each month is estimated at Selling and Admin is estimated each to be Selling price per software is $110 00 Sales are all on account 40 of sales are collected in the month of sale and 60 in the month following the sale Accounts receivable on January 1 is $30,000 February is a planned purchase of equipment for $435,000 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 January 1 beginning cash is $10,000 Interest on borrowings is paid before any principal can be paid To the extent possible, the company strives to repay any debt at the end of each month if there is cash available 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 Sales budget 2 Production budget 3 Direct materials purchases budget Direct labor budget 5 Overhead budget 6 Selling and Admin budget Cash Budget 8 Income Statement e Overhead each month is estimated at Overhead estimates Fixed portion (per month) variable cost ($ per DL 1 02 Supplies Power Maintenance Supervision Depreciation Taxes Other 12500 14000 45000 4300 86000 f Selling and Admin is estimated each to be Selling and admin per month variable cost (S per unit sold (cost driver) Fixed portion (per month) 30000 14 500D Salaries Commissions Depreciation Shipping Other 2 6 13000 0 6

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 18, 2024

Information for Inforcart Budgets. Infocart produces technology software. The software sales for the next 5 months are projected to be: January 15,000 February 18,000 March

Information for Inforcart Budgets.

Infocart produces technology software. The software sales for the next 5 months are projected to be:

January 15,000

February 18,000

March 21,000

April 24,000

May 26,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 17% of the following months sales.
Data for materials used are:

Part A87 4 parts per unit $5.60 per part

Part D33 3 parts per unit $4.75 per part

Raw materials ending inventory is always budgeted to equal 22% of the following months

production needs.

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

expected to be 30,000 parts.

Payments for Raw materials purchases are on the following schedules:

20% paid in the month of purchase and

80% in the month following purchase.

Accounts payable as of January 1 is $0.00.

Direct labor used per harness is 1.5 hours at a rate of $15/hr.

Overhead each month is estimated at:
Selling and Admin is estimated each to be:

Selling price per software is $110.00.
Sales are all on account. 40% of sales are collected in the month of sale and 60% in the month following the sale. Accounts receivable on January 1 is $30,000.
February is a planned purchase of equipment for $435,000.
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. January 1 beginning cash is $10,000. Interest on borrowings is paid before any principal can be paid. To the extent possible, the company strives to repay any debt at the end of each month if there is cash available. 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.

Sales budget 2. Production budget 3. Direct materials purchases budget

Direct labor budget 5. Overhead budget 6. Selling and Admin budget

Cash Budget 8. Income Statement

e Overhead each month is estimated at: Overhead estimates Fixed portion (per month) variable cost ($ per DL 1 02 Supplies Power Maintenance Supervision Depreciation Taxes Other 12500 14000 45000 4300 86000 f. Selling and Admin is estimated each to be: Selling and admin per month variable cost (S per unit sold (cost driver) Fixed portion (per month) 30000 14 500D Salaries Commissions Depreciation Shipping Other 2.6 13000 0.6