Accounting project help

ACC 210 – Fall 2014 Name___________________________________

Computer Project – 10 points

Project #D

Important Note: This project should be an individual effort. Help from tutors, supplemental instruction instructors, or friends would be considered a violation of academic integrity. All questions should be addressed to the instructor, Sha Zhao.

A file entitled “Computer Project D” has been placed on MOODLE for you to download and save.

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, 2014. 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 90,000

May 110,000

June 100,000

July 80,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 company’s gross margin percentage is 35% of sales. (In other words, cost of goods sold is 65% of sales.)

d. The company’s monthly selling and administrative expenses are as follows:

Variable:

Shipping 5% of sales

Other expenses 8% of sales

Fixed:

Wages and salaries $15,000

Advertising 8,000

Depreciation:

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

e. Each month’s ending inventory should equal 30% of the following month’s 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 $18,500, and May $2,000.

h. Dividends totaling $2,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$40,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 $6,000 in cash.

j. The company’s balance sheet at March 31 is given on TAB 1A.

Due Date: Submit a finished file through MOODLE by Friday November 21st by 11:59pm. Your file should be named yourlastname_firstname_computerprojectD. Only files submitted by MOODLE will be accepted.

As noted on the syllabus, late projects will be accepted until graded projects have been returned. There is a 10% penalty for each 24-hour period that the project is late.

Grading: Your grade will be based on correctness, statement form, and spreadsheet formatting.