Question

Excel Project Instructions

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 31 2017 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.

Part A:

• Prepare budgets such that the pro-forma financial statements for the first quarter of 2018 may be prepared.
• 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.

Answer 1

Answer :

Part A Step 1 Prepare the year-end balance sheet for 2017

ABC Company

Balance Sheet

As of December 31, 2017

Assets

Current Assets

Cash $50,000

Accounts Receivable $175,000

Inventory $126,000

Total Current Assets $351,000

Equipment $480,000

Accumulated Depreciation $(90,000)

Equipment, Net $390,000

Total Assets $741,000

Liabilities and Stockholders' Equity

Current Liabilities

Accounts Payable $156,000

Short-term Notes Payable $12,000

Total Current Liabilities $168,000

Long-term Notes Payable $200,000

Total Liabilities $368,000

Stockholders' Equity

Common Stock $235,000

Retained Earnings $138,000 =373000-235000 (b)

Total Stockholders' Equity $373,000 =741000-368000 (a)

Total Liabilities and Stockholders' Equity $741,000

Step 2 Prepare Sales Budget

Description January February March Total

Budgeted Unit Sales 10,500 11,025 11,576 33,101 =10000*105% =10500*105% =11025*105%

Per unit Selling Price $25 $25 $25 $25

Budgeted Sales Revenue $262,500 $275,625 $289,406 $827,531

Budgeted Cash Sales $78,750 $82,688 $86,822 $248,259

Budgeted Credit Sales $183,750 $192,938 $202,584 $579,272

Step 3 Prepare Purchases budget

Description January February March Total

Budgeted Unit Sales 10,500 11,025 11,576 33,101

Add Required Ending Inventory 8,820 9,261 9,724 9,724

Total required units 19,320 20,286 21,300 42,825

Less Beginning Inventory 8,400 8,820 9,261 8,400

Budgeted Purchase units 10,920 11,466 12,039 34,425

Per unit Purchase Price $15 $15 $15 $15

Budgeted Purchase (dollars) $163,800 $171,987 $180,589 $516,376

Step 4 Prepare Selling expense budget.

Description January February March Total

Sales representatives' commissions $32,813 $34,453 $36,176 $103,441 =262500*12.5% =275625*12.5% =289406*12.5%

Sales manager's salary $3,500 $4,000 $4,000 $11,500

Budgeted Selling Expenses $36,313 $38,453 $40,176 $114,941

Step 5 General and administrative expense budget

Description January February March Total

Administrative salaries $8,000 $8,000 $8,000 $24,000

Depreciation $5,000 $5,000 $5,000 $15,000

Interest on the long-term note payable $1,800 $1,800 $1,800 $5,400

Budgeted General and Administrative Expenses $14,800 $14,800 $14,800 $44,400

"Step 6 Prepare Expected cash receipts from customers and the expected March 31 balance of accounts receivable.

"

Description January February March Total

Collection of Beg. AR $175,000 Blank Blank $175,000

For January Sales Blank $183,750 Blank $183,750

For February Sales Blank Blank $192,938 $192,938

Total Cash collection from AR $175,000 $183,750 $192,938 $551,688

March 31 balance of accounts receivable $202,584

Step 7 Prepare Expected cash payments for purchases and the expected March 31 balance of accounts payable

Description January February March Total

Beg. AP $156,000 Blank Blank $156,000

For January Purchase Blank $163,800 Blank $163,800

For February Purchase Blank Blank $171,987 $171,987

Total Payment to AP $156,000 $163,800 $171,987 $491,787

March 31 balance of accounts payable $180,589

Step 8 Prepare Cash budget

Description January February March Total

Beginning Cash Balance $50,000 $91,318 $50,502 $50,000

Cash Sales $78,750 $82,688 $86,822 $248,259

Collection from AR $175,000 $183,750 $192,938 $551,688

Total cash available $303,750 $357,755 $330,261 $849,947

Disbursements:

Payment for Purchases $156,000 $163,800 $171,987 $491,787

Payment for Selling Expenses $36,313 $38,453 $40,176 $114,941

Payment G&A $8,000 $8,000 $8,000 $24,000

Payment of Dividends Blank $100,000 Blank $100,000

Equipment purchases Blank Blank $55,000 $55,000

Total cash disbursements $200,313 $310,253 $275,163 $785,728

Cash Surplus (Shortage) $103,438 $47,502 $55,098 $64,218

Financing:

Borrowings Blank $3,000 Blank $3,000

Repayment $12,000 Blank $3,000 $15,000

Interest $120 $-   $30 $150

Ending Cash Balance $91,318 $50,502 $52,068 $52,068

Step 9 Prepare Budgeted income statement

Sales Revenue $827,531

Cost of Goods Sold $496,516 =516376+(8400*15)-(9724*15)

Gross Profit $331,015

Operating Expenses

Selling Expenses $114,941

G&A Expenses $44,400

Interest on short term notes $150

Total Operating Expenses $159,491

Operating Income $171,524

Income Tax $60,033

Net Income $111,491

Step 10 Prepare Budgeted statement of retained earnings

Beg. Retained Earnings 138,000

Add Net Income 111,491

Less Dividends 100,000

End. Retained Earnings 149,491

Step 11 Prepare Budgeted balance sheet

Assets

Current Assets

Cash $52,068

Accounts Receivable $202,584

Inventory $145,860 =9724*15

Total Current Assets $400,512

Equipment $535,000 =480000+55000

Accumulated Depreciation $(105,000) =-(90000+15000)

Equipment, Net $430,000

Total Assets $830,512

Liabilities and Stockholders' Equity

Current Liabilities

Accounts Payable $180,589

Interest on Long term Notes Payable $5,400

Income Tax Payable $60,033

Total Current Liabilities $246,023

Long-term Notes Payable $200,000

Total Liabilities $446,023

Stockholders' Equity

Common Stock $235,000

Retained Earnings $149,491

Total Stockholders' Equity $384,491

Total Liabilities and Stockholders' Equity $830,513

Note Difference of $1 due to rounding of to nearest dollar