Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF Budget project Home Insert Draw Page Layout Formulas Data Review View Developer Calibri v 11 A A E do v General Paste QvAv E 3 V $ ~ % " Co Recover Unsaved Workbooks. We were able to save changes to one or more files. Do you want to recover them? A15 X V fx A B C D E F G 29 30 (Enter your company name here) 31 Production Budget 32 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr YEAR Points Avai 33 Sales (in units) Points 34 Add: Desired Ending Finished Goods Inv. 35 show calculations here: 36 Total Needs 37 Less: Beg. Finished Goods Inv. 38 show calculations here: 39 Units to be produced 40 41 Additional information you will need for Cost of Goods Sold Budget: For cost of goods sold, the production costs include direct materials ($22 per unit), direct labor ($22 per unit), and overhead (applied at 150% of direct 42 labor). 43 44 (Enter your company name here) 45 Cost of Goods Sold 46 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr YEAR 47 Direct materials Points Avai 48 show calculations here: Points 49 Direct Labor 50 show calculations here: 51 Overhead 52 show calculations here: 53 Total Cost of goods sold 54 55 56 Additional information you will need for Selling and Admin Expense (or Operating Expense) Budget: Selling expenses include sales commissions of 9% of sales revenue and sales salaries of $27920 per quarter. Administrative expenses include office 57 building depreciation of $3710 per quarter and office salaries of $76100 per quarter. 58 59 (Enter your company name here) 60 Selling and Admin Expense Budget 61 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr YEAR Points Ava Project Settings DEC 32 381 5 MacBook AirExcel File Edit View Insert Format Tools Data Window Help Auto Save OFF Budget Developer Home Insert Draw Page Layout Formulas Data Review View Calibri 11 A A Y ab General Paste E $ ~ % 9 Recover Unsaved Workbooks. We were able to save changes to one or more files. Do you want to recover them? A15 + XV fx A B C D E F 58 59 (Enter your company name here) 60 Selling and Admin Expense Budget 61 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr YEAR 62 Selling expenses: 63 Sales commissions 64 Sales salaries 65 Total selling expense 66 67 Administrative expenses: 68 Depreciation expense - office bidg 69 Office salaries 70 Total Administrative expenses 71 72 Total Selling and Admin Expenses 73 Less: Non-cash Expenses 74 Total Cash Payments for Selling & Admin 75 76 77 (Enter your company name here) 78 Budgeted Income Statement 79 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr YEAR 80 Sales Revenue 81 Less; Cost of goods sold 82 Gross Profit (or Gross Margin) 83 Selling and Admin Expenses 84 Net Income 85 86 87 Additional information you will need for Schedule of Cash Collections: All Sales are made on credit. Experience shows that 75% of cash collections are received in the same quarter as the sale and the remaining 25% are collected in the quarter after the sale. Outstanding accounts receivable on Jan 1 is $79800 and will be paid in the first quarter. SHOW ALL CALCULATIONS 88 CLEARLY. 89 90 (Enter your company name here) 91 Schedule of Cash Collections from Customers Project Settings + DEC 32 5 ?EExcel File Edit View Insert Format Tools Data Window Help O Auto Save OFF Budget Home Insert Draw Page Layout Formulas Data Review View Developer Calibri 11 A A al Wrap Text General Paste B I U V A Merge & Center $ ~ % Recover Unsaved Workbooks. We were able to save changes to one or more files. Do you want to recover them? 24 + X V fx C D E F G Problem 1: BUDGETING You will complete the following budgets for the year (by quarter) in a professional format (complete with appropriate labels, subtotals, and calculations). 1. Sales Budget 2. Production Budget 3. Cost of goods sold Budget 4. Selling and admin expense budget (or operating expense budget) 5. Budgeted Income Statement 6. Cash Budget (including schedules of cash receipts from customers and cash payments for purchases) Additional information you will need for Sales Budget: Pick your Company Name and the product you will sell. Budgeted sales units have been provided for you in the Sales Budget and your budgeted sales price is $98 per unit. Budget Project for Manufacturing Company: (Enter your company name here) (Enter your company name here) Sales Budget 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr YEAR Sales (in Units) 12,510 13,140 13,400 14,070 53,120 price per unit $98.00 $98.00 $98.00 $98.00 $98.00 total gross sales $1,225,980.00 $1,287,720.00 $1,313,200.00 $1,378,860.00 $5,205,760.00 What type of company is this (meaning, what product are you making)? Additional information you will need for Production Budget: Company policy is to maintain 35% of next quarter's sales units in finished goods inventory. First quarter's beginning finished goods inventory is estimated at 4379 units. Fourth quarter's ending finished goods inventory is estimated at 4974 units. Project Settings Enter 10,080 DEC 32 8 MacBook AExcel File Edit View Insert F Format Tools Data Window Help AutoSave OFF Budget p EX Home Insert Draw Page Layout Formulas Data Review View Developer Calibri 11 2b Wrap Text v General Paste BI UV EE Merge & Center $ ~ % App Recover Unsaved Workbooks. We were able to save changes to one or more files. Do you want to recover them? A24 + X V fx B C D E F G 14 Budget Project for Manufacturing Company: Enter your company name here) 15 16 (Enter your company name here) 17 Sales Budget 18 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr YEAR 19 Sales (in Units) 12,510 13,140 13,400 14,070 53,120 20 price per unit $98.00 $98.00 $98.00 $98.00 $98.00 21 total gross sales $1,225,980.00 $1,287,720.00 $1,313,200.00 $1,378,860.00 $5,205,760.00 22 23 What type of company is this (meaning, what product are you making)? 24 25 26 27 Additional information you will need for Production Budget: Company policy is to maintain 35% of next quarter's sales units in finished goods inventory. First quarter's beginning finished goods inventory is estimated at 4379 units. Fourth quarter's ending finished goods inventory is estimated at 4974 units. 28 29 30 (Enter your company name here) 31 Production Budget 32 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr YEAR 33 Sales (in units) 12,510 13,140 13,400 14,070 34 Add: Desired Ending Finished Goods Inv. 4,599 4,690 4,925 4.974 35 show calculations here: 12510 x .35 13140 x 35 13400 X .35 14070 x .35 36 Total Needs 17,109 17,830 18,325 19,044 37 Less: Beg. Finished Goods Inv. 4,379 4,599 4,690 4,925 38 show calculations here: 17109 -4379 17830 -4599 18325 -4690 19044-4925 39 Units to be produced 12,730 13,231 13,635 14,120 40 41 Additional information you will need for Cost of Goods Sold Budget: For cost of goods sold, the production costs include direct materials ($22 per unit), direct labor ($22 per unit), and overhead (applied at 150% of direct 42 labor). 43 44 (Enter your company name here) Project Settings + Enter 10,080 DEC 32 2 467 8