Case Study $1$

MBA $6150$

Master Budgeting and Pro$-$Forma Financial Statements

You have just been assigned to a new manager who believes you have exceptional budgeting

skills. Since you began your job last summer, you have been showing management your latest

spreadsheets and how you use your new$-$found knowledge of Managerial Decision$-$Making to

make sound business decisions. Your new manager is responsible for the nationwide distribution

of the newly developed Self Protection Device $($SPD$),$ using an ultrasonic sound emission, DNA

material capture via a retractable claw, and an amazing $250,000$ lumen LED flashing light.

Through multiple franchise agreements, sales have grown very rapidly and the timing is right for

you to join her team and show your skills. You have just been given responsibility for all of the

planning and budgeting of the entire division. Your first assignment is to prepare a master budget

for the next three months, starting April $1,2024.$ You accept this responsibility with enthusiasm.

You are anxious to impress your new manager and the president of the parent company $($Allteq

Solutions$)$ who has a very high regard for you. To commence your new role, you have assembled

the following pertinent information:

Note: The company desires a minimum ending cash balance each month of $$12,000.$ The SPDs

are sold to retailers for $$30$ each and they are flying off the shelves. Recent forecasted sales in

units are provided below:

January $($actual$)20,000$ June $50,000$

February $($actual$)24,000$ July $40,000$

March $($actual$)28,000$ August $36,000$

April $38,000$ September $32,000$

May $65,000$

The increased sales volume before and during May is due to Mother$$s Day with SPD being a

favorite. Ending inventories are supposed to be equal to $90\%$ of the next month$$s sales in units.

The cost of each SPD is $$14.$

Purchases are paid for in the following manner: $50\%$ in the month of the purchase and the

remaining $50\%$ paid in the monthly following the purchase. All sales to the distributors are made

on credit terms with no discount $($for now$)$ and payable withing $15$ days. The SPD division has

determined that only $25\%$ of a month$$s sales are collected by the end of the month in which the

sale occurred. An additional $50\%$ is collected in the month following the sale and the remaining

$25\%$ is collected in the second month following the sale. Bad debts have been negligible,

supporting the favorable credit terms.

Below is a display of the SPD division monthly selling and administrative expenses:

Variable:

Sales

Commissions $ $1$ per SPD

Fixed:

Wages and

Salaries $$62,000$

Utilities $$41,000$

Insurance $$1,800$

Depreciation $$3,500$

Miscellaneous $$14,000$

Selling and administrative expenses are all paid during the month, in cash, with the exception of

depreciation $($of course$)$ and insurance is pre$-$paid for the duration of the policy. Due to a

violation of an existing copyright, lawyers for Allteq Solutions have negotiated a one$-$time

infringement penalty to be expensed and paid during May for $$350,000$ cash. The newly formed

SPD division contributes to the corporate dividend at a rate of $$175,000$ each quarter, payable in

the first month of following quarter. SPD$$s balance sheet at the end of the first quarter is shown

below:

Balance Sheet as of March $31,2024$

Assets

Cash $$44,000$

Accounts receivable $810,000$

Inventory $(34,200$

units$)478,800$

Prepaid insurance $16,200$

Fixed assets, net of

depreciation $472,700$

Total Assets $$1,821,700$

Liabilities and Stockholders Equity

Accounts payable $$259,000$

Dividends payable $175,000$

Capital Stock $750,000$

Retained earnings $637,700$

Total Liabilities and

Stockholders Equity $$1,821,700$

An agreement with East Rivers Bank allows you to borrow in increments of $$1,000$ at the

beginning of each month, up to a total loan amount of $$350,000.$ The interest rate on these loans

is $7\%$ annually $($pretty high considering market rates$),$ but the interest is not compounded,

meaning this is simple interest only. At quarter end, SPD would pay East Rivers Bank all of the

accumulated interest on the loan and as much of the balance of the loan as soon as possible

$($include $$1,000$ increments$)$ while retaining the minimum $$12,000$ cash balance.

Hint: use this Excel formula to determine the amount needed to borrow:

IF$($excess$/$deficit$<12,000,$ roundup$(-$excess$/$deficit $+12,000,-3,0)$ where $$excess$/$deficit$=$ the

cell address of where you compute each month$$s cash deficit or excess.

Required:

Prepare a master budget for the three months ending June $30,2024.$ Include the following budget

schedules and financial statements:

$1)$ Sales Budget by month and total for the quarter

$2)$ Schedule of expected cash collections from sales, by month and total

$3)$ Merchandise purchases budget in units and in dollars, by month and total

$4)$ Schedule of expected cash disbursements for merchandise purchases, by month and total

$5)$ Cash Budget, by month and total

$6)$ Prepare a budgeted Income Statement for the three