You and your team have been hired to prepare a partial Master Budget for Roberts Inc. for Q$22025($April$,$ May & June.$)$ Your team has also been asked to provide a thorough analysis of the results of each budget.

Part $1$:

Prepare the following elements of the Master Budget within Excel. Information needed to prepare the budgets follows below.

$1.$ Sales budget

$2.$ Budgeted Cash Receipts budget

$3.$ Production budget

$4.$ Direct Materials budget

$5.$ Budgeted Cash Payments for Direct Material budget

$6.$ Direct Labor budget

$7.$ Manufacturing Overhead budget, including the POHR

$8.$ Cost of Goods Sold budget including unit product cost as well as total COGS

$9.$ Selling and Administrative Expense budget

$10.$ Cash budget

$11.$ Budgeted Multi Step Income Statement for the quarter ending June $30,2025$; assume $30\%$ income tax rate

$12.$ Variance against Actuals Report $($Part $2)$

REQUIRED:

Budgeting Information:

Make sure you include a $$Total Q$2$ column for EACH budget.

$1.$ Budgeted sales units will be distributed separately for each team.

$2.$ The selling price is $$50$ per unit.

$3.$ All sales are on account.

$4.$ The company collects $80\%$ of these credit sales in the month of the sale; $15\%$ are collected in the month following sale; and the remaining $5\%$ are uncollectible.

$5.$ The accounts receivable balance on March $31,2025$ was $$22,500.$ All of this balance was collected in April.

$6.$ The company desires to have production inventory on hand at the end of each month equal to $25\%$ of the following month$$s budgeted unit sales.

$7.5$ pounds of material are required per unit produced.

$8.$ Management desires to have materials on hand at the end of each month equal to $10\%$ of the following month$$s production needs.

$9.$ The material costs $$0\mathrm{.}25$ per pound.

$10.$ The desired ending materials inventory for June $2025$ is $1,325$ pounds, $($this amount is given and does not need to be calculated.$)$

$11.60\%$ of a month$$s direct material purchases are paid for in the month of purchase; the other $40\%$ is paid for in the following month.

$12.$ The accounts payable balance on March $31,2025$ was $$10,000$ and will be paid fully in April.

$13.$ Each unit produced requires $45$ minutes of direct labor time.

$14.$ Each hour of direct labor time costs the company $$10.$

$15.$ Variable manufacturing overhead is $$15$ per direct labor hour.

$16.$ Fixed manufacturing overhead is $$35,000$ per month, which includes $$15,000$ of depreciation.

$17.$ Manufacturing overhead is applied on the basis of direct labor hours.

$18.$ Variable selling and administrative expenses are $$0\mathrm{.}30$ per unit sold.

$19.$ Fixed selling and administrative expenses are $$40,000$ per month and includes $$8,000$ of depreciation.

$20.$ A line of credit is available at a local bank that allows the company to borrow up to $$50,000.$

a$.$ All borrowing occurs at the beginning of the month and all repayments occur at the end of June.

b$.$ The interest rate is $1\mathrm{.}5\%$ per month.

$21.$ Roberts Inc. desires a cash balance of at least $$50,000$ at the end of each month. The cash balance at the beginning of April was $$60,000.$

$22.$ Cash dividends of $$40,000$ will be paid to stockholders in April.

$23.$ Equipment purchases of $$30,000$ are scheduled for June.

Part $2$:

After the end of the $2$nd quarter of $2025,$ Roberts Inc. wants you to evaluate their budget versus their actual results. The company reported the following actual results for the $2$nd quarter of $2025$:

Sales $$507,500$

Cost of Goods Sold $$302,500$

Gross Margin $$205,000$

Selling and Administrative Expenses $$125,000$

Net Operating Income $$80,000$

Interest Expense $$1,500$

Income before Income Tax $$78,500$

Income Tax Expense $(30\%)$ $$23,550$

Net Income $$54,950$

Required: Prepare a $$Variance against Actuals$$ report comparing the budgeted income statement to the actuals above. Label each variance as either favorable $($F$)$ or unfavorable $($U$).$ You are to create your performance report, also in Excel, as a separate tab along with all your other budgets. Include the actuals on your Data tab and continue to use cell referencing. You must also include a discussion of your variance report within your written document.

Comment on the following:

$$ Discuss the Variances against Actual results

$$ How did Roberts do in estimating their Q$2$ data...address certain assumptions that they made that caused the largest variances

$$ What changes would you recommend for the Q$32025$ budget

$$ Suggest ways that Roberts could budget better in the future

Answer rating: 100% (QA)

The Answer is in the image, click to view ... View the full answer