BMAL 530 EXCEL PROJECT INSTRUCTIONS

Excel Project Instructions
Assume
ABC Company has asked you to not only prepare their 2013 year-end Balance Sheet
but to also provide pro-forma financial statements for 2014. 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/13
Ending Balance

Cash

160,000

Accounts Receivable

126,000

Inventory

75,200

Equipment

745,000

Accumulated
Depreciation

292,460

Accounts Payable

36,900

Short-term Notes
Payable

18,300

Long-term Notes
Payable

157,225

Common Stock

450,000

Retained Earnings

solve

Additional
Information:
·
Sales
for December total 12,000 units. Each month’s sales are expected to exceed the
prior month’s results by 5%. The product’s selling price is $15 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 2012 inventory is 9,400 units,
which complies with the policy. The purchase price is $8 per unit.
·
Sales
representatives’ commissions are 10.0% 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 $140,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.

Project 1

Project 2

Project 3

Purchase
Price

$50,000

$75,000

$32,500

Required
Rate of Return

12%

8%

10%

Time
Period

3
years

5
years

2
years

Cash
Flows – Year 1

$18,000

$25,000

$20,000

Cash
Flows – Year 2

$22,000

$20,000

$18,000

Cash
Flows – Year 3

$22,000

$18,000

N/A

Cash
Flows – Year 4

N/A

$16,500

N/A

Cash
Flows – Year 5

N/A

$15,000

N/A

Required Action:
Part A:
Prepare the year-end balance sheet
for 2013. Be sure to use proper headings.Prepare budgets such that the
pro-forma financial statements may be prepared.
Sales budget, including
budgeted sales for April.Purchases budget, the budgeted
cost of goods sold for each month and quarter, and the cost of the March
31 budgeted inventory.Selling expense budget.General and administrative
expense budget.Expected cash receipts from
customers and the expected March 31 balance of accounts receivable.Expected cash payments for
purchases and the expected March 31 balance of accounts payable.Cash budget.Budgeted income statement.Budgeted statement of retained
earnings.Budgeted balance sheet.
Part B:
Calculate using Excel
formulas, the NPV of each of the 3 projects.It is possible that ABC
Company may not be able to complete all 3 projects. Therefore, advise ABC
Company as to the order in which they should pursue the projects (i.e.,
which project should ABC Company attempt to do first, second, and last).Provide
justification and analysis as to why you chose the order you did. The
analysis must also be done in Excel, not in a separate document.

This
assignment must be submitted as 1 Excel document.
This
assignment is due by 11:59 p.m. (ET) on Friday
of Module/Week 8.