Project

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.

To get started, do the following:

Download the Excel file: BudgetStarterFile.xlsx

Immediately save it under a new filename:

"SmithJBudget", for example, if your name were John Smith.

This newly$-$saved file is the one you will work in to do your project.

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:

$10,000\mathrm{.}0$

$2021$: Jan Feb Mar Apr May

Forecast sales $($in disks$)8,50025,00015,30018,00022,500$

Selling price per disk $=75$ $

$ $75$

Assumption about collection of Dec. $31/23$ Accts. Receivable:

$100\%$ collected in Jan. $/24$

Collection assumptions re

budgeted sales: $60\%$ collected in month of sale

$30\%$ collected in month following

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

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\mathrm{.}00$ $

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$.......................27,600$

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\%$