Any help on this question would be greatly appreciated! I have attached the question in an excel sheet as well. Thanks!!!

Developing a Master Budget for a Merchandising Organization Peyton Department Store prepares budgets quarterly. The following information is available for use in planning the second quarter budgets for 2010. PEYTON DEPARTMENT STORE Balance Sheet 31-Mar-10 Assets Liabilities and Stockholders' Equity Cash $4,000 Accounts payable $26,000 Accounts receivable 25,000 Dividends payable 17,000 Inventory 30,000 Rent payable 3,000 Prepaid Insurance 2,000 Stockholders' equity 40,000 Fixtures 25,000 Total assets $86,000 Total liabilities and equity $86,000 Actual and forecasted sales for selected months in 2010 are as follows: Month Sales Revenue January $40,000 February 50,000 March 40,000 April 50,000 May 60,000 June 70,000 July 90,000 August 80,000 Monthly operating expenses are as follows: Wages and salaries $26,000 Depreciation 100 Utilities 1,000 Rent 3,000 Cash dividends of $17,000 are declared during the third month of each quarter and are paid during the first month of the following quarter. Operating expenses, except insurance, rent, and depreciation are paid as incurred. Rent is paid during the following month. The prepaid insurance is for five more months. Cost of goods sold is equal to 50 percent of sales. Ending inventories are sufficient for 120 percent of the next month's sales. Purchases during any given month are paid in full during the following month. All sales are on account, with 50 percent collected during the month of sale, 40 percent during the next month, and 10 percent during the month thereafter. Money can be borrowed and repaid in multiples of $1,000 at an interest rate of 12 percent per year. The company desires a minimum cash balance of $4,000 on the first of each month. At the time the principal is repaid, interest is paid on the portion of principal that is repaid. All borrowing is at the beginning of the month, and all repayment is at the end of the month. Money is never repaid at the end of the month it is borrowed. a) Prepare a purchases budget for each month of the second quarter ending June 30, 2010. Peyton Department Store Monthly Purchase Budget Quarter Ending June 30, 2010 April May June Total Budgeted purchases (b) Prepare a cash receipts schedule for each month of the second quarter ending June 30, 2010. Do not include borrowings. Peyton Department Store Schedule of Monthly Cash Receipts Quarter Ending June 30, 2010 April May June Total Total cash receipts (c) Prepare a cash disbursements schedule for each month of the second quarter ending June 30, 2010. Do not include repayments of borrowings. Peyton Department Store Schedule of Monthly Cash Disbursements Quarter Ending June 30, 2010 April May June Total Total cash disbursements (d) Prepare a cash budget for each month of the second quarter ending June 30, 2010. Include budgeted borrowings and repayments. Only use negative signs, if needed, for:excess receipts over disbursements, balance before borrowings andcash balances (beginning and ending). Peyton Department Store Monthly Cash Budget Quarter Ending June 30, 2010 April May June Total Cash balance, beginning Receipts Disbursements Excess receipts over disb. Balance before borrowings Borrowings Loan repayments Cash balance, ending (e) Prepare an income statement for each month of the second quarter ending June 30, 2010. Only use negative signs to show net losses in income. Peyton Department Store Budgeted Monthly Income Statements Quarter Ending June 30, 2010 April May June Total Sales Cost of sales Gross profit Operating expenses: Wages and salaries Depreciation Utilities Rent Insurance Interest Total expenses Net income (f) Prepare a budgeted balance sheet as of June 30, 2010. Peyton Department Store Budgeted Balance Sheet 30-Jun-10 Assets Liabilities and Equity Cash Merchandise payable

Accounts receivable Dividend payable Inventory Rent payable Prepaid insurance Loans payable Fixtures Interest payable Total assets Stockholders' equity Total liab. & equity Developing a Master Budget for a Merchandising Organization Peyton Department Store prepares budgets quarterly. The following information is available for use in planning the second quarter budgets for 2010. PEYTON DEPARTMENT STORE Balance Sheet 31-Mar-10 Liabilities and Stockholders' Equity Assets Cash $4,000 Accounts payable $26,000 Accounts receivable 25,000 Dividends payable 17,000 Inventory 30,000 Rent payable 3,000 2,000 Stockholders' equity 40,000 $86,000 Total liabilities and equity $86,000 Prepaid Insurance Fixtures 25,000 Total assets Actual and forecasted sales for selected months in 2010 are as follows: Sales Revenue Month January $40,000 February 50,000 March 40,000 April 50,000 May 60,000 June 70,000 July 90,000 August 80,000 Monthly operating expenses are as follows: Wages and salaries $26,000 Depreciation 100 Utilities 1,000 Rent 3,000 Cash dividends of $17,000 are declared during the third month of each quarter and are paid during the first month of the following quarter. Operating expenses, except insurance, rent, and depreciation are paid as incurred. Rent is paid during the following month. The prepaid insurance is for five more months. Cost of goods sold is equal to 50 percent of sales. Ending inventories are sufficient for 120 percent of the next month's sales. Purchases during any given month are paid in full during the following month. All sales are on account, with 50 percent collected during the month of sale, 40 percent during the next month, and 10 percent during the month thereafter. Money can be borrowed and repaid in multiples of $1,000 at an interest rate of 12 percent per year. The company desires a minimum cash balance of $4,000 on the first of each month. At the time the principal is repaid, interest is paid on the portion of principal that is repaid. All borrowing is at the beginning of the month, and all repayment is at the end of the month. Money is never repaid at the end of the month it is borrowed. a) Prepare a purchases budget for each month of the second quarter ending June 30, 2010. Peyton Department Store Monthly Purchase Budget Quarter Ending June 30, 2010 April Budgeted purchases (b) Prepare a cash receipts schedule for each month of the second quarter ending June 30, 2010. Do not include borrowings. May June Total Peyton Department Store Schedule of Monthly Cash Receipts Quarter Ending June 30, 2010 April May June Total May June Total May June Total June Total Total cash receipts (c) Prepare a cash disbursements schedule for each month of the second quarter ending June 30, 2010. Do not include repayments of borrowings. Peyton Department Store Schedule of Monthly Cash Disbursements Quarter Ending June 30, 2010 April Total cash disbursements (d) Prepare a cash budget for each month of the second quarter ending June 30, 2010. Include budgeted borrowings and repayments. Only use negative signs, if needed, for: excess receipts over disbursements, balance before borrowings and cash balances (beginning and ending). Peyton Department Store Monthly Cash Budget Quarter Ending June 30, 2010 April Cash balance, beginning Receipts Disbursements Excess receipts over disb. Balance before borrowings Borrowings Loan repayments Cash balance, ending (e) Prepare an income statement for each month of the second quarter ending June 30, 2010. Only use negative signs to show net losses in income. Peyton Department Store Budgeted Monthly Income Statements Quarter Ending June 30, 2010 April May Sales Cost of sales Gross profit Operating expenses: Wages and salaries Depreciation Utilities Rent Insurance Interest Total expenses Net income (f) Prepare a budgeted balance sheet as of June 30, 2010. Peyton Department Store Budgeted Balance Sheet 30-Jun-10 Assets Cash Accounts receivable Inventory Prepaid insurance Fixtures Total assets Liabilities and Equity Merchandise payable Dividend payable Rent payable Loans payable Interest payable Stockholders' equity Total liab. & equity \f(a) Prepare a purchases budget for each month of the second quarter ending June 30, 2014. Peyton Department Store Monthly Purchase Budget Quarter Ending June 30, 2014 Particulars Budgeted Sales revenue Cost of Goods Sold Add desired ending inventory Total needs Less beginning inventory Budgeted purchases April May 50,000 25,000 36,000 61,000 (30,000) 31,000 June 60,000 30,000 42,000 72,000 (36,000) 36,000 70,000 35,000 54,000 89,000 (42,000) 47,000 Total 180,000 90,000 54,000 144,000 (30,000) 114,000 b. Prepare a cash receipts schedule for each month of the second quarter ending June 30, 2014. Do not include borro Peyton Department Store Budgeted Cash Receipt Schedule Quarter Ending June 30, 2014 Particulars Budgeted Sales revenue 50% collected during the month 40% collected during the next month 10% collected in the second month Total Cash Receipts April May 50,000 25,000 16,000 5,000 46,000 June 60,000 30,000 20,000 4,000 54,000 70,000 35,000 24,000 5,000 64,000 Total 180,000 90,000 60,000 14,000 164,000 c. Prepare a cash disbursements schedules for each month of the second quarter ending June 30, 2014. Do not includ Peyton Department Store Budgeted Cash Disbursements Schedules Quarter Ending June 30, 2014 Particulars Budgeted purchases Payment for March Purchases Payment made in the following month Total Cash Disbursements for Purchas Wages & salaries Utilities Rent Dividend Payable Total Cash Disbursements April May 31,000 26,000 June 36,000 31,000 31,000 25,000 1,000 2,000 36,000 36,000 ### ### ### 59,000 26,000 25,000 1,000 2,000 17,000 71,000 47,000 64,000 Total 114,000 26,000 67,000 93,000 75,000 3,000 6,000 17,000 194,000 d. Prepare a cash budget for each month of the second quarter ending June 30, 2014. Include budgeted borrowings and rep Peyton Department Store Budgeted Monthly Cash Budget Quarter Ending June 30, 2014 Particulars Beginning Balance of Cash Add Receipts Total Cash Available Less disbursements Excess of Cash Receipts Over Disburse Balance before Borrowings Borrowings loan Repayment Interest Ending Balance April May June Total 3,000 46,000 49,000 (71,000) (25,000) (22,000) 25,000 3,000 54,000 57,000 (59,000) (5,000) (2,000) 5,000 3,000 64,000 67,000 (64,000) 3,000 - 3,000 164,000 167,000 (194,000) (30,000) (27,000) 30,000 3,000 3,000 3,000 3,000 e. Prepare an income statement for each month of the second quarter ending June 30, 2014. Peyton Department Store Budgeted Monthly Income Statement Quarter Ending June 30, 2014 Particulars April Sales Cost of sales Gross profit Operating expenses: Wages and salaries Depreciation Utilities Rent Insurance Interest Total expenses Net income May June 50,000 (25,000) 25,000 60,000 (30,000) 30,000 70,000 (35,000) 35,000 180,000 (90,000) 90,000 25,000 100 1,000 2,000 400 250 28,750 (3,750) 25,000 100 1,000 2,000 400 300 28,800 1,200 25,000 ### 1,000 2,000 400 300 28,800 6,200 75,000 300 3,000 6,000 1,200 850 86,350 3,650 f. Prepare a budgeted balance sheet as of June 2014. Peyton Department Store Budgeted Balancesheet Quarter Ending June 30, 2014 Assets Cash Accounts Receivable Inventory Prepaid Insurance Fixtures Less Depreciation Total Assets Total Amount $ 3,000 41,000 54,000 800 25000 (300) Liabilities & Equity Accounts Payable Dividend Payable Rent Payable Loan Payable Interest Payable 24,700 Stockholder's Equity Total Liabilities & Stockholder's Equity 123,500 Amount $ 47,000 17,000 2,000 30,000 850 26,650 123,500 ude borrowings. not include repayments of borrowings. ngs and repayments. (a) Prepare a purchases budget for each month of the second quarter ending June 30, 2014. Peyton Department Store Monthly Purchase Budget Quarter Ending June 30, 2014 Particulars Budgeted Sales revenue Cost of Goods Sold Add desired ending inventory Total needs Less beginning inventory Budgeted purchases April May 50,000 25,000 36,000 61,000 (30,000) 31,000 June 60,000 30,000 42,000 72,000 (36,000) 36,000 70,000 35,000 54,000 89,000 (42,000) 47,000 Total 180,000 90,000 54,000 144,000 (30,000) 114,000 b. Prepare a cash receipts schedule for each month of the second quarter ending June 30, 2014. Do not include borro Peyton Department Store Budgeted Cash Receipt Schedule Quarter Ending June 30, 2014 Particulars Budgeted Sales revenue 50% collected during the month 40% collected during the next month 10% collected in the second month Total Cash Receipts April May 50,000 25,000 16,000 5,000 46,000 June 60,000 30,000 20,000 4,000 54,000 70,000 35,000 24,000 5,000 64,000 Total 180,000 90,000 60,000 14,000 164,000 c. Prepare a cash disbursements schedules for each month of the second quarter ending June 30, 2014. Do not includ Peyton Department Store Budgeted Cash Disbursements Schedules Quarter Ending June 30, 2014 Particulars Budgeted purchases Payment for March Purchases Payment made in the following month Total Cash Disbursements for Purchas Wages & salaries Utilities Rent Dividend Payable Total Cash Disbursements April May 31,000 26,000 June 36,000 31,000 31,000 25,000 1,000 2,000 36,000 36,000 ### ### ### 59,000 26,000 25,000 1,000 2,000 17,000 71,000 47,000 64,000 Total 114,000 26,000 67,000 93,000 75,000 3,000 6,000 17,000 194,000 d. Prepare a cash budget for each month of the second quarter ending June 30, 2014. Include budgeted borrowings and rep Peyton Department Store Budgeted Monthly Cash Budget Quarter Ending June 30, 2014 Particulars Beginning Balance of Cash Add Receipts Total Cash Available Less disbursements Excess of Cash Receipts Over Disburse Balance before Borrowings Borrowings loan Repayment Interest Ending Balance April May June Total 3,000 46,000 49,000 (71,000) (25,000) (22,000) 25,000 3,000 54,000 57,000 (59,000) (5,000) (2,000) 5,000 3,000 64,000 67,000 (64,000) 3,000 - 3,000 164,000 167,000 (194,000) (30,000) (27,000) 30,000 3,000 3,000 3,000 3,000 e. Prepare an income statement for each month of the second quarter ending June 30, 2014. Peyton Department Store Budgeted Monthly Income Statement Quarter Ending June 30, 2014 Particulars April Sales Cost of sales Gross profit Operating expenses: Wages and salaries Depreciation Utilities Rent Insurance Interest Total expenses Net income May June 50,000 (25,000) 25,000 60,000 (30,000) 30,000 70,000 (35,000) 35,000 180,000 (90,000) 90,000 25,000 100 1,000 2,000 400 250 28,750 (3,750) 25,000 100 1,000 2,000 400 300 28,800 1,200 25,000 ### 1,000 2,000 400 300 28,800 6,200 75,000 300 3,000 6,000 1,200 850 86,350 3,650 f. Prepare a budgeted balance sheet as of June 2014. Peyton Department Store Budgeted Balancesheet Quarter Ending June 30, 2014 Assets Cash Accounts Receivable Inventory Prepaid Insurance Fixtures Less Depreciation Total Assets Total Amount $ 3,000 41,000 54,000 800 25000 (300) Liabilities & Equity Accounts Payable Dividend Payable Rent Payable Loan Payable Interest Payable 24,700 Stockholder's Equity Total Liabilities & Stockholder's Equity 123,500 Amount $ 47,000 17,000 2,000 30,000 850 26,650 123,500 ude borrowings. not include repayments of borrowings. ngs and repayments. Problem 2- Developing a Master Budget for a Merchandising Organization Peyton Department Store prepares budgets quarterly. The following information is available for use in planning the second quarter budgets for 2014. Department Store alance Sheet March 31,2014 Assets Liabilities and Stockholder's Equity Cash...............................................$3,000 Accounts payable.......................$26,000 Accounts receivable....................25,000 Dividends payable..........................17,000 Inventory......................................30,000 Rent payable.....................................2,000 Prepaid insurance........................2,000 Stockholders' equity.......................40,000 Fixtures.........................................25,000 Total Assets..................................$85,000 Total liabilities and equity...............$85,000 Actual and forecasted sales for selected months in 2014 are as follows: Month Sales Revenue January..................................................................................................................................... $60,000 February.......................................................................................................................................50,000 March............................................................................................................................................40,000 April................................................................................................................................................50,000 May.................................................................................................................................................60,000 June................................................................................................................................................70,000 July...................................................................................................................................................90,000 August..............................................................................................................................................80,000 Monthly operating expenses are as follows: Wages and salaries.......................................................................................................................$25,000 Depreciation..........................................................................................................................................100 Utilities...................................................................................................................................................1000 Rent......................................................................................................................................................2,000 Cash dividends of $17,000 are declared during the third month of each quarter and are paid during the first month of the following quarter. Operating expenses, exc Required a. Prepare a purchases budget for each month of the second quarter ending June 30, 2014. b. Prepare a cash receipts schedule for each month of the second quarter ending June 30, 2014. Do not include borrowings. c. Prepare a cash disbursements schedules for each month of the second quarter ending June 30, 2014. Do not include repayments of borrowings. d. Prepare a cash budget for each month of the second quarter ending June 30, 2014. Include budgeted borrowings and repayments. e. Prepare an income statement for each month of the second quarter ending June 30, 2014. f. Prepare a budgeted balance sheet as of June 2014. except insurance, rent and depreciation are paid as incurred. Rent is paid during the following month. The prepaid insurance is for five more months. Cost of goods sold is equal to to 50 percent of sales. Ending inventories are sufficient for 120 percent of the next month's sales. Purchases during any given month are paid in full during the following month. All All sales are on account, with 50 percent collected during the month of sale, 40 percent during the next month, and 10 percent during the month thereafter. Money can be borrow owed and repaid in multiples of $1000 at an interest rate of 12 percent per year. The company desires a minimum cash balance of $3000 on the first of each month. At the time the the principal is repaid, interest is paid on the portion of principal that is repaid. All borrowing is at the beginning of the month, and as repayment is at the end of the month. Mone ney is never repaid at the end of the month it is borrowed. Developing a Master Budget for a Merchandising Organization Peyton Department Store prepares budgets quarterly. The following information is available for use in planning the second quarter budgets for 2010. PEYTON DEPARTMENT STORE Balance Sheet 31-Mar-10 Liabilities and Stockholders' Equity Assets Cash $4,000 Accounts payable $26,000 Accounts receivable 25,000 Dividends payable 17,000 Inventory 30,000 Rent payable 3,000 2,000 Stockholders' equity 40,000 $86,000 Total liabilities and equity $86,000 Prepaid Insurance Fixtures 25,000 Total assets Actual and forecasted sales for selected months in 2010 are as follows: Sales Revenue Month January $40,000 February 50,000 March 40,000 April 50,000 May 60,000 June 70,000 July 90,000 August 80,000 Monthly operating expenses are as follows: Wages and salaries $26,000 Depreciation 100 Utilities 1,000 Rent 3,000 Cash dividends of $17,000 are declared during the third month of each quarter and are paid during the first month of the following quarter. Operating expenses, except insurance, rent, and depreciation are paid as incurred. Rent is paid during the following month. The prepaid insurance is for five more months. Cost of goods sold is equal to 50 percent of sales. Ending inventories are sufficient for 120 percent of the next month's sales. Purchases during any given month are paid in full during the following month. All sales are on account, with 50 percent collected during the month of sale, 40 percent during the next month, and 10 percent during the month thereafter. Money can be borrowed and repaid in multiples of $1,000 at an interest rate of 12 percent per year. The company desires a minimum cash balance of $4,000 on the first of each month. At the time the principal is repaid, interest is paid on the portion of principal that is repaid. All borrowing is at the beginning of the month, and all repayment is at the end of the month. Money is never repaid at the end of the month it is borrowed. a) Prepare a purchases budget for each month of the second quarter ending June 30, 2010. Peyton Department Store Monthly Purchase Budget Quarter Ending June 30, 2010 April May June Total May June Total Budgeted purchases (b) Prepare a cash receipts schedule for each month of the second quarter ending June 30, 2010. Do not include borrowings. Peyton Department Store Schedule of Monthly Cash Receipts Quarter Ending June 30, 2010 April Total cash receipts (c) Prepare a cash disbursements schedule for each month of the second quarter ending June 30, 2010. Do not include repayments of borrowings. Peyton Department Store Schedule of Monthly Cash Disbursements Quarter Ending June 30, 2010 April May June Total May June Total June Total Total cash disbursements (d) Prepare a cash budget for each month of the second quarter ending June 30, 2010. Include budgeted borrowings and repayments. Only use negative signs, if needed, for: excess receipts over disbursements, balance before borrowings and cash balances (beginning and ending). Peyton Department Store Monthly Cash Budget Quarter Ending June 30, 2010 April Cash balance, beginning Receipts Disbursements Excess receipts over disb. Balance before borrowings Borrowings Loan repayments Cash balance, ending (e) Prepare an income statement for each month of the second quarter ending June 30, 2010. Only use negative signs to show net losses in income. Peyton Department Store Budgeted Monthly Income Statements Quarter Ending June 30, 2010 April May Sales Cost of sales Gross profit Operating expenses: Wages and salaries Depreciation Utilities Rent Insurance Interest Total expenses Net income (f) Prepare a budgeted balance sheet as of June 30, 2010. Peyton Department Store Budgeted Balance Sheet 30-Jun-10 Assets Cash Accounts receivable Inventory Prepaid insurance Fixtures Total assets Liabilities and Equity Merchandise payable Dividend payable Rent payable Loans payable Interest payable Stockholders' equity Total liab. & equity (a) Prepare a purchases budget for each month of the second quarter ending June 30, 2010. Peyton Department Store Monthly Purchase Budget Quarter Ending June 30, 2010 Particulars Budgeted Sales revenue Cost of Goods Sold Add desired ending inventory Total needs Less beginning inventory Budgeted purchases April May 50,000 25,000 36,000 61,000 (30,000) 31,000 June 60,000 30,000 42,000 72,000 (36,000) 36,000 70,000 35,000 54,000 89,000 (42,000) 47,000 Total 180,000 90,000 54,000 144,000 (30,000) 114,000 b. Prepare a cash receipts schedule for each month of the second quarter ending June 30, 2010. Do not include borro Peyton Department Store Budgeted Cash Receipt Schedule Quarter Ending June 30, 2010 Particulars Budgeted Sales revenue 50% collected during the month 40% collected during the next month 10% collected in the second month Total Cash Receipts April May 50,000 25,000 16,000 5,000 46,000 June 60,000 30,000 20,000 4,000 54,000 70,000 35,000 24,000 5,000 64,000 Total 180,000 90,000 60,000 14,000 164,000 c. Prepare a cash disbursements schedules for each month of the second quarter ending June 30, 2010. Do not includ Peyton Department Store Budgeted Cash Disbursements Schedules Quarter Ending June 30, 2010 Particulars Budgeted purchases Payment for March Purchases Payment made in the following month Total Cash Disbursements for Purchas Wages & salaries Utilities Rent Dividend Payable Total Cash Disbursements April May 31,000 26,000 June 36,000 31,000 31,000 26,000 1,000 3,000 36,000 36,000 ### ### ### 61,000 26,000 26,000 1,000 3,000 17,000 73,000 47,000 66,000 Total 114,000 26,000 67,000 93,000 78,000 3,000 9,000 17,000 200,000 d. Prepare a cash budget for each month of the second quarter ending June 30, 2010. Include budgeted borrowings and r Peyton Department Store Budgeted Monthly Cash Budget Quarter Ending June 30, 2010 Particulars Beginning Balance of Cash Add Receipts Total Cash Available Less disbursements Excess of Cash Receipts Over Disburse Balance before Borrowings Borrowings loan Repayment Interest Ending Balance April 4,000 46,000 50,000 (73,000) (27,000) (23,000) 25,000 2,000 May June 2,000 54,000 56,000 (61,000) (7,000) (5,000) 5,000 - Total 64,000 64,000 (66,000) (2,000) (2,000) - 4,000 164,000 168,000 (200,000) (36,000) (32,000) 30,000 (2,000) (2,000) e. Prepare an income statement for each month of the second quarter ending June 30, 2010. Peyton Department Store Budgeted Monthly Income Statement Quarter Ending June 30, 2010 Particulars April Sales Cost of sales Gross profit Operating expenses: Wages and salaries Depreciation Utilities Rent Insurance Interest Total expenses Net income May June 50,000 (25,000) 25,000 60,000 (30,000) 30,000 70,000 (35,000) 35,000 180,000 (90,000) 90,000 26,000 100 1,000 3,000 400 250 30,750 (5,750) 26,000 100 1,000 3,000 400 300 30,800 (800) 26,000 ### 1,000 3,000 400 300 30,800 4,200 78,000 300 3,000 9,000 1,200 850 92,350 (2,350) f. Prepare a budgeted balance sheet as of June 2010. Peyton Department Store Budgeted Balancesheet Quarter Ending June 30, 2010 Assets Cash Accounts Receivable Inventory Prepaid Insurance Fixtures Less Depreciation Total Assets Total Amount $ (2,000) 41,000 54,000 800 25000 (300) Liabilities & Equity Accounts Payable Dividend Payable Rent Payable Loan Payable Interest Payable 24,700 Stockholder's Equity Total Liabilities & Stockholder's Equity 118,500 Amount $ 47,000 17,000 3,000 30,000 850 20,650 118,500 ude borrowings. not include repayments of borrowings. wings and repayments.