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:

$$ Your team is to use Excel to create your Master Budget. Start off by creating a $$Data$$ tab that will house ALL your budgeting data. Your Data tab will be the only tab where you can actually manually input data. You are to create a separate tab for each required budget. Use cell referencing to transfer amounts from the Data tab to the other respective tabs$.$ You must ensure that your budgets have a professional appearance including proper labels, headings, spacing and subtotal lines. Make sure that your formats are consistent, i$.$e$.,$ rounding, use of the $ sign... Consider use of shading$/$color to make your budgets more eye appealing and easy to read.

$$ You must also include a written analysis where you discuss each budget$$s function, as well as the financial results of each budget. Be thorough, discuss monthly & quarter results. Your analysis should be in Microsoft Word. Take the perspective that Roberts knows nothing about budgets and that you are attempting to educate them, as well as communicate the results of their budget.

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$)$ Budgeted Sales: March: $3300/$April:$2800/$May:$3900/$June:$5100/$July:$4100/$August:$1600$