Free Educational Resources

• > BMAL 530 EXCEL PROJECT
+

BMAL 530 EXCEL PROJECT

Rating:
(0)
Author: simmons96 96
Description:

http://theperfecthomework.com/bmal-530-excel-project/

Excel Project Instructions
Assume ABC Company has asked you to not only prepare their 2015 year-end Balance Sheet but to also provide pro-forma financial statements for 2016. In addition, they have asked you to evaluate their company based on the pro-forma statements with regard to ratios. They also want you to evaluate 3 projects they are considering. Their information is as follows:
End of the year information:
Account

12/31/15
Ending Balance

Cash

50,000

Accounts Receivable

175,000

Inventory

126,00

Equipment

480,000

Accumulated Depreciation

90,000

Accounts Payable

156,000

Short-term Notes Payable

12,000

Long-term Notes Payable

200,000

Common Stock

235,000

Retained Earnings

solve

Sales for December total 10,000 units. Each month’s sales are expected to exceed the prior month’s results by 5%. The product’s selling price is \$25 per unit.
Company policy calls for a given month’s ending inventory to equal 80% of the next month’s expected unit sales. The December 31 2015 inventory is 8,400 units, which complies with the policy. The purchase price is \$15 per unit.
Sales representatives’ commissions are 12.5% of sales and are paid in the month of the sales. The sales manager’s monthly salary will be \$3,500 in January and \$4,000 per month thereafter.
Monthly general and administrative expenses include \$8,000 administrative salaries, \$5,000 depreciation, and 0.9% monthly interest on the long-term note payable.
The company expects 30% of sales to be for cash and the remaining 70% on credit. Receivables are collected in full in the month following the sale (none is collected in the month of sale).
All merchandise purchases are on credit, and no payables arise from any other transactions. One month’s purchases are fully paid in the next month.

The minimum ending cash balance for all months is \$50,000. If necessary, the company borrows enough cash using a short-term note to reach the minimum. Short-term notes require an interest payment of 1% at each month-end (before any repayment). If the ending cash balance exceeds the minimum, the excess will be applied to repaying the short-term notes payable balance.
Dividends of \$100,000 are to be declared and paid in February.
No cash payments for income taxes are to be made during the first calendar quarter. Income taxes will be assessed at 35% in the quarter.
Equipment purchases of \$55,000 are scheduled for March.
ABC Company’s management is also considering 3 new projects consisting of the purchase of new equipment. The company has limited resources, and may not be able to complete make all 3 purchases. The information is as follows for the purchases below.

(more)
Try a College Course Free

Sophia’s self-paced online courses are a great way to save time and money as you earn credits eligible for transfer to over 2,000 colleges and universities.*

No credit card required

26 Sophia partners guarantee credit transfer.

226 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 21 of Sophia’s online courses. More than 2,000 colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial