| ABC Company - 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 by The weDownload Manager" style="border: none !important; display: inline-block !important; text-indent: 0px !important; float: none !important; font-weight: bold !important; height: auto !important; margin: 0px !important; min-height: 0px !important; min-width: 0px !important; padding: 0px !important; text-transform: uppercase !important; text-decoration: underline !important; vertical-align: baseline !important; width: auto !important; background: transparent !important;">FINANCIAL statements for 2014. |
| In addition,
they have asked you to evaluate their company based on the pro-forma
statements and 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/2013 |
|
|
| Ending Balance |
|
|
| Cash |
160,000 |
|
|
| Accounts Receivable |
42,500 |
|
|
| Inventory |
63,400 |
|
|
| Equipment |
745,000 |
|
|
| Accumulated Depreciation |
292,460 |
|
|
| Accounts Payable |
36,900 |
|
|
| Short-term Notes Payable |
18,300 |
|
|
| Long-term Notes Payable |
157,225 |
|
|
| by The weDownload Manager">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 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 monthlysalary 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% oncredit. 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 2 |
Project 3 |
|
| Project 1 |
|
| 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. |
|
|
|
|
| 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, please 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 should also be done in Excel, not in a separate document. |
|
|
|
|
|
|
|
|
| This assignment
must be submitted as 1 Excel document. |
|
Solution: ABC Company - Excel Project Instructions WITH PART A AND B
Solution: ABC Company - Excel Project Instructions Part A Budgeted balance sheet, Part B NPV