Week 5 Homework Assignment Excel FLE HOM PAGE LAVOUT INSERT FORMULAS DATA REVIEW VEW -A A Calibr Wap Tet Aut General Paster &-A.E 3 Mege& Center - Cenditional Fermat a Cel Table Styles S % . inset Delete Foman Formatting Cipbeard Font Agnment Number Ce K11 C H 1 Comprehensive Budgeting Problem Silverman Manufacturing is preparing its master budget for the fist quarter of the upcoming year. The following data pertain to Sverman Manufacturing's 2 loperations Cument Assets as of December 31 (prior year 0 polnts - Cash 4,500 Accounts receivable, net Inventory 49,000 15,320 Property, plant and equipment, met Accounts payable Capital stock 10 Retained earnings 121.500 42400 125.000 22,920 Ha) Actual sales in December were $0,000 Selling price per unit is projected to remain stable at St0 per unit throughout the budget period Sales for the first 11 tve months of the upcoming year are budgeted to be as follows: 12 anuary 13 February 14 March 80,0009 92000 99,000 15 Apnl 16 May 97,000 85,000 17 Mb) Sales are 30% cash and 0% credit, All redit sales are collected in the month following the sale o silverman Manufacturing has a policy that states that each month's ending inventory of finished goods should be 25% of the following month's sales in 18 units). dof each month's direct material purchases, a% are paid in the month of parchase, while the remiander is paid for in the month following puchase. Two 19 pounds of direct material is needed per unit at S2 per pound. Ending inventory of direct materials should be 10% of neat month's prodution needs. tel Monthly manudacturing convenion costs are $5,000 for factory rent, $1000 for other fied manudactaring espenses, and S120 per unit fo wariable 20 manufacturing overhead. No depreciation is included in these figares. All expemes are paid in the month in which they are incureed Assgt 1 Assgme 2 Select deinaten and press ENTER erhe Paster PLL 1911M Conditional Format as Cell Formatting Table Styles Styles Insert Delete Format $% ,. Merge & Center BIU - A Pate Cells Number Alignment Font Cpbeard K26 H E (d)Of each month's direct material purchases, 20% are paid in the month of purchase, while the remiander is pald for in the month following purchase. Two 19 pounds of direct material is needed per unit at $2 per pound. Ending inventory of direct materials should be 10 % of next month's production needs (e) Monthly manufacturing conversion costs are $5,000 for factory rent, $,000 for other fixed manufacturing expenses, and $1.20 per unit for variable 20 manulacturing overhead. No depreciation is included in these figures. All expenses are paid in the month in which they are incurred. n Computer equipment for the administrative offices will be purchased in the upcoming quarter. In January, Silverman Manufacturing will purchase 21 equipment for $5,000 (cash) while February's cash expenditure will be $12,000 and March's cash expenditure will be $16,000. ()Operating expenses are budgeted to be $1 per unit sold plus fixed operating expenses of $1,000 per month. All operating expenses are paid in the month in 22 which they ae incurred Depreciation on the building and equipment for the general and administrative offices is budgeted to be $4,800 for the entire quarter, which includes 23 depreciation on new acquisitions. )seeman Manufacturing has a policy that the ending cash balance in each month must be at least $4,000. t has a line of credit with a local bank. The company can bomow in increments of $1,000 at the beginning of each month, up to a total outstandingg loan balance of S100,000. 1he interest rate on these loas is 1% per month simple interest (not compounded). Silverman Manufacturing would pay down on the line of credit ballance it it has eacess funds at the .. 24 end of the quarter, The company would alse pay the accumulated interest at the end of the quarter on the funds bomowed duringg the quarter The company's income tax rate is projected to be % of operating income less interest expense. 25 estimated taxes 26 Rqairements The company pays S10,000cah at the end of February ins 27 (N)Prepare a chedule of canh collections for lanary, Febeuary and Marh, and for the qater in total. Use the following format Cash Collections Budget lanare February Quarter 30 Cash Sales 3 Cedit Sales March Assget 1 Assgmt 2 NIER on xR PLL1911M $ Paste Forma B IU 6-0 Cell St Clipboard Font Alignment Number L13 fr A C E 34 (2) Prepare a production budget, using the following format: 35 Production Budget 36 January February March Quarter 37 Unit sales 38 Plus: Desired ending inventory 39 Total needed 40 Less: Beginning inventory 41 Units to produce 42 "Hint: Units sales Sales in dollars/Selling price per unit 43 44 (3) Prepare a direct materials budget, using the following format: 45 Direct Materials Budget February 46 January March Quarter 47 Units to be produced 48 X Pounds of DM needed per unit 49 Quantity (pounds) needed for production 50 Plus: Desired ending inventory of DM 51 Total quantity (pounds) needed 52 Less: Beginning inventory of DM 53 Quantity (pounds) to purchase Assgmt 1 Assgmt 2 Type here to search E2S IS EConditional For Calibri General A A 1: $ % Format as Table A B I Cell Styles- Cipboard Font Alignment Number Styles L13 A C D 43 44 (3) Prepare a direct materials budget, using the following format: 45 Direct Materials Budget 46 January February March Quarter 47 Units to be produced 48 X Pounds of DM needed per unit 49 Quantity (pounds) needed for production 50 Plus: Desired ending inventory of DM 51 Total quantity (pounds) needed 52 Less: Beginning inventory of DM 53 Quantity (pounds) to purchase $4 X Cost per pound SS Total cost of DM purchases 56 57 (4) Prepare a cash payments budget for the direct material purchases from Requirement 3, using the following format: 58 Cash Payments for Direct Material Purchases Budget January 59 February March Quarter December purchases (from Accounts 60 Payable) 61 January purchases Assgmt 1 Assgmt 2 Type here to search 95 AutoSave ( o Week 5 Homework Assignment Excel File Home O Search Insert Draw Page Layout Help Formulas Data Review View X Conditional Form General Calibri A A 11 % Format as Table" Paste B I U A- Cell Styles Clipboard Font Alignment Number Styles fr L13 55 Total cost of DM purchases 56 57 (4) Prepare a cash payments budget for the direct material purchases from Requirement 3, using the following format: 58 Cash Payments for Direct Material Purchases Budget 59 January February March Quarter December purchases (from Accounts 60 Payable) 61 January purchases 62 February purchases 63 March purchases Total cash payments for direct material 64 purchases 65 66 (5) Prepare a cash payments budget for conversion costs, using the following format: Cash Payments for Conversion Costs Budget February 67 68 January March Quarter 69 Variable conversion costs 70 Rent (fixed) 71 Other fixed MOH 72 Total payments for conversion costs Assgmt 1 Assgmt 2 Type here to search 959 Search AutoSave O Help View Review Data Formulas Page Layout Draw Conditiona Insert Home File General Format as A A % Calbei Cell Styles A- Paster I Stye Number Alignment Foet Cigbod L13 A Total cash payments for direct material 64 purchases 65 66 (5) Prepare a cash payments budget for conversion costs, using the following format Cash Payments for Conversilon Costs Budget February 67 Quarter March January 68 69 Variable conversion costs 70 Rent (fixed) 71 Other fixed MOH 72 Total payments for conversion costs 73 74 (6) Prepare a cash payments budget for operating expenses, using the following format: Cash Payments for Operating Expenses Budget February 75 March Quarter January 76 77 Variable operating expenses 78 Fixed operating expenses 79 Total payments for operating expenses 80 81 (7) Prepare a combined cash budget, using the following format: Combined Cash Budget 82 Assgmt 2 Assgmt 1 Type here to search Excel Week S Homework Assignment AutoSave oe P Search Help View Review Formulas Data Page Layout Insert Draw Home File Conditional Forma General A A 11 SE Fomat as Teble $ % Calbei 5Cell Styles A - te IU- styles Number Alignment Foem Clpbond L13 C A 73 74 (6) Prepare a cash payments budget for operating expenses, using the following format 75 Cash Payments for Operating Expenses Budget lanuary February March Quarter 76 7 Variable operating expenses 78 Fixed operating expenses 79 Total payments for operating expenses 80 81 (7) Prepare a combined cash budget, using the following format 82 Combined Cash Budget 83 84 Cash balance, beginning 85 Add cash collections January E February March Quarter 86 Total cash available 87 Less cash payments Direct material purchases Conversion costs Operating expenses Equipment purchases Tax payment Assgmt 1 Assgnt 2 91 92 O Type here to search L of RM Condition General 11A A Calibri EFormat as $ % Peter A- Cell Styles U Sty Number Alignment Font Oigboard (8) Calculate the budgeted manufacturing cost per unit, using the following format (assume th A100 E C B A 100 (8) Calculate the budgeted manufacturing cost per unit, using the following format (assume that fixed manufacturing overhead is be 101 102 Direct Materials cost per unit 103 Conversion cost per unit 104 Fixed manufacturing overhead per unit 105 Budgeted cost of manufacturing each unit 106 Budgeted Manufacturing Cost per Unit 107 (9) Prepare a budgeted income statement for the quarter ending March 31, using the following format: 108 Budgeted Income Statement 109 For the Quarter Ending March 31 110 Sales 111 Cost of goods sold 112 Gross profit 113 Operating expenses 114 Depreciation 115 Operating income 116 Less interest expense 117 Less provision for income taxes Assgmt 1 Assgmt 2 O Type here to search M Calibri 11 AC A General Conditional Paste EFormat as I U v A Cell Styles- Clipboard Font Alignment Number Style A100 (8) Calculate the budgeted manufacturing cost per unit, using the following format (assume tha A C 106 E 107 (9) Prepare a budgeted income statement for the quarter ending March 31, using the following format: 108 109 Budgeted Income Statement For the Quarter Ending March 31 110 Sales 111 Cost of goods sold 112 Gross profit 113 Operating expenses 114 Depreciation 115 Operating income 116 Less interest expense 117 Less provision for income taxes 118 Net income 119 Cost of goods sold Budgeted cost of manufacturing each unit x Number of units sold 120 121 122 123 124 125 Assgmt 1 Assgmt 2 Iil Week 5 Homework Assignment Excel FLE HOM PAGE LAVOUT INSERT FORMULAS DATA REVIEW VEW -A A Calibr Wap Tet Aut General Paster &-A.E 3 Mege& Center - Cenditional Fermat a Cel Table Styles S % . inset Delete Foman Formatting Cipbeard Font Agnment Number Ce K11 C H 1 Comprehensive Budgeting Problem Silverman Manufacturing is preparing its master budget for the fist quarter of the upcoming year. The following data pertain to Sverman Manufacturing's 2 loperations Cument Assets as of December 31 (prior year 0 polnts - Cash 4,500 Accounts receivable, net Inventory 49,000 15,320 Property, plant and equipment, met Accounts payable Capital stock 10 Retained earnings 121.500 42400 125.000 22,920 Ha) Actual sales in December were $0,000 Selling price per unit is projected to remain stable at St0 per unit throughout the budget period Sales for the first 11 tve months of the upcoming year are budgeted to be as follows: 12 anuary 13 February 14 March 80,0009 92000 99,000 15 Apnl 16 May 97,000 85,000 17 Mb) Sales are 30% cash and 0% credit, All redit sales are collected in the month following the sale o silverman Manufacturing has a policy that states that each month's ending inventory of finished goods should be 25% of the following month's sales in 18 units). dof each month's direct material purchases, a% are paid in the month of parchase, while the remiander is paid for in the month following puchase. Two 19 pounds of direct material is needed per unit at S2 per pound. Ending inventory of direct materials should be 10% of neat month's prodution needs. tel Monthly manudacturing convenion costs are $5,000 for factory rent, $1000 for other fied manudactaring espenses, and S120 per unit fo wariable 20 manufacturing overhead. No depreciation is included in these figares. All expemes are paid in the month in which they are incureed Assgt 1 Assgme 2 Select deinaten and press ENTER erhe Paster PLL 1911M Conditional Format as Cell Formatting Table Styles Styles Insert Delete Format $% ,. Merge & Center BIU - A Pate Cells Number Alignment Font Cpbeard K26 H E (d)Of each month's direct material purchases, 20% are paid in the month of purchase, while the remiander is pald for in the month following purchase. Two 19 pounds of direct material is needed per unit at $2 per pound. Ending inventory of direct materials should be 10 % of next month's production needs (e) Monthly manufacturing conversion costs are $5,000 for factory rent, $,000 for other fixed manufacturing expenses, and $1.20 per unit for variable 20 manulacturing overhead. No depreciation is included in these figures. All expenses are paid in the month in which they are incurred. n Computer equipment for the administrative offices will be purchased in the upcoming quarter. In January, Silverman Manufacturing will purchase 21 equipment for $5,000 (cash) while February's cash expenditure will be $12,000 and March's cash expenditure will be $16,000. ()Operating expenses are budgeted to be $1 per unit sold plus fixed operating expenses of $1,000 per month. All operating expenses are paid in the month in 22 which they ae incurred Depreciation on the building and equipment for the general and administrative offices is budgeted to be $4,800 for the entire quarter, which includes 23 depreciation on new acquisitions. )seeman Manufacturing has a policy that the ending cash balance in each month must be at least $4,000. t has a line of credit with a local bank. The company can bomow in increments of $1,000 at the beginning of each month, up to a total outstandingg loan balance of S100,000. 1he interest rate on these loas is 1% per month simple interest (not compounded). Silverman Manufacturing would pay down on the line of credit ballance it it has eacess funds at the .. 24 end of the quarter, The company would alse pay the accumulated interest at the end of the quarter on the funds bomowed duringg the quarter The company's income tax rate is projected to be % of operating income less interest expense. 25 estimated taxes 26 Rqairements The company pays S10,000cah at the end of February ins 27 (N)Prepare a chedule of canh collections for lanary, Febeuary and Marh, and for the qater in total. Use the following format Cash Collections Budget lanare February Quarter 30 Cash Sales 3 Cedit Sales March Assget 1 Assgmt 2 NIER on xR PLL1911M $ Paste Forma B IU 6-0 Cell St Clipboard Font Alignment Number L13 fr A C E 34 (2) Prepare a production budget, using the following format: 35 Production Budget 36 January February March Quarter 37 Unit sales 38 Plus: Desired ending inventory 39 Total needed 40 Less: Beginning inventory 41 Units to produce 42 "Hint: Units sales Sales in dollars/Selling price per unit 43 44 (3) Prepare a direct materials budget, using the following format: 45 Direct Materials Budget February 46 January March Quarter 47 Units to be produced 48 X Pounds of DM needed per unit 49 Quantity (pounds) needed for production 50 Plus: Desired ending inventory of DM 51 Total quantity (pounds) needed 52 Less: Beginning inventory of DM 53 Quantity (pounds) to purchase Assgmt 1 Assgmt 2 Type here to search E2S IS EConditional For Calibri General A A 1: $ % Format as Table A B I Cell Styles- Cipboard Font Alignment Number Styles L13 A C D 43 44 (3) Prepare a direct materials budget, using the following format: 45 Direct Materials Budget 46 January February March Quarter 47 Units to be produced 48 X Pounds of DM needed per unit 49 Quantity (pounds) needed for production 50 Plus: Desired ending inventory of DM 51 Total quantity (pounds) needed 52 Less: Beginning inventory of DM 53 Quantity (pounds) to purchase $4 X Cost per pound SS Total cost of DM purchases 56 57 (4) Prepare a cash payments budget for the direct material purchases from Requirement 3, using the following format: 58 Cash Payments for Direct Material Purchases Budget January 59 February March Quarter December purchases (from Accounts 60 Payable) 61 January purchases Assgmt 1 Assgmt 2 Type here to search 95 AutoSave ( o Week 5 Homework Assignment Excel File Home O Search Insert Draw Page Layout Help Formulas Data Review View X Conditional Form General Calibri A A 11 % Format as Table" Paste B I U A- Cell Styles Clipboard Font Alignment Number Styles fr L13 55 Total cost of DM purchases 56 57 (4) Prepare a cash payments budget for the direct material purchases from Requirement 3, using the following format: 58 Cash Payments for Direct Material Purchases Budget 59 January February March Quarter December purchases (from Accounts 60 Payable) 61 January purchases 62 February purchases 63 March purchases Total cash payments for direct material 64 purchases 65 66 (5) Prepare a cash payments budget for conversion costs, using the following format: Cash Payments for Conversion Costs Budget February 67 68 January March Quarter 69 Variable conversion costs 70 Rent (fixed) 71 Other fixed MOH 72 Total payments for conversion costs Assgmt 1 Assgmt 2 Type here to search 959 Search AutoSave O Help View Review Data Formulas Page Layout Draw Conditiona Insert Home File General Format as A A % Calbei Cell Styles A- Paster I Stye Number Alignment Foet Cigbod L13 A Total cash payments for direct material 64 purchases 65 66 (5) Prepare a cash payments budget for conversion costs, using the following format Cash Payments for Conversilon Costs Budget February 67 Quarter March January 68 69 Variable conversion costs 70 Rent (fixed) 71 Other fixed MOH 72 Total payments for conversion costs 73 74 (6) Prepare a cash payments budget for operating expenses, using the following format: Cash Payments for Operating Expenses Budget February 75 March Quarter January 76 77 Variable operating expenses 78 Fixed operating expenses 79 Total payments for operating expenses 80 81 (7) Prepare a combined cash budget, using the following format: Combined Cash Budget 82 Assgmt 2 Assgmt 1 Type here to search Excel Week S Homework Assignment AutoSave oe P Search Help View Review Formulas Data Page Layout Insert Draw Home File Conditional Forma General A A 11 SE Fomat as Teble $ % Calbei 5Cell Styles A - te IU- styles Number Alignment Foem Clpbond L13 C A 73 74 (6) Prepare a cash payments budget for operating expenses, using the following format 75 Cash Payments for Operating Expenses Budget lanuary February March Quarter 76 7 Variable operating expenses 78 Fixed operating expenses 79 Total payments for operating expenses 80 81 (7) Prepare a combined cash budget, using the following format 82 Combined Cash Budget 83 84 Cash balance, beginning 85 Add cash collections January E February March Quarter 86 Total cash available 87 Less cash payments Direct material purchases Conversion costs Operating expenses Equipment purchases Tax payment Assgmt 1 Assgnt 2 91 92 O Type here to search L of RM Condition General 11A A Calibri EFormat as $ % Peter A- Cell Styles U Sty Number Alignment Font Oigboard (8) Calculate the budgeted manufacturing cost per unit, using the following format (assume th A100 E C B A 100 (8) Calculate the budgeted manufacturing cost per unit, using the following format (assume that fixed manufacturing overhead is be 101 102 Direct Materials cost per unit 103 Conversion cost per unit 104 Fixed manufacturing overhead per unit 105 Budgeted cost of manufacturing each unit 106 Budgeted Manufacturing Cost per Unit 107 (9) Prepare a budgeted income statement for the quarter ending March 31, using the following format: 108 Budgeted Income Statement 109 For the Quarter Ending March 31 110 Sales 111 Cost of goods sold 112 Gross profit 113 Operating expenses 114 Depreciation 115 Operating income 116 Less interest expense 117 Less provision for income taxes Assgmt 1 Assgmt 2 O Type here to search M Calibri 11 AC A General Conditional Paste EFormat as I U v A Cell Styles- Clipboard Font Alignment Number Style A100 (8) Calculate the budgeted manufacturing cost per unit, using the following format (assume tha A C 106 E 107 (9) Prepare a budgeted income statement for the quarter ending March 31, using the following format: 108 109 Budgeted Income Statement For the Quarter Ending March 31 110 Sales 111 Cost of goods sold 112 Gross profit 113 Operating expenses 114 Depreciation 115 Operating income 116 Less interest expense 117 Less provision for income taxes 118 Net income 119 Cost of goods sold Budgeted cost of manufacturing each unit x Number of units sold 120 121 122 123 124 125 Assgmt 1 Assgmt 2 Iil