You have just been assigned to a new manager who believes you have exceptional budgeting skills. Since you began your jo

[answerhappygod]

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, 2022. 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, 2022
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, 2022.
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 months ending
June 30, 2022. Use the
contribution approach.
7) Prepare a budgeted Balance Sheet as of Jun 30, 2022.
8) Your new manager has asked for your explanation about your
experience in preparing this
budget. She specifically wants to know your thoughts on the value
of all this work and whether
the benefits from the work are worth the effort. Prepare a memo
(with a heading), no longer than
2 pages, describing your experience from this exercise and your
thoughts on the value of having
a Master Budget.