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) 65,000

April 85,000

May 115,000

June 80,000

July 75,000

b. Sales are 40% for cash and 60% 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 45% of sales. (In other words, cost of goods sold is 55% of sales.)

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

Variable:

Shipping 3% of sales

Other expenses 6% of sales

Fixed:

Wages and salaries $12,000

Advertising 7,000

Depreciation:

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

e. Each months ending inventory should equal 40% 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 $14,500, and May $3,000.

h. Dividends totaling $4,000 will be declared and paid.

i. The company desires a minimum ending cash balance each month of $5,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$15,000. The interest rate on these loans is 1% 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 $5,000 in cash.

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

Data:

TAB1B Newport Tie Company			TAB 1A Newport Tie Company
			Balance Sheet
Actual Sales for March and budgeted sales for April-July are as follows:			31-Mar-16
March(actual)
April			Assets
May
June			Cash			8,000
July			Accounts Receivable			39,000
			Inventory			18,700
Collection on Sales			Buildings and equipment (net)			214,100
Sales collected Current Month			Total Assets			279,800
Sales collected Following Month			Liabilities and Stockholders' Equity
			Liabilities
Cost of Goods Sold (percentage of sales)			Accounts Payable			$20,075
Desired ending invenories (Percentage of next COGS)			Stockholders' Equity
			Common stock			183,575
Purchases Paid as Follows			Retained Earnings			76,150
In month of purchase			Total Stockholders' Equity			259,725
In following month			Total Liabilities and Stockholders' Equity			$ 279,800
Variable Monthly Expenses
Shipping			TAB1B (March 2016)
Other expenses			Budgeted Cost of Goods Sold
			Add desired ending inventory
Fixed Monthly Expenses			Total Needs
Wages and Salaries			Less beginning inventory
Advertising			Required dollar purchases
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

Budget:

NEWPORT TIE COMPANY
	April	May	June	Quarter
1A: Schedule of Expected Cash Collections
Cash Sales
Credit Sales
Total Cash Collections
1B: Merchandises Purchases Budget
Budgeted Cost of Goods Sold
Add desired ending inventory
Total Needs
Less beginning inventory
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
For the Three Months Ended June 30, 2016
	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