Your required tasks are as follows:

TAB 1/DATA

1. Insert all the data given below into TAB1B. This is the only TAB which can have hard coded entries. The balance sheet from the last accounting period is given.

TAB 2/BUDGET

Prepare a master budget for the three-month period ending June 30, 2016. You MUST use formulas in all cells, not constant numbers. That means all cells in your budget must be linked to the data from TAB 1 or completed data from TAB 2. A template has been provided. Include the following detailed budgets:

1. a. A schedule of expected cash collections from sales, by month and in total.

b. A merchandise purchases budget in dollars. Show the budget by month and in total.

c. A schedule of expected cash disbursements for merchandise purchases, by month and in total.

d. A schedule of cash disbursements for selling and administrative expenses, by month and in total.

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

TAB 3/INCOME STATEMENT

1. Prepare an absorption costing income statement for the quarter ending June 30. You MUST use formulas in all cells, not constant numbers.

TAB 4/BALANCE SHEET

1. Prepare a budgeted balance sheet as of June 30. You MUST use formulas in all cells, not constant numbers.

DATA:

a. Actual sales for March and budgeted sales for April-July are as follows:

March(actual) 75,000

April 65,000

May 100,000

June 85,000

July 65,000

b. Sales are 30% for cash and 70% on credit. All payments on credit sales are collected in the month following the sale. The accounts receivable at March 31 are a result of March credit sales.

c. The companys gross margin percentage is 40% of sales. (In other words, cost of goods sold is 60% of sales.)

d. The companys monthly selling and administrative expenses are as follows:

Variable:

Shipping 4% of sales

Other expenses 5% of sales

Fixed:

Wages and salaries $11,000

Advertising 8,000

Depreciation:

Depreciation for the entire quarter, including new assets acquired during the quarter will be $4,000.

e. Each months ending inventory should equal 45% of the following months cost of goods sold.

f. Inventory purchases are paid as follows: 50% in the month of purchase and the remaining 50% in the following month.

g. Equipment purchases during the quarter will be as follows: April $13,500, and May $2,500.

h. Dividends totaling $3,500 will be declared and paid.

i. The company desires a minimum ending cash balance each month of $6,000. The company has an agreement with a bank that allows it to borrow in increments if $1,000 at the beginning of each month, up to a total loan balance of$20,000. The interest rate on these loans is 1.5% per month, and for simplicity, we will assume that interest is 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 as possible (in increments of $1,000), while still retaining at least $6,000 in cash.

j. The companys balance sheet at March 31 is given on TAB 1A.

Actual Sales for March and budgeted sales for April-July are as follows:

March(actual)

April

May

June

July

Collection on Sales

Sales collected Current Month

Sales collected Following Month

Cost of Goods Sold (percentage of sales)

Desired ending invenories (Percentage of next COGS)

Purchases Paid as Follows

In month of purchase

In following month

Variable Monthly Expenses

Shipping

Other expenses

Fixed Monthly Expenses

Wages and Salaries

Advertising

Fixed Quarterly Expenses

Depreciation

Equipment Purchased in Quarter

April

May

Dividends declared and paid in June

Agreement with Bank

Borrowing increments

Maximum borrowing amount

Interest Rate per Year

Repayment increments

Required minimum balance

TAB 1A Newport Tie Company

Balance Sheet

31-Mar-16

Assets

Cash

8,000

Accounts Receivable

52,500

Inventory

17,550

Buildings and equipment (net)

214,100

Total Assets

292,150

Liabilities and Stockholders' Equity

Liabilities

Accounts Payable

$21,150

Stockholders' Equity

Common stock

194,850

Retained Earnings

76,150

Total Stockholders' Equity

271,000

Total Liabilities and Stockholders' Equity

$292,150

TAB1B (March 2016)

Budgeted Cost of Goods Sold

Add desired ending inventory

Total Needs

Less beginning inventory

Required dollar purchases

Required dollar purchases

1C: Schedule of cash disbursements for purchases

March Purchases

April Purchases

May Purchases

June Purchases

Total cash disbursements for purchases

1C: Schedule of cash disbursements for selling and administrative expenses

Salaries and wages

Shipping

Advertising

Other expenses

Total cash disbursements for selling and administrative expenses

2
NEWPORT TIE COMPANY
Cash Budget
	April	May	June	Quarter
Cash balance beginning
Add cash collections
Total cash available
Less disbursements:
Purchase of inventory
Selling and Administrative
Equipment
Dividends
Total Disbursements
Excess(deficiency) of receipts over disbursements
Financing:
Borrowings
Repayments
Interest
Total financing
Cash balance ending