BMAL530 Excel Project Spring Latest 2015 October

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 the first quarter of 2014
(that is, January – March 2014). They
also want you to evaluate 3projects 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 for this

Additional Information:
·
Sales
for December total 12,000 units. Each month’s unit 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,2013inventory
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 $160,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 all 3purchases.
The information for the purchases is as follows:

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.

[Hint: The End-of-Chapter Challenge for Chapter 4
of the Textbook provides a template to guide you in the preparation of all the necessary
budgets and financial statements.]
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
asa single Excel document.
This assignment is due by 11:59
p.m. (ET) on Friday of Module/Week
8.