EXCEL PROJECT ASSIGNMENT INSTRUCTIONS

OVERVIEW

Assume ABC Company has asked you to not only prepare their $2017$ year$-$end Balance Sheet but to also provide pro$-$forma financial statements for $2018.$ 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/17$

Ending Balance

Cash $50,000$

Accounts Receivable $175,000$

Inventory $126,000$

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

Additional Information:

$$ 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 $312017$ inventory is $8,400$ units, which complies with the policy. The purchase price is $$15$ per unit.

$$ Sales representatives$$ commissions are $12\mathrm{.}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\mathrm{.}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:

Project $1$ Project $2$ Project $3$

Purchase Price $$80,000$ $$175,000$ $$22,700$

Required Rate of Return $6\%8\%12\%$

Time Period $3$ years $5$ years $2$ years

Cash Flows $$ Year $1$ $$48,000$ $$85,000$ $$13,000$

Cash Flows $$ Year $2$ $$36,000$ $$74,000$ $$13,000$

Cash Flows $$ Year $3$ $$22,000$ $$38,000$ N$/$A

Cash Flows $$ Year $4$ N$/$A $$26,800$ N$/$A

Cash Flows $$ Year $5$ N$/$A $$19,000$ N$/$A

$$

INSTRUCTIONS

Part A:

$$ Prepare the year$-$end balance sheet for $2017.$ Be sure to use proper headings.

$$ Prepare budgets such that the pro$-$forma financial statements for the first quarter of $2018$ 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 one Excel document