[obj777]

You are in charge of accounting at a Tie Company. Prepare the following budgets:

Prepare an Excel spreadsheet for a master budget for the three-month ending June 30, 2013. You MUST use formulas in all cells and not constant numbers. This means all cells in your budget must be linked to the data from another tab. Include the following budgets:

1A. Sales Budget by month and in total
1B. A Schedule of expected cash collections from sales, by month and in total.
1C. A merchandise purchases budget in units and in dollars. Show the budget by month and in total.
1D. A schedule of expected cash disbursements for merchandise purchases, by month and in total.

2. A cash budget. Show the budget by month and in total

3. A budgeted income statement for the three month period ending June 30Tth. Use the contribution approach. You MUST use formulas in all the cells once again.

4. A budgeted balance sheet as of June 30. You MUST use formulas in all the cells.
================================================== ===================================

Provided Data:

The company desires a minimum ending cash balance each month of $10,000

The ties are sold to retailers for $11.00 each and cost the company $6.00 each to make

Ending inventories are supposed to equal 90% of the next months sales in units.

Purchases are paid as follows: 50% in the month of purchase, and 50% in the following month.

All sales are on credit and are payable within 15 days. The company has found that only 25% of the months' sales are collected by month-end. An additional 50% is collected the next month, and the last 25% is collected the second month.

Projected Sales in Units:
January (actual) 30,000
February (actual) 28,000
March(actual) 32,000
April 38,000
May 48,000
June 50,000
July 40,000
August 40,000
September 32,000

================================================== ========================

The companys' monthly selling and administrative expenses are as follows:
Variable:
Sales Commissions $1.25 per tie

Fixed:
Wages and Salaries $24,000
Utilities $15,000
Insurance $1,500
Depreciation $1,750
Miscellaneous $3,500

All selling and administrative expenses are paid during the month, in cash, with the exception of depreciation and insurance expired. Land will be purchased during May for $35,000 cash. The company declares dividends of $25,000 each quarter, payable in the first month of the following quarter.


LOANS AND INTEREST:

The company has an agreement with a band that allows it to borrow in increments of $1,000 at the beginning of each month, up to a total loan balance of $100,000. The interest rate on these loans is 1% per month, and for simplicity are not compounded. At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan possible (In increments of $1000) while still retaining at least $10,000 in cash.

================================================== =================

The Company's Balance Sheet as of March 31st:

Cash $14,000
Accounts Receivable ($77,000 FEB. Sales $341,000
and $264,000 MAR. Sales
Merchandise Inventory $239,400
Prepaid Insurance $14,400
Fixed Assets Net of Depreciation $172,700
Total Assets $781,500

Liabilities and Stockholders Equity
Liabilities:
Accounts Payable $130,900
Dividends Payable $25,000
Total Liabilities $155,900

Stockholders Equity:
Common Stock $300,000
Retained Earnings $325,600
Total Stockholders Equity $625,600
Total Liab. & Stock.Equity $781,500

================================================== ====================================

Below is what I have done thus far. I was confident with all my answers up until Problem 2 until I could not complete it which has caused my to doubt what I have done so far.

I've been looking at this thread from a few months back which has helped tremendously, however I'm still wondering if I have done the forms correctly.
https://www.askmehelpdesk.com/accounting/budgeted-cash-disbursements-merchandise-purchases-580021.html


PROBLEM 1A

SALES BUDGET

April / May / June / Quarter

Budgeted Sales in Units 38,000 / 48,000 / 50,000 / 136,000
Selling Price per Unit $11 / $11 / $11 / $11
Total Sales $418,000 / $528,000 / $550,000 / $1,496,000


PROBLEM 1B

Schedule of Expected Cash Collections

April / May / June / Quarter

February Sales $77,000 / BLANK / BLANK / $77,000
March Sales $176,000 / $88,000 / BLANK / $264,000
April Sales $104,500 / $209,000 / $104,500 / $418,000
May Sales BLANK / $132,000 / $264,000 / $396,000
June Sales BLANK / BLANK / $137,500 / $137,500
Total Cash Collections $357,500 / $429,000 / $506,000 / $1,292,500


PROBLEM 1C

Merchandise Purchases Budget

April / May / June / Quarter

Budgeted Sales in Units 38,000 / 48,000 / 50,000 / $136,000
Add Budgeted Ending Inventory 43,200 / 45,000 / 36,000 / 36,000
Total Needs 81,200 / 93,000 / 86,000 / 172,000
Less Beginning Inventory 7,600 / 43,200 / 45,000 / 7,600
Required Unit Purchases 73,600 / 49,800 / 41,000 / 164,400
Unit Cost $6 / $6 / $6 / $6
Required Dollar Purchases $441,600 / $298,800 / $246,000 / $986,400


PROBLEM 1D

Budgeted Cash Disbursements for Merchandise Purchases

April / May / June / Quarter

March Purchases $130,900 / BLANK / BLANK / $130,900
April Purchases $220,800 / $220,800 / BLANK / $441,600
May Purchases BLANK / $149,400 / $149,400 / $298,800
June Purchases BLANK / BLANK / $123,000 / $123,000
Total Cash Payments $351,700 / $370,200 / $272,400 / $994,300


PROBLEM 2

CASH BUDGET

This is where I began to lose confidence in my numbers. Particularly dealing with the dividends and whether the Excess column had the correct value in them.


April / May / June / Quarter

Cash Balance Beginning $14,000 / $35,300 / $60,600 / $14,000
Add Receipts from Customers $418,000 / $528,000 / $550,000 / $1,292,500
Total Cash Available $432,000 / $563,300 / $610,600 / $1,306,500

Less Disbursements:
Purchase on Inventory $351,700 / $370,200 / $272,400 / $994,300
Sales Commission $47,500 / $60,000 / $62,500 / $170,000
Salaries & Wages $24,000 / $24,000 / $24,000 / $72,000
Utilities $15,000 / $15,000 / $15,000 / $45,000
Miscellaneous $3,500 / $3,500 / $3,500 / $10,500
Dividends BLANK / BLANK / $25,000 / $25,000
Land Purchases BLANK / $35,000 / BLANK / $35,000
Total Disbursement $441,700 / $507,700 / $402,400 / $1,351,800
Excess(Or deficiency) of
Receipts over disbursements ($9,700) / $30,600 / $158,183 / ($45,300)

Financing:
Borrowings $20,000 / BLANK / BLANK / $20,000
Repayments BLANK / ($20,000) / BLANK / ($20,000)
Interest BLANK / ($16.67) / BLANK / ($17)
Total Financing $20,000 / ($20,017) / BLANK / ($17)
Cash Balance Ending $10,300 / $10,583 / $158,183 / $158,183