Excel 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 8Auto Save OFF Budget project Q v Search Sheet Home Insert Draw Page Layout Formulas Data Review View Developer Share % Conditional Formatting O Format as Table Paste Font Alignment Number Cells Cell Styles ~ Editing Ideas Ser Recover Unsaved Workbooks. We were able to save changes to one or more files. Do you want to recover them? F85 A C D E F 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 Direct materials $275,220 $289,080 $294,800 $309,540 $1,168,640 18 show calculations here: 12510 * 12 13140 *22 13400 * 22 14070 * 22 9 Direct Labor $275,220 $289,080 $294,800 $309,540 $1,168,640 50 show calculations here: 12510 * 22 13140 * 22 13400 * 22 14070 * 22 Overhead $412,830 $433,620 $442,200 $464,310 $1,752,960 52 show calculations here: 275220 * 150 289080 * 150 294800 * 150 $1,083,390 Total Cost of goods sold $963,270 $1,011,178 $1,031,800 $1,083,390 $4,090,240 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 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 62 Selling expenses: 63 Sales commissions $110,338 $115,895 $118,188 $124,097 $468,518 64 Sales salaries $27,920 $27,920 $27,920 $27,920 $111,680 65 Total selling expense $138,258 $143,815 $146,108 $151,017 $580,198 66 67 Administrative expenses: 68 Depreciation expense - office bidg $3,710 $3,710 $3,710 $3,710 $14,840 69 Office salaries $76,100 $76,100 $76,100 $76,100 $304,400 70 Total Administrative expenses $79,810 $79,810 $79,810 $79,810 $319,240 71 72 Total Selling and Admin Expenses $218,068 $223,625 $225,918 $231,827 $899,438 72 Locc. Mon rach Funanone C2 710 ca7in $2 710 Project Settings IT atv Xme Insert Draw Page Layout Formulas Data Review View Developer 1 Sh A % Conditional Formatting v ste Font Format as Table Alignment Number Cell Styles v Cells Editing Ideas Recover Unsaved Workbooks. We were able to save changes to one or more files. Do you want to recover them? B C D E F (Enter your company name here) Selling and Admin Expense Budget 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr YEAR elling expenses: ales commissions $110,338 $115,895 $118,188 $124,097 $468,518 ales salaries $27,920 $27,920 $27,920 $27,920 $111,680 Total selling expense $138,25/LL $143,815 $146,108 $151,017 $580,198 dministration expenses: epreciation expense - office bidg $3,710 $3,710 $3,710 $3,710 $14,840 Office salaries $76,100 $76,100 $76,100 $76,100 $304,400 Total Administrative expenses $79,810 $79,810 $79,810 $79,810 $319,240 otal Selling and Admin Expenses $218,068 $223,625 $225,918 $231,827 $899,438 Less: Non-cash Expenses $3,710 $3,710 $3,710 $3,710 $14,840 Total Cash Payments for Selling & Admin $214,358 $219,915 $222,208 $228,117 $884,598 (Enter your company name here) Budgeted Income Statement 1st Qtr 2nd Qtr Brd Qtr 4th Qtr YEAR Sales Revenue $1,225,980 $1,287,720 $1,313,200 $1,378,860 $5,205,760 Less: Cost of goods sold $963,270 $1,011,780 $1,031,800 $1,083,390 $4,090,240 Gross Profit (or Gross Margin) $262,710 $275,940 $281,400 $295,470 $1,115,520 Selling and Admin Expenses $218,068 $223,625 $225,918 $231,827 $899,438 Net Income $44,642 $52,315 $55,482 $63,643 $216,082 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 CALCULATION CLEARLY. Project Settings iJ ? or tvExcel File Edit View Insert Format Tools Data Window Help AutoSave OFF 76 5 6 Budget project Q v Search Sheet Home Insert Draw Page Layout Formulas Data Review View > > Share A % " Conditional Formatting v Format as Table Cells Editing Ideas Sensi Paste Font Alignment Number Cell Styles v Recover Unsaved Workbooks. We were able to save changes to one or more files. Do you want to recover them? A99 4 X V fx Total Cash Receipts from Customers A B D E F 89 90 Whitman Athletic's Inc. 91 Schedule of Cash Collections from Customers 92 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr YEAR 93 Sales $1,225,980 $1,287,720 $1,313,200 $1,378,860 $5,205,760 94 Collections: 95 75% same quarter $919,485 $965,790 $1,034,145 $1,034,145 $3,904,320 96 formula: (quarter x 75%) 97 25% next quarter $79,800 $306,495 $321,930 $328,300 $1,036,525 98 formula: (quarter x 25%) 99 Total Cash Receipts from Customers $999,285 $1,272,285 $1,356,075 $1,362,445 $4,940,845 100 101 Additional information you will need for Schedule of Cash Payments for Purchases: All direct material purchases are made on credit and budgeted purchases are provided below. Cash payments for purchases are made 70% in the same quarter as the purchase and 30% will be paid in the quarter after the purchase. Outstanding accounts payable as of Jan 1 is $23860 and will be paid in the 102 first quarter. SHOW ALL CALCULATIONS CLEARLY. 103 104 Whitman Athletic's Inc. 105 Schedule of Cash Payments for Purchases 106 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr YEAR 107 Direct material purchases $377,000 $471,300 $494,900 $504,800 $1,848,000 108 Cash Payments for direct material purchases: 109 110 70% same quarter $263,900 $329,910 $346,430 $353,360 $1,293,600 111 formula: (quarter x 70%) 112 30% next quarter $23,860 $113,100 $141,390 $148,470 $426,820 113 formula: (quarter x 30%) 114 Total Cash payments for direct materials $287,760 $443,010 $487,820 $501,830 $1,720,420 115 116 Additional information you will need for the Cash Budget: The beginning cash balance on January 1 is expected to be $40200. Remember to use the information calculated in the Selling and Admin Expense 117 budget, Schedule of Cash Receipts, and Schedule of Cash Payments for Purchases. 118 119 Whitman Athletic's Inc. 120 Cash Budget A Project Settings + 9,544 DEC 32 2 1 459 9Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF Budget project Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Share Com Conditional Formatting LO A - Format as Table v Paste Font Alignment Number Cells Editing Ideas Sensitivity Cell Styles v Recover Unsaved Workbooks. We were able to save changes to one or more files. Do you want to recover them? Yes A99 4 x fx Total Cash Receipts from Customers A C E F G 108 Cash Payments for direct material purchases: 109 110 70% same qu $263,900 $329,910 $346,430 $353,360 $1,293,600 111 formula: (quarter x 70%) 112 30% next quarter $23,860 $113,100 $141,390 $148,470 $426,820 113 formula: (quarter x 30%) 114 Total Cash payments for direct materials $287,760 $443,010 $487,820 $501,830 $1,720,420 115 116 Additional information you will need for the Cash Budget: The beginning cash balance on January 1 is expected to be $40200. Remember to use the information calculated in the Selling and Admin Expense 117 budget, Schedule of Cash Receipts, and Schedule of Cash Payments for Purchases. 118 119 Whitman Athletic's Inc. 120 Cash Budget 21 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr YEAR 22 Beginning Cash Balance 23 Add: Cash Collections from Customers 24 Total Cash Available 25 26 Less Cash Payments: 27 Cash payments for direct material purch 28 Cash payments for selling and admin. 29 Total Cash Payments 30 Ending Cash Balance $0 3 Problem 2: FLEXIBLE Budget Performance Report 4 This problem will build on the information from the previous problem. You will need the information from the following budgets: 5 1. Cost of Goods Sold Budget 6 2. Selling and Admin Expense Budget 7 3. Budgeted Income Statement a. Classify each of the following as VARIABLE or FIXED. For variable items, enter the variable amount per unit. For fixed items, enter the total amount