The ABC Company manufactures a solid state disk drive that

it sells to computer companies in North America. Assume the date is January $5,2024$ and that the company is preparing its master budget for the first quarter

of $2024.$

Instructions:

You will use Microsoft Excel to prepare the Quarter $1,2024$ master budget for the company.Please prepare the following schedule in the same order as listed below:

$1.[$Opening balance sheet$]$

$2.$ Sales budget

$3.$ Schedule of expected cash collections

$4.$ Production budget

$5.$ Direct materials budget

$6.$ Schedule of cash payments

$7.$ Direct labor budget

$8.$ Manufacturing overhead budget

$9.$ Ending finished goods inventory budget

$10.$ Selling and administrative budget

$11.$ Cash budget

$12.$ Cost of goods manufactured budget

$13.$ Budgeted income statement$*$

$14.$ Budgeted statement of retained earnings

$15.$ Budgeted balance sheet

$*$ with a cost of goods sold computation embedded within.

All budgets and schedules must be for each month and include a total for the quarter column EXCEPT for items $12$ through $14$ which will just be for the entire quarter and item $15$ which will be at March $31,2024.$

Data for project

In addition to the opening $($December $31,2023)$ balance sheet, use the following data to complete the project:

$2021$:

Forecast sales $($in disks$)$

Selling price per disk $=10,000\mathrm{.}0$

Jan $8,500$

Feb $25,000$

Mar $15,300$

Apr $18,000$

May $22,500$

Selling price per disk $=$ $$75$

Assumption about collection of Dec. $31/23$ Accts. Receivable: $100\%$ collected in Jan. $/24$

Collection assumptions re budgeted sales:

collected in month of sale $60\%$

collected in month following $30\%$

collected in second month after the sale $10\%$

Desired ending finished goods inventory in units..... $20\%$ of following month's budgeted sales in disks.

Actual inventory from Dec. $31/23$ year$-$end info............ $2,270$ disks

Raw materials cost per disk........... $$15$

As per the December $31,2023$ balance sheet, the actual raw materials

inventory is $$19,100.$

Desired raw materials ending inventory level is: $10\%$ of the

following month's production needs $($in dollars$)$

Expected payment of AP$...............100\%$ paid in Jan. $/24$

Payment pattern for budgeted raw materials purchases:

$40\%$ in the month of purchase

$60\%$ in the month following

Budgeted direct labor hours to make $1$ disk........ $0\mathrm{.}8$ hours

Budgeted direct labor cost per hour:.................. $ $24$

Manufacturing overhead per month:

The fixed overhead is budgeted at $...$ $ $181,000$ per month and includes depreciation on manufacturing assets of$...........$ $ $34,000$

Variable manufacturing overhead is estimated at $$8$ per direct labor hour.

As the last part of your manufacturing overhead budget, compute a predetermined overhead rate based on the budgeted quarter time period $($absorption costing method$).$ It will be a rate per direct labor hour.

Variable selling and administrative expense per unit sold..... $ $5\mathrm{.}00$

Budgeted fixed Selling and administrative per month are:

Advertising..................................... $ $60,000$

Administrative salaries..................... $135,000$

Office depreciation exp..................... $41,000$

Insurance........................................ $???$

$*$ A $1-$year insurance policy will be purchased for cash on January $1.$

The amount paid is$.......................27600$

Other budgeted expenditures, paid in cash:

Equipment purchase in Feb. $ $123,000$

Dividends to be paid in Feb. $ $55,000$

Financing section of Cash Budget:

A short$-$term demand loan is automatically arranged to cover any cash deficiency below the minimum required cash balance. These borrowed amounts are reflected in the "Short$-$term bank loan" account on the balance sheet. Amounts borrowed are assumed to be borrowed at the beginning of the month. All repayments are assumed to have been made at the end of the month. Interest is calculated and paid only on the amount repaid, and at the time repaid.

Borrowings and repayments are all in multiples of $$1,000.$

Maintain a minimum cash balance of$.................$ $ $45,000$

Interest rate per year...................... $10\%$

$($Round interest amounts to the nearest full dollar$)$ OPENING BALANCE SHEET$-$ Balance Sheet

December $31,2023$

ASSETS

Current assets:

Cash $$102,011$

Accounts receivable $360,000$

Raw materials inventory $($a$)19,100$

Finished goods inventory $($b$)116,7502,270$ disks @ $$51\mathrm{.}4317$

Total current assets $597,861$

Capital assets:

Land $240,000$

Buildings and equipment $($c$)2,000,000$

Less: Accumulated depreciation $(800,000)$

Capital assets, net $1,440,000$

Total assets $$2,037,861$

LIABILITIES AND SHAREHOLDERS' EQUITY

Current liabilities:

Short$-$term bank loan $$12,000($Full amount was borrowed on December $31,2021)$

Accounts payable $91,200$

Total current liabilities $103,200$

Shareholders' equity:

Common shares $640,000$

Retained earnings $1,294,661$

Total shareholders' equity $1,934,661$

Total liabilities and shareho