Question

Please create an Assumption page in excel for all given data. Creating a master budget where all data needs to be links to assumptions. No hard numbers can be used. Thank you

The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods: Current assets as oJune Quarter Shilow Company Cash Budget April May $ 8,500 69,600 78.100 0 0 0 Quarter Schedule of Expected Cash Collections A

0 0
Add a comment Improve this question Transcribed image text
Answer #1

Solution 1:

Schedule of expected cash collections
Particulars April May June Quarter
Budgeted Sales $76,000.00 $81,000.00 $106,000.00 $263,000.00
Cash Sale $45,600.00 $48,600.00 $63,600.00 $157,800.00
Collection for credit sales $24,000.00 $30,400.00 $32,400.00 $86,800.00
Total Collections $69,600.00 $79,000.00 $96,000.00 $244,600.00

Solution 2:

Merchandise Purchase Budget
Particulars April May June Quarter
Budgeted Cost of Goods Sold (75% of Sales) $57,000.00 $60,750.00 $79,500.00 $197,250.00
Add: Desired ending merchandise inventory (80% of next month COGS) $48,600.00 $63,600.00 $28,800.00 $28,800.00
Total Needs $105,600.00 $124,350.00 $108,300.00 $226,050.00
Less: Beginning inventory $45,600.00 $48,600.00 $63,600.00 $45,600.00
Required purchases $60,000.00 $75,750.00 $44,700.00 $180,450.00
Schedule of expected cash disbursement - Merchandise Purchases
Particulars April May June Quarter
March Purchases $27,300.00 $27,300.00
April Purchases $30,000.00 $30,000.00 $60,000.00
May Purchases $37,875.00 $37,875.00 $75,750.00
June Purchases $22,350.00 $22,350.00
Total Disbursement $57,300.00 $67,875.00 $60,225.00 $185,400.00

Solution 3:

Cash Budget - Shilow company
Particulars April May June Quarter
Opening Cash balance $8,500.00 $4,320.00 $4,565.00 $8,500.00
Add: Collection from customers $69,600.00 $79,000.00 $96,000.00 $244,600.00
Total Cash Available $78,100.00 $83,320.00 $100,565.00 $253,100.00
Less - Cash Disbursement:
For Inventory $57,300.00 $67,875.00 $60,225.00 $185,400.00
For Expenses $16,980.00 $17,880.00 $22,380.00 $57,240.00
For Equipment $2,500.00 $0.00 $0.00 $2,500.00
Total Cash disbursement $76,780.00 $85,755.00 $82,605.00 $245,140.00
Excess (deficiency) of cash available over disbursements $1,320.00 -$2,435.00 $17,960.00 $7,960.00
Financing:
Borrowings $3,000.00 $7,000.00 $0.00 $10,000.00
Repayments $0.00 $0.00 -$10,000.00 -$10,000.00
Interest $0.00 $0.00 -$230.00 -$230.00
Total Financing $3,000.00 $7,000.00 -$10,230.00 -$230.00
Ending cash balance $4,320.00 $4,565.00 $7,730.00 $7,730.00

Solution 4:

Absorption costing income statement - Shilow Company
for quarter ended June 30
Particulars Amount
Sales $263,000.00
Cost of goods sold (75%) $197,250.00
Gross profit $65,750.00
Operating expenses:
Sales commission $31,560.00
Rent $9,900.00
Other expenses $15,780.00
Depreciation $2,727.00
Total operating expenses $59,967.00
Operating income $5,783.00
Interest expense $230.00
Net Income $5,553.00

Solution 5:

Balance Sheet- Shilow Company
30-Jun
Particulars Amount
Assets:
Cash $7,730.00
Accounts receivables ($106,000*40%) $42,400.00
Inventory $28,800.00
Building and equipment, net ($121,200 +$2,500 - $2,727) $120,973.00
Total Assets $199,903.00
Liabilities and stockholder's Equity:
Accounts payable (44,700*50%) $22,350.00
Common Stock $150,000.00
Retained Earnings ($22,000 + $5,553) $27,553.00
Total liabilities and stockholders equity $199,903.00
Add a comment
Know the answer?
Add Answer to:
Please create an Assumption page in excel for all given data. Creating a master budget where...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • Can you also do a sales budget for the given information? Thank you in advance The...

    Can you also do a sales budget for the given information? Thank you in advance The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods: Current assets as of March 31: Cash Accounts receivable Inventory Building and equipment, net Accounts payable Common stock Retained earnings $ 8,500 $ 24,000 $ 45, 600 $ 121,200 $ 27,300 $ 150,000 $ 22,000 June Quarter Shilow Company Cash Budget April May $ 8,500 69,600 78,100 a. The...

  • a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual)...

    a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) April May June July $ 60,000 $ 76,000 $ 81,000 $106,000 $ 57,000 c. Sales are 60% for cash and 40% on credit. Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result of March credit sales d. Each month's ending inventory should equal 80% of the following month's budgeted cost of goods sold. e One-half...

  • Problem 8-29 Completing a Master Budget (LO8-2, L08-4, LO8-7, LO8-8, LO8-9, LO8-10] The following data relate...

    Problem 8-29 Completing a Master Budget (LO8-2, L08-4, LO8-7, LO8-8, LO8-9, LO8-10] The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods: Current assets as of March 31: Cash Accounts receivable Inventory Building and equipment, net Accounts payable Common stock Retained earnings $ 7,400 $ 19,600 $ 39,000 $ 126,000 $ 23, 175 $ 150,000 $ 18,825 a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) April...

  • Problem 8-29 Completing a Master Budget [LO8-2, LO8-4, LO8-7, LO8-8, LO8-9, LO8-10] The following data relate...

    Problem 8-29 Completing a Master Budget [LO8-2, LO8-4, LO8-7, LO8-8, LO8-9, LO8-10] The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods: Current assets as of March 31: Cash $ 7,800 Accounts receivable $ 21,200 Inventory $ 41,400 Building and equipment, net $ 130,800 Accounts payable $ 24,675 Common stock $ 150,000 Retained earnings $ 26,525 The gross margin is 25% of sales. Actual and budgeted sales data: March (actual) $ 53,000 April $...

  • Problem 8-29 Completing a Master Budget (LO8-2, LO8-4, LO8-7, LO8-8, LO8-9, LO8-10] The following data relate...

    Problem 8-29 Completing a Master Budget (LO8-2, LO8-4, LO8-7, LO8-8, LO8-9, LO8-10] The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods: Current assets as of March 31: Cash Accounts receivable Inventory Building and equipment, net Accounts payable Common stock Retained earnings $ 8,600 $ 24,400 $ 46,200 $ 118,800 $ 27,675 $ 150,000 $ 20,325 a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) April May...

  • The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods:...

    The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods: Current assets as of March 31: Cash Accounts receivable Inventory Building and equipment, net Accounts payable Common stock Retained earnings $ 9, 100 $ 26,400 $ 49,200 $ 106,800 $ 29,550 $ 150,000 $ 11,950 a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) April May June Ouly $ 66,000 $ 82,000 $ 87,000 $ 112,000 $...

  • Can you please create an excel Assumption page with the given date to be used in...

    Can you please create an excel Assumption page with the given date to be used in a master budget that needs to be all referenced to the assumption page. Thank you The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods Current assets as of March 31: Cash 8,500 24,000 45,600 $121,200 $ 27,300 $150,000 $ 22,000 Accounts receivable Inventory Building and equipment, net Accounts payable Common stock Retained earnings a. The gross margin...

  • The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods:...

    The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods: Current assets as of March 31: Cash Accounts receivable Inventory Building and equipment, net Accounts payable Common stock Retained earnings $ 8,200 $ 22,800 $ 43,800 $ 128,400 $ 26,175 $ 150,000 $ 27,025 a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) April May June July $ 57,000 $ 73,000 $ 78,000 $ 103,000 $ 54,000...

  • The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods:...

    The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods: Current assets as of March 31: Cash $ 9,400 Accounts receivable $ 27,600 Inventory $ 51,000 Building and equipment, net $ 99,600 Accounts payable $ 30,675 Common stock $ 150,000 Retained earnings $ 6,925 The gross margin is 25% of sales. Actual and budgeted sales data: March (actual) $ 69,000 April $ 85,000 May $ 90,000 June $ 115,000 July $ 66,000 Sales are...

  • The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods:...

    The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods: Current assets as of March 31: Accounts receivable Inventory Building and equipment, net Accounts payable Common stock Retained earnings $ 8,400 $ 23,600 $ 45,000 $ 123,600 $ 26,925 $ 150,000 $ 23,675 a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) Apr 11 May June July $ 59,000 $ 75,000 $ 80.000 $ 105,000 $ 56,000...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT