Budgeting Project - Basic Instructions The project should be completed in Excel When you have finished the project, you need to upload your spreadsheet in the Blackboard assignment link as well as complete the test portion in Blackboard. Make sure your name is on the input's worksheet in the yellow box, Introduction I The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. There is a lot to do here, so get started right away. 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 IT environment, make frequent orders and expect fast deliveries. The information below pertains to the company's budgeting process. Bud-t-bed 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 helpis pald $10/hour. Variable manufacturing overhead is $1 per unit produced Fixed manufacturing overhead is $50,500 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 $.50 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 purcha 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, 321.000 Common Stock net Total assets $409.200 Retained earnings Total Liabilities and equities $12,000 $0 0 51,000 150,000 196.200 $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 tabl 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 es M 10000 Acre tak inte 3200 14 de Preparado Collection M M Acor 100 Goodie Entry Baggery Needed Ending my Padmash of purchase Pinowing more sos $12.000 Direct later flerede Mardering Overhead 1 100 produced 50 500 Nocash fand insbowe Selling Expenses Variat Nocashed need in bove Gas Beginning balance um cashbance Barrowing rate Borrowing $ 40.000 $ 0.000 10 Beginning of man Other vidende Equipment purchase Ement purchase $ 51.000 in April $143.700 in May SAMOO in June . Cash Budget S&A Budget OH Budget DL Budget RM Budget Production Budget Input Sales Budget Collections Budget dy A B D E F G H Sales Budget Unit sales Selling price Total sales April May June 20,000 50,000 30,000 $ 9.50 $ 9.50 $ 9.50 $190,000 $475,000 $285,000 Total 100,000 29 950,000 B D E F G Cash Collections Budget Current Month Prior Month Total April May June Total $133,000 $332,500 $199,500 $665,000 $ 30,000 / $ 57,000 $142,500 $229,500 $ 163,000 $389,500 $342,000 $894,500 A B D E F G H July 25,000 Production Budget Sales in Units Desired ending inventory Total requirements Less: beginning inventory Production in Units April May June Total 20,000 50,000 30,000 100,000 10,000 6,000 5,000 21,000 4,000 4,000 10,000 B D E F G H April May June Total July 25,000 2 52.000 10,000 26,000 Raw Materials Budget Units Produced Required per unit Total used in production Desired ending inventory Total requirements Less: beginning inventory Raw material purchases - units Cost per unit Raw material purchases - $ A B D E F G H April May June Total Direct Labor Budget: Units produced Labor hours per unit Total labor hours Rate Direct labor cost B D E F G May June Total Overhead Budget: Units produced Variable overhead per unit Total variable overhead Fixed overhead Total overhead Noncash overhead expenses Overhead Disbursements April 500 $ 1.00 $ 500 $50,500 $51,000 $20,500 $30,500 Jx B E F May June Total April 190,000 $ 1.00 Selling/Admininstrative Budget Units sold Variable S&A per unit Total Variable S&A Fixed S&A Total S&A Noncash S&A expenses S&A disbursements A B D E F April $40,000 May June Quarter $30,500 Cash Budget: Beginning balance Collections Available balance Disbursements: Direct material prior Direct material current Direct labor Overhead S&A Dividends Equipment purchases Interest Total disbursements Balance before financing Minimum balance Excess (deficiency) Borowing Accumulated borrowings at beginning of month Repayments Ending balance $30,000 $0 SO $0 Accumulated Borrowings at end of month: B C D E Income Statement: Sales Variable Expenses: Manufacturing S&A Contribution margin Fixed Expenses: Manufacturing S&A Operating Income Interest Expense Net Income B D E F Budgeted Balance Sheet Cash Accounts receivable Finished Goods Inventory Raw Material Inventory Equipment Total Assets Accounts payable Notes payable Interest payable Common Stock Retained earings Total Liabilities & Equity Working notes: Budgeted Sales Add: Closing inventory Total Needs Less: Opening Inventory Production Budget Production Budget April 20,000 10,000 30,000 4,000 26,000 May 50,000 6,000 56,000 10,000 46,000 June 30,000 5,000 35,000 6,000 29,000 Total 100,000 5,000 105,000 4,000 101,000 July August 25,000 15,000 3,000 28,000 5,000 23,000 July 23,000 2 46,000 2 Raw Materials Budget April Production units 26,000 Material needed per production unit (yards) Total used in production 52,000 Add: Closing inventory 9,200 Total Requirements 61,200 Less: Opening Inventory 5,200 Raw material purchases - unit 56,000 Cost per unit $1.00 Raw Material purchases - $ $56,000 0 May 46,000 2 92,000 5,800 97,800 9,200 88,600 $1.00 $88,600 June Total 29,000 101,000 2 2 58,000 202,000 4,600 4,600 62,600 206,600 5,800 5,200 56,800 201,400 $1.00 $1.00 $56,800 $201,400 Beginning inventory = 52,000*10% Ending inventory = 46,000*10% Production Budget Quarter April 50,000 20% 10,000 20,000 30,000 May 30,000 20% 6.000 50,000 56,000 June 25.000 20% 5.000 30,000 35.000 For working July 15,000 20% 3,000 25,000 28,000 Next Month's Expected Sale Units Ratio of Ending Inventory to unit sales Desired Finished Goods Ending Inventory Plus: Budgeted units sales Required units of available production Less: Estimated Finished Goods Beginning Inventory (Ending Inventory of last month) Units to be produced Direct labor budget Direct labor budget 4,000 10,000 6,000 5,000 23,000 26,000 46,000 29,000 101,000 Total May 46,000 0.05 Budgeted Production Units (Refer Note 2) Labor Hour per unit Total required direct labor time Direct labor rate per hour Budgeted direct labor in dollars 2) Overhead Budget April 26.000 0.05 1300 $10.00 S13.000 June 29.000 0.05 1450 $10.00 $14,500 2300 $10.00 $23,000 S50,500 Total S101,000 Budgeted Total Direct Labor Hours Variable overhead per unit Total Variable Overhead Fixed Overhead Total Overhead Noncash overhead expense Overhead Disbursements 3) Selling Administrative Budget April 26,000 $1 $26.000 $50,500 $76,500 S20.500 $56,000 May 46.000 SI $46,000 $50,500 $96,500 $20.500 $76,000 June 29.000 SI S29,000 $50,500 $79,500 S20.500 $59,000 S252,500 $191,000 Units sold Variable S&A per unit Total Variable S&A Fixed S&A Total S&A Nocash S&A Expense S&A Disbursements April 20000 $1 $20,000 $70,000 $90,000 $10,000 $80,000 May 50000 Si $50,000 S70,000 $120,000 S10,000 $110.000 June 30000 $1 $30,000 $70,000 $100,000 $10.000 $90,000 Total 100000 si $100,000 S210,000 S310,000 S30,000 S280,000