Master Budget Project Instructions

Learning Objectives

Prepare a fully referenced worksheet using absolute and relative references where appropriate.

Demonstrate an understanding of how the budget flows and consolidates.

Prepare financial statements from a budget.

Prepare a summary cash budget.

Project

Universal Electronics is a small, rapidly growing wholesaler of consumer electronic products. The firm$$s main product lines are small kitchen appliances and power tools. The general manager of marketing has completed the sales forecast. She believes the sales during the first quarter of $20$x$3$ will increase by $10\%$ each month over the previous month$$s sales. Then she expects sales to remain constant over the next several months. The company$$s projected balance sheet as of December $31,20$x$2$ is as follows:

Cash

$$28,000$

Accounts receivable

$276,000$

Marketable securities

$16,000$

Inventory

$154,000$

Buildings and equipment

$626,000$

Total assets

$ $1,100,000$

Accounts payable

$ $176,400$

Bond interest payable

$12,500$

Property taxes payable

$3,600$

Bonds payable $(10\%$ due $20$x$6)$

$300,000$

Common Stock

$500,000$

Retained earnings

$107,500$

Total liabilities and stockholder$$s equity

$$1,100,000$

Denise Gelia, the controller, is now preparing the monthly budget for the first quarter of $20$x$1.$ In the process, the following information has been accumulated:

$1.$ Projected sales for December $20$x$2$ are $$400,000.$ Credit sales are typically $75\%$ of the total sales. The company$$s credit experience indicates that $10\%$ of the credit sales is collected during the month of the sale, and the remainder collected the following month.

$2.$ The company$$s cost of goods sold is generally $70\%$ of sales. Inventory is purchased on account and $40\%$ of each month$$s purchases are paid during the month of the purchase. The remainder is paid during the following month. To have adequate stocks of inventory on hand, the firm attempts to have inventory at the end of each month equal to half of the next month$$s projected cost of goods sold.

$3.$ Denise has estimated the Universal Electronics$$ other monthly expenses will be as follows:

Sales salaries

$$21,000$

Advertising and promotion

$$16,000$

Administrative salaries

$$21,000$

Depreciation

$$25,000$

Interest on bonds

$$2,500$

Property taxes

$$900$

In addition, sales commissions run at the rate of $1\mathrm{.}0\%$ of sales.

$4.$ Universal Electronics president Reggie Shouse has indicated that the firm should invest $$125,000$ in an automated inventory handling system to control the movement of inventory in the firm$$s warehouse just after the new year begins. The equipment purchase will be funded primarily from the firm$$s cash and marketable securities$.$ However, Shouse believes that the company should keep a minimum cash balance of $$19,000.$ If necessary, the remainder of the equipment purchases will be financed using short$-$term credit from a local bank. The minimum period for such a loan is three months. Dana believes that the short$-$term interest rates will be $10\%$ per year at the time of the equipment purchases. If a loan is necessary, Shouse has decided it should be paid off by the end of the first quarter, if possible.

$5.$ Universal Electronics$$ board of directors has indicated its intention to declare and pay dividends of $$50,000$ on the last day of the first quarter, if possible.

$6.$ The interest on any short$-$term borrowing will be paid when the loan is repaid. Interest on the company$$s bonds is paid semi$-$annually on January $31$ and July $31$ for the preceding six$-$month period.

$7.$ Property taxes are paid semiannually on February $28$ and August $31$ for the preceding six$-$month period. $.$ Summary cash budget I need

Prepare Universal Electronics$$ master budget for the first quarter of $2023$ using the template provided in Canvas.The yeilow cells are "data input" ceils in which facts, assumptions or estimates

are to be entered. "The blue cells are "relationship" cells in which formulas are

$0$ be entered. The green cell are facts, assumptions or estimates from

data input" cells.