Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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 Mothers Day with SPD being a favorite. Ending inventories are supposed to be equal to 90% of the next months 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 months 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. SPDs 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 months 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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Financial Accounting

Authors: Paul D. Kimmel, Jerry J. Weygandt, Donald E. Kieso

7th Edition

1118725786, 978-1118725788

More Books

Students also viewed these Accounting questions

Question

Describe reviewing applications and rsums.

Answered: 1 week ago

Question

Identify the uses of performance appraisal.

Answered: 1 week ago

Question

Discuss selection in a global environment.

Answered: 1 week ago