1. Create a master budget for a retail firm. I have attached an example excel worksheet.

In this case, you do not have a production budget, but you do have a direct materials budget for the purchase of inventory. Then you have all of the remaining budgets.

requirements are as follows:

Be sure that the budgets are consistent so that I can follow them all the way through and they balance.

Provide a summary that describes your product and the budgeting process, and why you made the choices that you did. Conclude with a paragraph that explains how your proposed budget has maintained or improved the financial health of your company, and what changes / improvements might be made for the following year. The summary does not have to be any longer than a page or two.

MBA 630 - Budget example Name: Information: This example will be for a "simplified" retail store that will only be selling one product. First of all, think of a strategy for your business. What is your overall plan? What do you hope to accomplish? Then when creating your budget, you can decide: - Do this as an "Operating budget" - so you are not considering "Ownership" so much as "Retail operations" - So you would have a store "Manager", and any sales staff who would be your "direct labor" - So, how much does your product "Cost"? This will be the Purchasing price of your inventory. - Then determine a "markup". - In other words, what would be the percentage charge, over and above cost, that would be the "price" of your product to the customer? Name: Schedule 1 - Revenues My-retail-Product Quarterly revenues Selling Units Total Price Sold Revenues $375 4,500 $ 1,687,500 I 337,500 II 421,875 III 421,875 IV 506,250 Estimate how many units will be sold in a year. Calculate your total annual Revenues. Then decide how your sales will be divided quarterly: - Will your sales be consistent throughout the year? Then just divide your total revenues by four. - Or do you have a seasonal business, where one or more quarters will have higher sales than the others? - Remember, the quarterly revenues must add up to the total. Schedule 3 - Direct Materials (Purchases) budget Budgeted unit sales (Sch. 1) Add target ending inventory Total required Less beginning inventory Units to be purchased 4,500 1,000 5,500 5,500 $180,000 (No beginning inventory - first year of operations) $990,000 Cost: $180 1,687,500 Schedule 4 - Direct Labor Manager's salary Partner benefits (40%) 50,000 20,000 70,000 Sales staff salaries Sales staff benefits (40%) 25,000 10,000 35,000 Total Direct labor 105,000 Enter salaries for your Manager and Sales staff Then add an "estimate" for how much you will pay in benefits. Schedule 5 - Overhead budget Support staff: Accounting Custodial Support staff benefits Office: Computer Office supplies Utilities: Electricity Heat/air conditioning Internet access Telephone Trash collection Other Building rent Advertising Insurance Other Depreciation Computer Total overhead 60,000 29,000 10,400 20,000 18,000 13,000 16,000 1,500 3,100 2,400 2,500 50,000 20,000 4,000 20,000 2,000 271,900 $ 99,400 - They may or may not apply to your particular business. - Be sure to describe in your explanation which overhead costs you included and why. - The cost of salaries and benefits for support staff will be in the "payroll" section of your cash budget. depreciation is calculated separately because it is NOT included in the cash budget (depreciation is a NON-CASH expense). 170,500 Other overhead besides salaries, before depreciation 269,900 Total overhead before depreciaiton Schedules 7, 8, 9 - Cash Budget Cash balance, beginning Add: receipts Current quarter Previous quarter Bank loan Quarters I 100,000 II 170,025 III 126,613 IV 101,638 Year 100,000 270,000 - 337,500 67,500 337,500 84,375 405,000 84,375 1,350,000 236,250 $250,000 250,000 Total cash available Deduct: disbursements Inventory Direct labor Overhead - payroll and benefits All other overhead (less depreciation) 620,000 575,025 548,488 591,013 247,500 26,250 24,850 42,625 247,500 26,250 24,850 42,625 247,500 26,250 24,850 42,625 990,000 105,000 99,400 170,500 Repayment on loan principal Interest cost on loan Income taxes Total disbursements Minimum cash balance desired Total cash needed Cash excess (deficiency) 62,500 6,250 40,000 449,975 100,000 549,975 70,025 62,500 4,688 40,000 448,413 100,000 548,413 26,613 62,500 3,125 40,000 446,850 100,000 546,850 1,638 62,500 1,563 40,000 445,288 100,000 545,288 45,725 250,000 15,625 160,000 1,790,525 100,000 1,890,525 45,725 170,025 126,613 101,638 145,725 145,725 Last quarter's sales were: $0 If you believe that some of your sales are uncollectible, then you may also use bad debt expense on the income statement (example in textbook). 1,936,250 247,500 26,250 24,850 42,625 Assumptions: 80% of sales are collected in the current quarter 20% of sales are collected from the previous quarter Cash balance, ending The company takes out a loan of $250,000 Annual interest rate is 10% The loan will be paid back by the end of the year The company pays "All other overhead" in quarterly payments (see formula, row 15) Estimated tax payments Cash available minus total cash needed Cash available - cash disbursements + financing (Note: I did not add any additional financing in this budget) Budgeted Income statement Revenues Expenses Cost of goods sold Direct labor Overhead Depreciation expense Operating income Interest expense Income before income taxes Income taxes Net income Schedule 1 Units sold x cost Schedule 4 Schedule 5 Schedule 5 $ 810,000 $ 105,000 269,900 2,000 $ $ Schedule 7-9 $ tax rate = 35% Depreciation expense should be shown separately. Interest expense should be shown separately. Income taxes should be shown separately. $ 1,687,500 1,186,900 500,600 15,625 484,975 169,741 315,234 Budgeted Balance Sheet Assets Current assets Cash (match BB for Qtr I on Sch. 7, 8, 9) Accounts receivable Inventory Name: Assets 2013 100,000 - Schedule 7-9 4th Quarter sales x 20% Inventory 2014 $ 145,725 101,250 180,000 100,000 Property, plant and equipment Equipment Accumulated depreciation Total Liabilities and Owners' equity Current liabilities Accounts payable Income taxes payable Total current liabilities Total liabilities Stockholders' equity Owners' equity Retained earnings Total 20,000 (2,000) 18,000 118,000 $ $ No change Accumulated depreciation 426,975 16,000 442,975 20,000 (4,000) Liabilities and Owners' equity 55,000 25,000 80,000 bb+Sch 3+Sch 5 (excl.depr)-Sch 7-9(lines 13,15,16) bb+Schedule 11-Schedule 7-9 55,000 34,741 89,741 80,000 38,000 - 89,741 No change 38,000 118,000 bb+NI(Schedule 11)-dividends 38,000 315,234 $ 353,234 442,975