** I am mostly interested in the process for the cash budget in question #2. How

Question

** I am mostly interested in the process for the cash budget in question #2. However, I would still like to see how the entire problem works though. I am really struggling with this so please make your steps as simple and easy to follow as possible. Thank you for your time.

ACCT 2302 Managerial Accounting

Master Budget Project

A. Information to be used in preparation of master budget: 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: a. 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

b. 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.

c. 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.

d. Inventory purchases are paid for in the month following the purchase.

e. Recurring monthly expenses are as follows: Salaries and wages $10,000 Depreciation on plant and equipment 4,000 Utilities 1,000 Other 1,700

f. Property taxes of $15,000 are due and payable on July 15, 2016.

g. Advertising fees of $6,000 must be paid on August 20, 2016.

h. A lease on a new storage facility is scheduled to begin on September 2, 2016. Monthly payments are $5,000.

i. 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.

j. 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 $ ? $ ?

========

========

2

B. Requirements

1. Complete the balance sheet given in item j.

2. Prepare a cash budget for each month in the third quarter and for the quarter in total (the third quarter begins on July 1). Provide a supporting schedule of cash collections.

3. Prepare a pro-forma balance sheet as of September 30, 2016.

4. Provide 3 different ways in which the company may increase net income. In fulfilling this requirement, you should use as many concepts from the course as possible, identify and explain each concept used, and provide pro-forma excel spreadsheets to support your recommendations.

Explanation / Answer

1 Balance sheet as of June 30, 2016 (in $) Cash (Balancing Figure) 13550 Accounts receivables (schedule III) 88200 Inventory (schedule II) 36000 Plant and Equipment 425000 Accounts Payables (schedule II) 84000 Common Stock 210000 Retained Earnings 268750 562750 562750 2 Cash budget for each month in the third quarter and for the quarter in total (the third quarter begins on July 1) July August September Beginning Cash Balance 13550 10405 10360 Add: Budgeted cash receipts (Schedule I) 102600 100700 113300 Total Cash available for use 116150 111105 123660 Less: cash disbursements    Purchases Paid (Schedule II) 84000 76000 94000    Recurring monthly expenses Paid (Schedule III) 12700 12700 12700    Property Tax 15000    Advertising Fee 6000    Lease Payments 5000    Interest 45 45    (borrowing is done at the beginning of the month) (6000*9%*1/12) (6000*9%*1/12) Total Disbursements 111745 94745 111700 Cash Surplus/(deficit) 4405 16360 11960 (Minimum Cash Balance to be maintained - $ 10,000) Borrow 6000 -6000 (company must borrow in multiples of $1,000 and principal payment is made at the end of a month) Budgeted Ending Cash Balance 10405 10360 11960 Schedule I - cash collections (In $) May June July August September Sales 100000 120000 90000 100000 135000 Cash Sales - 30% of Total sales (A) 30000 36000 27000 30000 40500 Credit Sales - 70% of Total sales 70000 84000 63000 70000 94500 Cash Collection of Credit Sales     In the month of Sale -20% of Credit sales 16800 12600 14000 18900 (84000*20%) (63000*20%) (70000*20%) (94500*20%)     In the following month of Sale -50% of Credit sales 35000 42000 31500 35000 (70000*50%) (84000*50%) (63000*50%) (70000*30%)     In the second month of Sale -30% of Credit sales 21000 25200 18900 (70000*30%) (84000*30%) (63000*30%) Cash Collection of Credit Sales (B) 75600 70700 72800 Total Cash Collections (A+B) 102600 100700 113300 Schedule II - Purchases Payment May June July August September October Beginning Inventory (A) 48000 36000 40000 54000 44000 Cost of Goods Sold (B) 96000 72000 80000 108000 88000 (The markup on goods is 25 percent of cost) (120000*100/125) (90000*100/125) (100000*100/125) (135000*100/125) (110000*100/125) Closing Inventory( C) 48000 36000 40000 54000 44000 (50 percent of the cost of next month’s sales) (120000*100/125*50%) (90000*100/125*50%) (100000*100/125*50%) (135000*100/125*50%) (110000*100/125*50%) Purchases (B+C-A) 84000 76000 94000 98000 Payment for Purchases 84000 76000 94000 (Paid in the month following the purchase) Schedule III - Recurring Monthly Expenses Payment Salaries and Wages 10000 Depreciation on Plant and Equipment 0 (Non Cash Expense) Utilities 1000 Others 1700 12700 Accounts receivables (schedule III) Credit Sales Outstanding as on 1st July May 70000 21000 (70000*30%) June 84000 67200 (84000*80%) 88200 Accounts receivables (schedule IV) Credit Sales Outstanding as on 30th September July 63000 18900 (70000*30%) August 70000 56000 (84000*80%) 74900 3 Balance sheet as of September 30, 2016 Cash 11960 Accounts receivables (schedule IV) 74900 Inventory (schedule II) 44000 Plant and Equipment 425000 Accounts Payables (schedule II) 98000 Common Stock 210000 Retained Earnings (Balancing Figure) 247860 555860 555860

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.