Income Statement: Sales Variable Expenses: Manufacturing S&A Contribution margin Fixed Expenses: Manufacturing S&A Operating Income Interest Expense Net Income The Case Patriot Flag Company is a small manufacturing company. They manufacture only American flags. Their product is sold to retail outlets. Stores like Wal-Mart which operate in a close to JT environment, make frequent orders and expect fast deliveries. The information below pertains to the company's budgeting process Budgeted sales in flags (units) are as follows: April May June July August 20,000 50,000 30,000 25,000 15,000 . . Each unit sells for $9.50 All sales are on account. The company's collection pattern is: 70% collected the month of sale: 30% collected in the month following. The company desires to have inventory on hand at the end of each month equal to 20 percent of the following month's budgeted sales in units. On March 31, 4,000 units were on hand. Two yards of material are required per flag produced. The company desires to have materials on hand at the end of each month equal to 10 percent of the following month's production needs. The requirement was met on March 31. The material used in production costs $1.00 per yard. One half of the month's purchases is paid for in the month of purchases; the other half is paid for in the following month. No discount terms are available. The accounts payable as of March 31 were $12,000. Each flag requires 3 minutes of labor time to make (.05 hours) and the hourly help is paid $10/hour. Variable manufacturing overhead is $1 per unit produced Fixed manufacturing overhead is $50,sto per month including $20,500 in depreciation that is not a current cash outflow. All cash disbursements for manufacturing overhead are paid in the month incurred. Variable selling and administrative expenses are $1.00 per unit sold. Fixed selling and administrative expense is $70,000 per month including $10,000 in depreciation that is not a cash outflow of the current month. . All cash disbursements for selling and administrative costs are paid in the month incurred. Cash dividends in the amount of $51,000 are to be paid to shareholders in April. These dividends were declared in March. Equipment purchases and payments of $143,700 are scheduled for May and $48,800 for June. . Additionally, Patriot has the following balance sheet as of March 31, 20XX. Assets Liabilities and Equities Cash $40,000 Accounts payable Accounts Receivable 30,000 Notes Payable Raw materials inventory 5,200 Interest payable Finished goods inventory 14,000 Dividend payable Property, Plant and equipment, 320.000 Common Stock net Total assets $409.200 Retained earnings Total Liabilities and equities $12,000 $0 0 51,000 150,000 196 209 $409.200 The company must maintain a minimum cash balance of $30,000 each month. The cash balance at the beginning of April was $40,000. An open line of credit is available at a local bank, which allows the company to borrow up to $70,000 per quarter. (When preparing a budget, the minimum cash balance must ALWAYS be met even if it forecasts cash needs beyond the credit line available. If the available line of credit is insufficient, then alternative arrangements for financing must be made.) All borrowing is done at the beginning of a month, and all repayments are made at the end of a month. Interest is paid monthly on the first of the next month, thus each months interest paid is based on the outstanding borrowings the prior month. The interest rate is 10 percent per year. If there are excess funds above the minimum $30,000 cash at the end of the month, these are used to repay the outstanding loans. When computing interest and repayment watch the number of months the money was borrowed. The Company uses variable costing in its budgeted income statement and its balance sheet. Requirements 1. Prepare a master budget for the quarter ended June 30 that includes: Sales budget, schedule of expected cash collections, production budget, material purchases budget/schedule of expected cash payments for material, direct labor budget, manufacturing overhead budget, selling and administrative expense budget, cash budget, income statement and balance sheet. (A total of 10 schedules). Each schedule should be on a separate worksheet as appears in your template. There is a template in the assignments link and in the Excel Project folder with an input page you must use. All of your spreadsheets must be formula driven from the input tab! This means that EVERY cell should contain either a value referenced from the input worksheet or a formula manually entered the cell using the referenced values or the input values. The trickiest part of the assignment is computing the borrowings. You are expected to create logical formula here. Remember, the borrowings are at the beginning of the month and repayments at the end. If you borrow in January and repay in February, there will be 2 months of interest to be paid (beginning of January to end of February). Interest is paid the first of the next month. Check figures: Total Collections in April: $163,000 Raw Materials Purchases in June: $56,800 S&A Disbursements in May: $110,000 Accumulated Borrowings at the end of June: $46,917 Net Income: $136,224 Total Assets: $558,600