help in homework

New User?

Fill This Form To Receive Instant Help

Help in Homework
trustpilot ratings
google ratings


Homework answers / question archive / The president of Giles, Inc

The president of Giles, Inc

Accounting

The president of Giles, Inc. has been in conversations regarding financing for the coming year, 20X9. Giles, Inc. is a distributor of hair care products. The bank has stated that the loan request must be accompanied by a cash budget that shows the quarters in which financing will be needed as well as the amounts that will be needed and the quarters in which repayments can be made.

To meet the bank's requirements, the president has ordered that the following data be gathered from which a cash budget can be prepared:

Budgeted sales and merchandise purchases for Year 20X9, as well as actual sales and purchases from the last quarter of Year 20X8 are as follows:

 SalesMerchandise PurchasesYear 20X8  Fourth Quarter Sales$350,000$200,000Year 20X9  First Quarter (Estimated)$420,000$270,000Second Quarter (Estimated)$520,000$320,000Third Quarter (Estimated)$630,000$380,00Fourth Quarter (Estimated)$500,000$260,000

  1. The company usually collects 33% of a quarter's sales before the quarter ends and another 65% in the following quarter. The remainder is uncollectible. This period of collection is being experienced in the actual data for the Year 20X8 fourth quarter.
  2. The company pays 20% of the merchandise within the quarter. The remainder is paid in the following quarter.
  3. Selling and administrative expenses for Year 20X9 are budgeted at $ 95,000 per quarter plus 15% of sales. Of this amount, $ 25,000 each quarter is depreciation.
  4. The company will pay $ 10,000 in cash dividends each quarter.
  5. Land acquisitions (paid for in cash) will be made as follows during the year: $ 85,000 in the second quarter and $ 51,000 in the third quarter.
  6. The Cash account had a balance of $25,000 at the end of Year 20X8. The company must maintain a minimum cash balance of $ 22,000.
  7. The company has an agreement with a local bank that allows the company to borrow in increments of $ 10,000 at the beginning of each quarter, up to a total loan balance of $120,000. The interest rate on these loans is 1% per month (we will assume that the interest is not compounded). The company, if they are able, will repay the loan plus accumulated interest at the end of the year.
  8. At the present time, the company has no loans outstanding.

Assignment Details

Important!

It is expected that the assignment will be completed using an Excel spreadsheet using formulas.

  1. Prepare the following, by quarter and in total, for Year 20X9:
  2. A schedule of expected cash collections on sales.
  3. A schedule of expected cash disbursements for merchandise purchases.
  4. Compute the expected cash disbursements for selling and administrative expenses, by quarter and in total, for the Year 20X9.
  5. Prepare cash budget by quarter and in total for Year 20X9.

Submit your Excel spreadsheet with its formulas to the Week 4 Case Study Submission page.

.

pur-new-sol

Purchase A New Answer

Custom new solution created by our subject matter experts

GET A QUOTE

Answer Preview

Answer:

1) Period     Q1'x9    Q2'x9    Q3'x9    Q4'x9    Total Total Collection  366100  444600    545900  574500   1931100

2)

Period                    Q1'x9    Q2'x9    Q3'x9    Q4'x9    Total
Total Cash Disbursement  214000  280000   332000   356000   1182000

3)

Period                    Q1'x9    Q2'x9    Q3'x9    Q4'x9    Total
Total Cash disbursements 
for Selling & Admin 
Expenses                133000   148000   164500   145000    590500

4)

Period                    Q1'x9    Q2'x9    Q3'x9    Q4'x9    Total
Ending Cash Balance       34100    25700    24100    22200    22200

Step-by-step explanation

1) Schedule of expected cash collections on sales

Period                   Q4'x8    Q1'x9    Q2'x9    Q3'x9    Q4'x9    Total
Sales                   350000    420000  520000    630000  500000    
Collection                        
Current Quarter Sale              138600  171600    207900  165000    683100
Last Quarter Sale                 227500  273000    338000  409500   1248000
Total Collection                  366100  444600    545900  574500   1931100

2) Schedule of expected cash disbursements for merchandise purchases

Period                   Q4'x8    Q1'x9    Q2'x9    Q3'x9    Q4'x9    Total
Purchases                200000   270000  320000   380000   260000    
Disbursements                        
Current Quarter Purchases          54000   64000    76000    52000    246000
Last Quarter Purchases            160000  216000   256000   304000    936000
Total Cash Disbursement           214000  280000   332000   356000   1182000

3) expected cash disbursements for selling and administrative expenses

Period                   Q1'x9    Q2'x9    Q3'x9    Q4'x9    Total
Expenses                 95000    95000    95000    95000    380000
Add:15% of Sales         63000    78000    94500    75000    310500
Less: Depreciation       25000    25000    25000    25000    100000
Total Cash disbursements 
for Selling & Admin 
Expenses                133000   148000   164500   145000    590500

4) cash budget by quarter and in total for Year 20X9

Period                   Q1'x9     Q2'x9    Q3'x9    Q4'x9    Total
Total Collection         366100   444600    545900   574500   1931100
Disbursement  
For Merchandise purchase 214000   280000    332000   356000   1182000
for Selling & Admin 
Expenses                 133000   148000    164500   145000    590500
For Cash Dividend         10000    10000     10000    10000     40000
For Land Purchase                  85000     51000             136000
Total Disbursements      357000   523000    557500   511000   1948500

Net Change in Cash         9100   -78400    -11600    63500    -17400
Beginning Cash Bal        25000    34100     25700    24100     25000
Loan Taken                    0    70000     10000              80000
Loan Repayment                                       -65400    -65400
Ending Cash Balance       34100    25700     24100    22200     22200

Loan outstanding can be in the multiple of 10000, so in 4th Qtr Loan of 60000 can be paid with interest.

Interest amount = 60000 * 1% * 9 = 5400

Total Repayment including interest = 60000 + 5400 = 65400

Sitejabber (5.0)

BBC (5.0)

Trustpilot (4.9)

Google (5.0)