$\backslash$table$[[$Budget Template $-,,,,],[$Student Name,,,,$],[$Information Given in the Problem:,,,,$],[,,,,],[$Company Name,April,May,June,July$],[$Sales Information:,,,,$],[,,,,],[$Unit Sales,,,,$],[$Sales Price,,,,$],[$Cash Sales $(\%),,,,],[$Credit $($A$/$R$)$ percent,,,,$],[,,,,],[$A$/$R Balance,,,,$],[,,,,],[$Cost and Inventory Information:,,,,$],[$Desired El $(\%),,,,],[$Inventory $-$ Balance $-$ Units,,,,$],[,,,,],[$Product Cost,,,,$],[$Paid Month of Purchase $(\%),,,,],[$Paid month following Purchase $(\%),,,,],[,,,,],[$A$/$P Balance,,,,$],[,,,,],[$Selling and Admin Cost:,,,,$],[$Commission $(\%),,,,],[$Shipping $(\%),,,,],[$Office Salaries $($Amount$),,,,],[$Rent $($Amount$),,,,],[$Depreciation Expense $($Amount$),,,,],[$Budget Templates,,,,$],[(,,,,]]$ Budgets Excel

Master Budgets and Flexible Budgets

$1)$ Anda Enterprises is preparing its master budget for the quarter ended June $30$

Sales Information

The company sells a single product for $$50$ each.

Sales are split between $20\%$ cash and $80\%$ on credit $($A$/$R$).$

All credit sales are collected in the following month.

The Accounts Receivable balance on March $31$ is $$12,000$

Budgeted sales are as follows:

April May June July

Unit sales $9007008001000$

Product Cost and Inventory Information

The product cost is $$30$ per unit.

The desired ending inventory is $45\%$ of the next month$$s sales in units.

Inventory in units for March $31$: $360$ units

Purchases are paid $60\%$ in the month of the purchase and $40\%$ the next month.

The Accounts Payable Balance on March $31$ is $$5,000$

Operating Expense Information

Commission $(3\%$ of Sales$)$

Shipping $(2\%$ of Sales$)$

Office Salaries $-$ $$2000$ per month

Rent Expense $-$ $$1500$ per month

Depreciation Expense $-$ $$2500$ per month

Prepare the following budgets for the months of April, May and June $(2$nd Qtr Total Budget$)$

$($You must use excel to format the budgets and you must use Excel formulas to complete this portion

of the exam$)$

a$)$ Sales Budget

b$)$ Table of Cash Receipts

c$)$ Merchandise Purchase Budget

d$)$ Table of cash disbursement for Purchase of merchandise $($Product Cost$)$

e$)$ Table of cash disbursements for selling and administrative expenses

HINT: Cash Disbursements $$ means cash going out

Exam $3-$ Budgets Excel Portion of the Exam Fall $24\_$J$10$

Master Budgets and Flexible Budgets

$2)$ Dewle, Inc$$s static budget for the year is shown below:

Sales $(25,000$ units$)$ $$1,400,000$

Cost of Goods Sold:

Direct Material $$140,000$

Direct Labor $300,000$

Overhead $($includes $$3$ per unit Variable cost$)165,000605,000$

Gross Profit $795,000$

Selling Expenses

Sales Commissions $($Variable$)40,000$

Rent $($Fixed$)90,000$

Insurance $($Fixed$)45,000$

General and administrative expenses

Salaries $($fixed$)190,000$

Rent $($Fixed$)60,000$

Depreciation $($Fixed$)70,000495,000$

$ $300,000$

Prepare a flexible budget for Rashad Inc. that shows a detailed budget for its static budget and its actual

sales volume of $30,000$ units. Use the contribution margin approach.

$25,000$ units $30,000$ units

Sales

Variable Cost:

Direct Materials

Direct Labor

Variable OH

Sales Commission

Total Variable Cost

Contribution Margin

Fixed Cost:

Fixed OH

Rent Expense $$ Selling

Insurance Expense

Salaries Expense

Rent Expense $$ General

Depreciation Expense

Total Fixed Cost

Income from Operations