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
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 |
Please create an Assumption page in excel for all given data. Creating a master budget where...
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) 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 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 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 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: 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 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: 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: 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: 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...