Wilmington University Master Budgeting and Pro Forma Financial Statements Case Study

User Generated

Xhznfvobl

Business Finance

Wilmington University

Description

Case Study 2 Spring 2 2020 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 Accounting to make sound business decisions. Your new manager is responsible for the nationwide distribution of the newly developed Personal Protection Device (PPD), using an ultrasonic sound emission and an amazing 25,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 to show your skills. You have just been given responsibility for all planning and budgeting of the entire PPD division. Your first assignment is to prepare a master budget for the next three months, starting April 1, 2019. You accept this responsibility with enthusiasm, and you are anxious to impress your new manager and the president of the company, 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 on $15,000. The PPD’s are sold to retailers for $18 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

Unformatted Attachment Preview

Case Study 2 Spring 2 2020 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 Accounting to make sound business decisions. Your new manager is responsible for the nationwide distribution of the newly developed Personal Protection Device (PPD), using an ultrasonic sound emission and an amazing 25,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 to show your skills. You have just been given responsibility for all planning and budgeting of the entire PPD division. Your first assignment is to prepare a master budget for the next three months, starting April 1, 2019. You accept this responsibility with enthusiasm, and you are anxious to impress your new manager and the president of the company, 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 on $15,000. The PPD’s are sold to retailers for $18 each and they are flying off the shelves. Recent forecasted sales in units are provided below: January (actual) February (actual) March (actual) April May 20,000 24,000 28,000 38,000 65,000 June July August September 50,000 40,000 36,000 32,000 The increased sales volume before and during May is due to Mother’s Day with PPD being a favorite. Ending inventories are supposed to be equal to 90% of the next month’s sales in units. The cost of each PPD is $8.00. Purchases are paid for in the following manner: 50% in the month of the purchase and the remaining 50% paid in the month following the purchase. All sales to the distributors are made on credit terms with no discount (for now), and payable within 15 days. The PPD 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 credit terms as favorable. Below is a display of the PPD division monthly selling and administrative expenses: Variable: Sales Commissions Fixed: $ 1 per PPD Wages and Salaries Utilities Insurance Depreciation Miscellaneous $62,000 $41,000 $1,800 $3,500 $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 the creation of toxic waste during production of the PPD, a one-time disposal fee will be paid during May for $215,000 cash. PPD contributes to the corporate dividend at a rate of $25,000 each quarter, payable in the first month of the following quarter. PPD’s balance sheet at the end of the first quarter is shown below: Balance Sheet as of March 31, 2019 Assets Cash Accounts receivable ($108,000 February sales: $378,000 March sales) Inventory (34,200 units) Prepaid insurance Fixed assets, net of depreciation Total Assets $44,000 486,000 273,600 16,200 472,700 $1,292,500 Liabilities and Stockholders Equity Accounts payable $148,000 Dividends payable 25,000 Capital Stock 750,000 Retained earnings 369,500 Total Liabilities and Stockholders Equity $1,292,500 An agreement with East Rivers Bank allows PPD 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 10% annually (pretty high considering market rates) but the interest is not compounded, meaning this is simple interest only. At quarter end, PPD would pay East Rivers Bank of the all of the accumulated interest on the loan and as much of the balance of the loan as possible (in $1,000 increments) while retaining the minimum $20,000 cash balance. Required: Prepare a master budget for the three- months ending June 30, 2019. 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. Show the budget by month and total. 4) Schedule of expected cash disbursements for merchandise purchases, by month and total. 5) Cash Budget. Show the cash budget by month and in total. 6) Prepare a budgeted Income Statement for the three months ending June 30, 2019. Use the contribution approach. 7) Prepare a budgeted Balance Sheet as of June 30, 2019. 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, no longer than 2 pages, describing your experience from this exercise, and your thoughts on the value of having a Master Budget.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

Running head: MASTER BUDGET AND PRO-FORMA STATEMENTS

Master budget and Pro-forma statements
Name
Institution

1

MASTER BUDGET AND PRO-FORMA STATEMENTS

2

1. Sales budget
sales budget for the quarter ending 30th June

month

April

May

June

total for quarter

budgeted sales

38000

65000

50000

153000

selling price

$

total sales

$ 684,000.00

18.00

2. Cash collections by month

$
$

18.00
1,170,000.00

$

18.00

$ 900,000.00

$

2,754,000.00

MASTER BUDGET AND PRO-FORMA STATEMENTS

3

sales budget for the quarter ending 30th June
month
budgeted sales

April

May

June

total sales

38000
$
$
18.00
18.00
$
$
684,000.00
1,170,000.00

collections made that month(25%)

$
171,000.00

$
292,500.00
$
342,000.00

$
225,000.00
$
585,000.00
$
171,000.00

$
171,000.00

$
634,500.00

$
981,000.00

selling price

collections made next month (50%)
collections made in the third month
(25%)

total cashcollected by month from sales

65000

total for quarter
50000
153000

$
18.00
$
900,000.00

$
2,754,000.00

$
1,786,500.00

sales budget for the quarter ending 30th June

month
budgeted sales
selling price
total sales

collections made that month(25%)
collections made next month (50%)

April

May
38000

June
65000

total for quarter
50000
153000

$
18.00
$
684,000.00

$
18.00
$
1,170,000.00

$
18.00
$
900,000.00

$
171,000.00

$
292,500.00
$

$
225,...


Anonymous
Goes above and beyond expectations!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags