Master Budget Project Information to be used in preparation of master budget: (N
ID: 2786770 • Letter: M
Question
Master Budget Project
Information to be used in preparation of master budget:
(Note: the third quarter begins on July 1)
Ryan Richards, controller for Grange Retailers, has assembled the following data to assist in the preparation of a cash budget for the third quarter of 2016:
Sales:
May (actual) $100,000
June (actual) 120,000
July (estimated) 90,000
August (estimated) 100,000
September (estimated) 135,000
October (estimated) 110,000
Each month, 30 percent of sales are for cash and 70 percent are on credit. The collection pattern for credit sales is 20 percent in the month of sale, 50 percent in the following month, and 30 percent in the second month following the sale.
Each month, the ending inventory exactly equals 50 percent of the cost of next month’s sales. The markup on goods is 25 percent of cost.
Inventory purchases are paid for in the month following the purchase.
Recurring monthly expenses are as follows:
Salaries and wages $10,000
Depreciation on plant and equipment 4,000
Utilities 1,000
Other 1,700
Property taxes of $15,000 are due and payable on July 15, 2016.
Advertising fees of $6,000 must be paid on August 20, 2016.
A lease on a new storage facility is scheduled to begin on September 2, 2016. Monthly payments are $5,000.
The company has a policy to maintain a minimum cash balance of $10,000. If necessary, it will borrow to meet its short-term needs. All borrowing is done at the beginning of the month. All payments on principal and interest are made at the end of a month. The annual interest rate is 9 percent. The company must borrow in multiples of $1,000.
A partially completed balance sheet as of June 30, 2016, follows. (Accounts payable is for inventory purchases only.)
Cash $ ?
Accounts receivable ?
Inventory ?
Plant and equipment 425,000
Accounts payable $ ?
Common stock 210,000
Retained earnings ________ 268,750
Total $ ? $ ?
======== ========
Requirements
PART 1:
Complete the balance sheet given in item j.
PART 2:
Prepare a pro-forma balance sheet as of September 30, 2016.
PART 3:
Provide 3 different ways in which the company may increase net income. In fulfilling this requirement, identify and explain each concept used, and provide pro-forma excel spreadsheets to support your recommendations.
Explanation / Answer
Ryan Richards, controller for Grange Retailers, Month Sales COGS=80% of Sales June Purchases May $ 100,000.00 $ 80,000.00 Opening Inventory=96000*50%=48000 June $ 120,000.00 $ 96,000.00 Closing Inventory=(72000*50%)=36000 July $ 90,000.00 $ 72,000.00 Cost of goods sold=96000 Aug $ 100,000.00 $ 80,000.00 Purchases=96000+36000-48000=84000 Sept $ 135,000.00 $ 108,000.00 Oct $ 110,000.00 $ 88,000.00 Sales Budget July Aug Sep Quarter Total Sales $ 90,000.00 $ 100,000.00 $ 135,000.00 $ 325,000.00 Schedule of expected cash collection July Aug Sep Quarter May sales=($100000*70%)*30% $ 21,000.00 $ 21,000.00 June Sales=($120000*70%)*50% in July and ($120000*70%)*30% in Aug $ 42,000.00 $ 25,200.00 $ 67,200.00 July Sales=($90000*30%) in July,($90000*70%)*20% in July,($90000*70%)*50% in Aug,($90000*70%)*30% in Sep $ 39,600.00 $ 31,500.00 $ 18,900.00 $ 90,000.00 Aug Sales=($100000*30%) in aug,($100000*70%)*20% in Aug,($100000*70%)*50% in Sep $ 44,000.00 $ 35,000.00 $ 79,000.00 Sep Sales=($135000*30%) in Sep,($135000*70%)*20% in Sep $ 59,400.00 $ 59,400.00 Total Cash collection $ 102,600.00 $ 100,700.00 $ 113,300.00 $ 316,600.00 Schedule of Mercendise Purchase Budget July Aug Sep Quarter Budgeted Unit Sales $ 90,000.00 $ 100,000.00 $ 135,000.00 $ 325,000.00 Cost of goods sold $ 72,000.00 $ 80,000.00 $ 108,000.00 $ 260,000.00 Add: Desired ending inventory $ 40,000.00 $ 54,000.00 $ 44,000.00 $ 44,000.00 Total Needs $ 112,000.00 $ 134,000.00 $ 152,000.00 $ 304,000.00 Less: Beginning Inventory $ 36,000.00 $ 40,000.00 $ 54,000.00 $ 36,000.00 Required Purchases $ 76,000.00 $ 94,000.00 $ 98,000.00 $ 268,000.00 Schedule of Budgeted cash disbursement for mercendise purchases July Aug Sep Quarter Accounts Payable (June Purchase =$84000 $ 84,000.00 $ 84,000.00 July Purchase $ - $ 76,000.00 $ 76,000.00 Aug Purchase $ - $ 94,000.00 $ 94,000.00 Sep Purchase $ - $ - Total Cash payment $ 84,000.00 $ 76,000.00 $ 94,000.00 $ 254,000.00 Schedule of Cash Budget For the three months ending june 30th July Aug Sep Quarter Cash Balance $ 13,550.00 $ 19,450.00 $ 31,450.00 $ 13,550.00 Add: Collection from customers $ 102,600.00 $ 100,700.00 $ 113,300.00 $ 316,600.00 Total cash available $ 116,150.00 $ 120,150.00 $ 144,750.00 $ 330,150.00 Less: Disbursement Mercendise Purchases $ 84,000.00 $ 76,000.00 $ 94,000.00 $ 254,000.00 Salaries $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 30,000.00 Utilities $ 1,000.00 $ 1,000.00 $ 1,000.00 $ 3,000.00 Others $ 1,700.00 $ 1,700.00 $ 1,700.00 $ 5,100.00 Total Cash Disbursement $ 96,700.00 $ 88,700.00 $ 106,700.00 $ 292,100.00 Excess(Deficiency) of receipts over payments $ 19,450.00 $ 31,450.00 $ 38,050.00 $ 38,050.00 Cash balance at the year end $ 19,450.00 $ 31,450.00 $ 38,050.00 Budgeted Income Statement For the three months ending Sep 30th Sales $ 325,000.00 Variable cost Cost of goods sold $260,000 260000 Gross Profit $ 65,000.00 Salaries & Wages=(10000*3) $ 30,000.00 Utilitiies $ 3,000.00 Others $ 5,100.00 Depreciation $ 12,000.00 $ 50,100.00 Net Operating Income $ 14,900.00 Budgeted Balance Sheet Sep 30th Assets Cash $ 38,050.00 Accounts Receivable $ 96,600.00 Inventory 44000 Property & Equipment $ 413,000.00 Total Assets $ 591,650.00 Liabilities & Stockholder's Equity Accounts Payable $ 98,000.00 Capital Stock $ 210,000.00 Retained Earnings $ 283,650.00 Total Liabilities & Stockholder's Equity $ 591,650.00 Cost of goods sold=Opening Inventory+Purchases-Closing Inventory Cost 100 Gross Profit 25 Sales 125 Gross Profit Ratio=(25/125)*100= 20 % Balance Sheet of June 30th 2016 Cash(Balancing figure) $ 13,550.00 Accounts Receivable=($21000+$42000+$25200) $ 88,200.00 Inventory $ 36,000.00 Plant & Equipment $ 425,000.00 Accounts Payable $ 84,000.00 Common Stock $ 210,000.00 Retained Earnings $ 268,750.00 Total $ 562,750.00 $ 562,750.00