Question

In: Finance

Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but...

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 buget
  • Budgeted income statement.
  • Budgeted statement of retained earnings.
  • Budgeted balance sheet.

Show excel formula

Solutions

Expert Solution

I made all necessary budget in excel and paste tacle here and show the all formula via screenshot of excel. If you have any query please revert me.

Below is the input data sheet screen shot in this cell no. B25 commission of sales representative is 12.5% due to roundig off it shows 13% but calculations are correct. in table below the screenshot i made correction .

Input data sheet
Account 31/12/2017
Ending Balance    
Cash 50,000
Accounts Receivable 1,75,000    
Inventory 1,26,000    
Equipment 4,80,000    
Accumulated Depreciation 90,000    
Accounts Payable 1,56,000    
Short-term Notes Payable 12,000    
Long-term Notes Payable 2,00,000    
Common Stock 2,35,000    
Retained Earnings solve
December sales 10,000 units
Each month's sales 5% extra of prior month's sales
Selling price $25 per unit
Each month's ending inventory 80% of the next month's expected unit sales
December 31, 2017 inventory 8,400 units
Purchase price $15 per unit
Sales representatives' commissions 12.5% of sales
Sales manager's monthly salary (January) $3,500
Sales manager's monthly salary (After Jan) $4,000
General and administrative expenses:
Administrative salaries $8,000 per month
Depreciation $5,000 per month
Interest on the long-term note payable 0.90% monthly
Cash sales 30% of sales
Credit sales 70% of sales
Collection of receivables in the month following the sale
Purchases Credit
Payment of purchases in the next month
Minimum ending cash balance $50,000
Interest on short-term notes 1% at each month end
Dividends payment in February $1,00,000
Income tax rate 35%
Purchase of equipment in March $55,000

ABC Company
Balance Sheet
As At December 31, 2017
Assets
Current Assets
    Cash $50,000
    Accounts Receivable 1,75,000
    Inventory 1,26,000
    Total Current Assets 3,51,000
Fixed Assets
    Equipment 4,80,000
    Less: Depreciation 90,000 3,90,000
    Total Fixed Assets 3,90,000
Total Assets 7,41,000
Liabilities
Current Liabilities
    Accounts payable 1,56,000
    Short-term Notes Payable 12,000
    Total Current Liabilities 1,68,000
Long Term Liabilities
    Long-term Notes Payable 2,00,000
Total Liabilities 3,68,000
Owner's Equity
Common Stock 2,35,000
Retained Earnings 1,38,000
Total Owner's Equity 3,73,000
Liabilities & Owner's Equity 7,41,000

ABC Company
Sales Budget
January February March April
Expected unit sales 10,500 11,025 11,576 12,155
Unit selling price $25 $25 $25 $25
Total sales $2,62,500 $2,75,625 $2,89,406 $3,03,877
ABC Company
Purchases Budget
January February March Quarter
Expected unit sales 10,500 11,025 11,576 33,101
Add: Desired ending inventory 8,820 9,261 9,724
Total inventory required 19,320 20,286 21,300
Less: Beginning inventory 8,400 8,820 9,261
Units required to be purchased 10,920 11,466 12,039 34,425
Price per unit $15 $15 $15
Total cost of purchases $1,63,800 $1,71,990 $1,80,590 $5,16,380
Cost of goods sold $1,57,500 $1,65,375 $1,73,644 $4,96,519
Cost of ending inventory $1,45,861
ABC Company
Selling Expense Budget
January February March Quarter
Sales representatives' commissions $32,812.50 $34,453.13 $36,175.78 1,03,441
Sales manager's salary   3,500 4,000 4,000 11,500
Total selling expenses 36,313 38,453 40,176 1,14,941

ABC Company
General and Administrative Expense Budget
January February March Quarter
Administrative salaries $8,000 $8,000 $8,000 24,000
Depreciation 5,000 5,000 5,000 15,000
Interest on the short-term note payable 120 120
Interest on the long-term note payable 1,800 1,800 1,800 5,400
Total general & administrative expense $14,920 $14,800 $14,800 $44,520
ABC Company
Expected Cash Receipt from Customers
January February March Quarter
Cash sales $78,750 $82,688 $86,822 2,48,259
Collection from credit sales 1,75,000 1,83,750 1,92,938 5,51,688
Total cash receipt from customers 2,53,750 2,66,438 2,79,759 7,99,947
Expected accounts receivable (March 31) $2,02,584
ABC Company
Expected Cash Payment for Purchases
January February March Quarter
Payment for purchases $1,56,000 $1,63,800 $1,71,990 4,91,790
Expected accounts payable (March 31) $1,80,590

ABC Company
Cash Budget
January February March
Cash Inflows
Cash receipt from customers 2,53,750 2,66,438 2,79,759
Cash Outflows
Payment for purchases 1,56,000 1,63,800 1,71,990
Selling expenses 36,313 38,453 40,176
Administrative salaries $8,000 $8,000 $8,000
Interest on the long-term note payable 1,800 1,800 1,800
Interest on the short-term note payable 120 0 0
Payment of dividends 1,00,000
Purchase of equipment 55,000
Total cash outflows 2,02,233 3,12,053 2,76,966
Cash Flow Summary
Cash at the start of month $50,000 $89,518 $50,000
Net cash gain (loss) during month 51,518 -45,616 2,794
Cash balance at end of month before financing $1,01,518 $43,902 $52,794
Less: Minimum cash balance desired 50,000 50,000 50,000
Surplus cash (deficit) $51,518 ($6,098) $2,794
External Financing Summary
External financing balance at start of month 0 0 $6,098.13
New financing required 0 $6,098.13 0
Financing repayments 12,000 0 $2,794
External financing balance at end of month 0 6,098 3,305
Cash balance at the end of month after financing $89,518 $50,000 $50,000

ABC Company
Budgeted Income Statement
For the quarter Ending March 31, 2018
Sales 8,27,531
Cost of goods sold 4,96,519
Gross margin 3,31,013
Operating Expenses
   Selling Expense
Sales representatives' commissions 1,03,441
Sales manager's salary   11,500
   Total Selling Expense 1,14,941
   General and Administrative Expense (G & A)
Administrative salaries 24,000
Depreciation 15,000
Interest on the short-term note payable 120
Interest on the long-term note payable 5,400
Total general & administrative expense 44,520
Total Operating Expenses 1,59,461
    Operating Income 1,71,551
    Income Tax Expense 60,043
    Net income 1,11,508
Statement of Retained Earnings
March 31, 2018
Opening Retained Earnings                     1,38,000
Add: Net Income                     1,11,508
Less: Dividends ($1,00,000)
Retained Earnings, March 31, 2018                     1,49,508

ABC Company
Budgeted Balance Sheet
As At March 31, 2018
Assets
Current Assets
    Cash $50,000
    Accounts Receivable 2,02,584
    Inventory 1,45,861
    Total Current Assets 3,98,445
Fixed Assets
    Equipment 5,35,000
    Less: Depreciation 1,05,000 4,30,000
    Total Fixed Assets 4,30,000
Total Assets 8,28,445
Liabilities
Current Liabilities
    Accounts payable 1,80,590
    Short-term Notes Payable 3,305
    Income Tax Payable 60,043
    Total Current Liabilities 2,43,937
Long Term Liabilities
    Long-term Notes Payable 2,00,000
Total Liabilities 4,43,937
Owner's Equity
Common Stock 2,35,000
Retained Earnings 1,49,508
Total Owner's Equity 3,84,508
Liabilities & Owner's Equity 8,28,445

Related Solutions

Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but...
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...
Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but...
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...
Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but...
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...
Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but...
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...
Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but...
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...
Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but...
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...
Excel Project Instructions Assume ABC Company has asked you to not only prepare their 2017 year-end...
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...
Bohemian Manufacturing Company has the following end-of-year balance sheet: Bohemian Manufacturing Company Balance Sheet For the...
Bohemian Manufacturing Company has the following end-of-year balance sheet: Bohemian Manufacturing Company Balance Sheet For the Year Ended on December 31 Assets Liabilities Current Assets: Current Liabilities: Cash and equivalents $150,000 Accounts payable $250,000 Accounts receivable 400,000 Accrued liabilities 150,000 Inventories 350,000 Notes payable 100,000 Total Current Assets $900,000 Total Current Liabilities $500,000 Net Fixed Assets: Long-Term Bonds 1,000,000 Net plant and equipment(cost minus depreciation) $2,100,000 Total Debt $1,500,000 Common Equity Common stock 800,000 Retained earnings 700,000 Total Common Equity...
The balance sheet data of Pronghorn Company at the end of 2017 and 2016 follow. 2017...
The balance sheet data of Pronghorn Company at the end of 2017 and 2016 follow. 2017 2016 Cash $29,000 $35,400 Accounts receivable (net) 55,400 44,500 Inventory 64,300 44,600 Prepaid expenses 15,200 25,000 Equipment 90,800 74,800 Accumulated depreciation—equipment (18,100 ) (8,800 ) Land 79,900 39,600 $316,500 $255,100 Accounts payable $63,600 $49,000 Accrued expenses 15,000 18,000 Notes payable—bank, long-term –0– 22,800 Bonds payable 30,200 –0– Common stock, $10 par 200,400 160,100 Retained earnings 7,300 5,200 $316,500 $255,100 Land was acquired for $40,300...
The balance sheet data of Ivanhoe Company at the end of 2017 and 2016 follow. 2017...
The balance sheet data of Ivanhoe Company at the end of 2017 and 2016 follow. 2017 2016 Cash $29,000 $34,900 Accounts receivable (net) 55,200 44,900 Inventory 64,900 44,600 Prepaid expenses 15,100 24,900 Equipment 90,100 75,600 Accumulated depreciation—equipment (18,200 ) (7,200 ) Land 80,600 39,800 $316,700 $257,500 Accounts payable $67,200 $53,300 Accrued expenses 14,800 17,900 Notes payable—bank, long-term –0– 23,000 Bonds payable 29,700 –0– Common stock, $10 par 198,700 157,900 Retained earnings 6,300 5,400 $316,700 $257,500 Land was acquired for $40,800...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT