Question

Case Study 1 MBA 6150 Fall B-1 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 Self Protection Device (SPD), using an ultrasonic sound emission, DNA material capture via retractable claw, 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 division. Your first assignment is to prepare a master budget for the next three months, starting April 1, 2020. You accept this responsibility with enthusiasm, and 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 on $12,000. The SPD's are sold to retailers for $22 each and they are flying off the shelves. Recent forecasted sales in units are provided below: June July January (actual) February (actual) March (actual) April May 20,000 24.000 28,000 38,000 65,000 50,000 40,000 36.000 32,000 August September

Answer 1

Solution:

Requirement 1 Sales Budget For the Three Months Ended June 30, 2020 April May June Quarter Estimated Sales (Units) 38,000 65,000 50,000 1,53,000 (x) Selling Price $ 22.00 $ 22.00 $ 22.00 Estimated Sales (Dollars) $ 8,36,000 $ 14,30,000 $ 11,00,000 $33,66,000 Requirement 2 Schedule of Expected Cash Collections from Sales For the Three Months Ended June 30, 2020 April May June Quarter February $ 1,32,000 $ 1,32,000 March $ 3,08,000 $ 1,54,000 $ 4,62,000 April $ 2,09,000 $ 4,18,000 $ 2,09,000 $ 8,36,000 May $ 3,57,500 $ 7,15,000 $ 10,72,500 June $ 2,75,000 $ 2,75,000 Total Cash Collection from Sales $ 6,49,000 $ 9,29,500 $ 11,99,000 $ 27,77,500

April Requirement 3 Merchandise Purchases Budget For the Three Months Ended June 30, 2020 May June Quarter Estimated Sales (Units) 38,000 65,000 50,000 1,53,000 Add: Desired Ending Inventory 58,500 45,000 36,000 36,000 Total Inventory Needed 96,500 1,10,000 86,000 1,89,000 Less: Beginning Inventory 34,200 58,500 45,000 34,200 Merchandise Purchases (Units) 62,300 51,500 41,000 1,54,800 (x) Cost of each SPD $ 9.00 $ 9.00 $ 9.00 Merchandise Purchases (Dollars) $ 5,60,700 $ 4,63,500 $ 3,69,000 $ 13,93,200 Requirement 4 Schedule of Expected Cash Disbursement for Merchadise Purchases For the Three Months Ended June 30, 2020 April May June Quarter Payment to Accs Payable (March) $ 1,66,500 $ 1,66,500 April $ 2,80,350 $ 2,80,350 $ 5,60,700 May $ 2,13,750 $ 2,13,750 $ 4,27,500 June $ 1,84,500 $ 1,84,500 Total Cash Disbursement for Purchases $ 4,46,850 $ 4,94,100 $ 3,98,250 $ 13,39,200