Assume ABC Company has asked you to not only prepare their 2015 year-end Balance
ID: 2418131 • Letter: A
Question
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
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 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.
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
$15,000
Cash Flows – Year 2
$36,000
$74,000
$12,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
Required Action:
Part A:
Prepare the year-end balance sheet for 2015. Be sure to use proper headings.
Prepare budgets such that the pro-forma financial statements for the first quarter of 2016 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.
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
Explanation / Answer
Part A
(‘1) Balance Sheet as on 12/31/2015
Particulars
Amount
Assets
Current Assets
Cash
50,000
Accounts Receivable
175,000
Inventory
12,600
Total Current Assets (a)
237,600
Fixed Assets
Equipment Less Accumulated Depreciation (b)
390,000
Total Assets (a+b)
627,600
Liabilities
Current Liabilities
Accounts Payable
156,000
Short Term Notes
12,000
Total Current Liability (x)
168,000
Long Term Notes (y)
200,000
Shareholders’ Equity
Common Stock
235,000
Retained Earnings (balancing figure)
24,600
Total
627,600
(‘2) Sales Budget
Particulars
Dec-15
Jan-16
Feb-16
Mar-16
Apr-16
Sales Units
(Previous Month Sales x 1.05)
10,000
10,500
11,025
11,576
12,155
Selling Price Per Unit ($)
25
25
25
25
25
Sales Value ( $)
250,000
262,500
275,625
289,400
303,875
(‘3) Purchase Budget
We know that
Purchase units = Sales units + Ending Inventory – Beginning Inventory
Particulars
Jan-16
Feb-16
Mar-16
Apr-16
Sales Units
10,500
11,025
11,576
12,155
Ending Inventory
( Next Month Sales x 0.8)
8,820
9261
9724
Beginning Units
8400
8820
9261
Purchase Units
10,920
11,466
12,039
Purchase Price per unit
15
15
15
Purchase Cost
163,800
171,990
180,585
March-16 Budgeted Inventory
Budgeted Units
9724 units
Budgeted Inventory Cost
145,860
(‘4) Selling Expense Budget
Particulars
Jan-16
Feb-16
Mar-16
Sales Value ($)
262,500
275,625
289,400
Sales Representative Commission @ 12.5 % of sales
32,813
34,453
36,175
Sales Manager Salary
3,500
4,000
4,000
Total
36,313
38,453
40,175
(‘5) General Administrative Expense Budget
Particulars
Jan-16
Feb-16
Mar-16
Administrative Salary
8,000
8,000
8,000
Depreciation
5,000
5,000
5,000
Interest on Long Term Notes
1,800
1,800
1,800
Total
14,800
14,800
14,800
(‘6) Expected Cash Receipts from Customers and Accounts Receivable
Particulars
Jan-16
Feb-16
Mar-16
April-16
Sales Value
262,500
275,625
289,400
303,875
Cash Sales ( 30 %) (a)
78,750
82,688
86,820
91,163
Credit Sales (70 %)
183,750
192,937
202,580
212,712
Cash Collection for Accounts Receivable (b)
175,000
183,750
192,937
Total Cash Collection
253,750
266,438
279,757
Accounts Receivable for March-16 = $202,580
(‘7) Expected Cash Payment for Purchase
Particulars
Jan-16
Feb-16
Mar-16
Purchase Value of Merchandise
163,800
171,990
180,585
Credit Purchase (100 %)
163,800
171,990
180,585
Payment for Accounts Payable
156,000
163,800
171,990
Accounts Payable on March 31, 2016 = $ 180,585
(’8) Cash Budget
Particulars
Jan-15
Feb-16
Mar-16
Beginning Cash
50,000
89,517
50,000
Cash Collection from Sale and Accounts Receivable
253,750
266,438
279,757
Cash from Issue of Short Term Notes
-
6,098
Total Cash Received
303,750
362,053
329,757
Cash Paid for Merchandise
156,000
163,800
171,990
Selling Expense Paid
36,313
38,453
40,175
General Expense Paid
9,800
9,800
9,800
Interest Paid on Short Term Notes
120
61
Dividend Payment
100,000
Equipment Purchase
55,000
Payment of Short Term Notes
12,000
2,731
Total Payments
214,233
312,053
279,757
Ending Cash Balance
89,517
50,000
50,000
Short Term Notes on March 31, 2016 = $3367
Particulars
Amount
Assets
Current Assets
Cash
50,000
Accounts Receivable
175,000
Inventory
12,600
Total Current Assets (a)
237,600
Fixed Assets
Equipment Less Accumulated Depreciation (b)
390,000
Total Assets (a+b)
627,600
Liabilities
Current Liabilities
Accounts Payable
156,000
Short Term Notes
12,000
Total Current Liability (x)
168,000
Long Term Notes (y)
200,000
Shareholders’ Equity
Common Stock
235,000
Retained Earnings (balancing figure)
24,600
Total
627,600
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.