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, 2017. 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 in June.

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.

rt Tie Company Balance Sheet 31-Mar-17 Assets Cash Accounts Receivable nventory Buildings and equipment (net) Total Assets Liabilities and Stockholders' Equity Liabilities Accounts Payable Stockholders' Equity Common stock Retained Earnings Total Stockholders' Equity Total Liabilities and Stockholders' Equity 8,000 52.500 17,550 214,100 292,150 $21,150 194850 76.150 271.000 292,150