can you please do the forcast sheet (2nd picture) with the formulas. and asap please and thank you
Zoom Breakeven Analysis Add care Forecast MPS Production Financial Results by Product Financial Results From the data provided below, determine the following a) b) Number of units that must be sold for the company to break even The amount of sales dollars required to break even Based on your results, what is the total standard costs when a breakeven number of units are sold? Known variables: a) $59,750 $230 260 BEU Selling price per unit: Rent Administarion Salaries Manufacturing labour per unit Employee Benefits Insurance Direct Materials Equipment Rental Office Supplies Selling price per unit: Direct Materials Manufacturing labour per unit Total Variable costs Contribution SS Contribution $275 $20,000 $8,000 $26 $2,500 $950 $19 $24,500 $3,800 b) $275 $19 $26 $45 $230 84% $59.750 84% $71,131 BE Sales c) 260 x 45 $11,700 Fixed Costs Rent Administarion Salaries Employee Benefits Insurance Equipment Rental Office Supplies Total Fixed Costs $20,000 $8,000 $2.500 $950 $24,500 $3,800 $59,750 Breakeven Analsysis Forecast Production MPS Financial Results Financial Results by Product D G H M Forecast Planning Using the previous sales data balow, create forecast models and determine which model you feel could be considered a "best fit Explain the criteria you used to make your recommendation EMA 1 January $89.500 2 February 576,300 81,800 3 March $82.650 $78475 $82,150 4 April $87.900 $85 225 $61.617 83588 12 December S87,265 5 May 598,630 $91,265 $89,727 S85,870 6 June $115.450 $107 000 $100,660 596,158 7 July $120,874 5118,162 5111,651 $105,714 10 11 October November $21.456 598,456 8 August September $68.453 $59.82 $94.663 $101 592 $100,852 9 10 es 111 loving Average 12 loving Average 13 loving Averate 14 15 16 72 18 10 20 22 Breakeven Analysis Forecast Prodytion MPS Financial Results Financial Results by Product 318 Production Planning al based on your monthly sales forecast and the selling price per unit, determine the total number of unit sales for each month (production plan wit auto calculate) b) Calculate the utilization of production for each month calculate the number of units to be produced for each product line for each month Chint - the total number of units produced for each item should equal your total production plan for that month) February March April May June July Sales Forecast $89,500 $87.965 $87,428 $87,476 588,602 $91,314 Selling Price per Unit $275 No of units forecasted to sell 325 320 318 322 332 Production Plan 320 910 122 324 351 Inventory 165 160 150 155 159 161 180 Work Days per month 20 21 22 23 20 23 Capacity 615 630 625 630 625 610 Utilization 52% 49% 52% 51% 52% 56% Percentage of total sales Widget A 15% 48 48 49 53 Widget B 22 20 68 71 71 71 77 Widget 38% 122 118 123 122 123 133 Widget D 25% 80 72 81 81 81 88 Total 320 310 323 322 324 351 323 46 IN Forecast bakeven Analysis Production MPS Financial Results Financial Results by Product MPS Master Production Schedule a) Calculate the total number of daily units that are planned to be produced each month b) Calculate the total number of daily units by each product line that are to be produced (hint: the total od these should equal the amounts determined in step by June February $89,500 March $87,965 April $87428 May $87.476 July $91,314 588,602 $275 318 323 Sales Forecast Selling Price per Unit No of units forecasted to sell Production Plan Inventory Work Days per month Capacity Total Units produced daily 332 351 180 161 165 325 320 160 20 615 16 Per Day 320 310 150 21 630 15 Per Day 2 318 322 159 23 630 14 Per Day 2 3 5 4 14 155 22 625 15 Per Day 2 3 6 4 15 625 16 Per Day 630 15 Per Day 8_888 Percentage of total sales 15% 22X Widget A Widget Widget Widget SANSEN 3 4 6 4 16 25% 15 16 Total 15 Breakeven Analysis Forecast Production MPS Finolal Results Financial Results by Product Planned Finandal Results From the data you have compled from your planned forecast and production, prepare a planned financial Income statement Your plan is to be completed for each month April $87,428 318 $19 $26 February $89,500 325 $6,184 $8,462 $14.545 $74,855 March $87,965 320 $6,078 $8,317 $14,394 $73,571 $6,040 $8,266 $14,306 $73,122 May $87,476 318 56,044 $8,270 $14,314 $73,161 June $88,602 322 56,122 $8,377 $14,499 $74,104 July $91,314 332 $6,309 58,633 $14,942 $76,372 Sales Forecast Units Sold Direct Materials (per unit) Direct Labour (per unit) Total Standard Costs Contibution Margins Expenses Rent Administarion Salaries Employee Benefits Insurance Equipment Rental Office Supplies Total Expenses Net Income (EBITDA) $20,000 $8,000 $2,500 $950 $19,000 $2.500 $52,950 $21.905 $20,000 $8,000 $2,500 $950 $24,500 $3100 $59.750 $13.821 $20,000 $8,000 $2.500 $950 $21,500 $3,000 $55,950 $17,172 $20,000 $8,000 $2,500 $950 $24.500 $3,800 $59,750 $13,411 $20,000 $8,000 $2,500 $950 $21,500 $3,000 $55,950 $18,154 $20,000 $8.000 $2.500 $950 $24,500 $3.800 $59.750 $16.622 Table Chart Text Add Category 100% Zoom Breakeven Analsysis Insert MPS Shape Media Comment Financial Results by Product Financial Results Forecast Production Sales and Contribution Martin.by.Item From the data you have compled from your planned financial income statement, determine the sales, standard costs, and contribution margin for each item in production March $87,965 320 February $89.500 325 $6,184 $8.462 $1466 $74,855 Sale Forecast Units Sold Direct Materiais (per unit) Direct Labour per unit) Total Standard Couts Contribution Margins 9 $6.078 May 587,476 318 $6,004 SH270 $14, 314 $73,161 June $88,602 322 $6,122 SE377 $14 499 574,100 July $91,314 332 $6,309 $8,633 516,942 $76,372 April $87,428 32 $6,040 $8.266 $14,306 $73,122 $19 $26 $8,317 $14,34 $73.571 Percentage of total sales Widget A 15 Sales for WA Direct Materiais (per unit) Direct Labour ter und Total Standard Costs Wet A Widget A Contribution Margin 313425 5928 $1200 52,197 $11,220 $13,195 $912 $1.245 $2.199 $11,036 $1), 114 $906 $1,200 $2.146 $10,968 $12121 3907 $1,241 $2.147 $10,974 $13,290 3918 51,257 $2,123 $11, 116 $13.697 5440 51.295 $2.2017 $11.456 22 Widget Sales for Widget Direct Merias (per unit) Detabout (per unit Tote Standard Costs Widget Widget Comitution Mari $19.60 S1360 31. $32 316,468 $19.252 $117 $1,810 $19.214 $1.329 $1.019 S1.14 516087 $19,245 $1 130 $1,819 $3149 $16,096 519.492 $1,147 $1843 190 $16,300 370 089 $1,388 $1,899 58787 $16.802 $16,186 3x 514010 52.350 331241 $2,291 $1,140 Watc Sves for wat Direct Materials for un Direct Labourer) Total Ward Cous Widget WidgetContribution Margin $14.690 57.197 5321 su $2.126 $3,13 $5.509 $2,159 313,223 52.295 31,141 $52430 $27.286 52100 $1160 3470 $27,957 31215 3515 $78,445 $20,021 $27.01 25 5>> 375 Widget Sales for Wider Direct Materia per una Direct Labour (per Tots Sandard Costs WD WidgetContribution Margin $21.991 $1,519 $2.079 $1.590 $18.90 501,857 $1,510 $7.066 515765 516,210 $3,115 586614 $10214 $22,151 S1,530 52.094 31.643 $125 $21,669 $1.511 $2.00 5.578.6 51 $22,828 51.527 $2.156 $1756 919.099 $89.500 Total Produrt Sales Total Product Direct Material Total Product Direct Labo Yo Product Standard Costs Total Product Contribution Margin $87.65 56.00 $6317 3144 $71,571 $8.462 $87421 56,040 SAN 314, 306 573, 122 $87.476 56044 58. 270 514314 573,161 SBR 602 54132 58.377 $14.499 574 104 $91,310 36109 $1,633 $14,942 $76,172 51465 $74355 Complete the excel sheet that can be found below and also answer the following questions: 1. Once you've completed building your models on the spreadsheet below, which forecast model would you choose? 2. Why would you choose the model you've selected? 3. What type of business intelligence analysis is this? Assignment 3 can be found here Forecast Planning Using the previous sales data below, create forecast model and determine which model you feel could be considered hester Explain the criteria you used to make your recommendation EMA 10 January SM2.500 1 March $42.650 February $74.300 4 April $87.500 5 May 3 Son New 6 une $1157450 Muly $130.24 August September SI 38.53 SL Period Month Previous Sales 2 Month Moving Average Month Moving Average 4 Month Moving Average EMA Che planation Zoom Breakeven Analysis Add care Forecast MPS Production Financial Results by Product Financial Results From the data provided below, determine the following a) b) Number of units that must be sold for the company to break even The amount of sales dollars required to break even Based on your results, what is the total standard costs when a breakeven number of units are sold? Known variables: a) $59,750 $230 260 BEU Selling price per unit: Rent Administarion Salaries Manufacturing labour per unit Employee Benefits Insurance Direct Materials Equipment Rental Office Supplies Selling price per unit: Direct Materials Manufacturing labour per unit Total Variable costs Contribution SS Contribution $275 $20,000 $8,000 $26 $2,500 $950 $19 $24,500 $3,800 b) $275 $19 $26 $45 $230 84% $59.750 84% $71,131 BE Sales c) 260 x 45 $11,700 Fixed Costs Rent Administarion Salaries Employee Benefits Insurance Equipment Rental Office Supplies Total Fixed Costs $20,000 $8,000 $2.500 $950 $24,500 $3,800 $59,750 Breakeven Analsysis Forecast Production MPS Financial Results Financial Results by Product D G H M Forecast Planning Using the previous sales data balow, create forecast models and determine which model you feel could be considered a "best fit Explain the criteria you used to make your recommendation EMA 1 January $89.500 2 February 576,300 81,800 3 March $82.650 $78475 $82,150 4 April $87.900 $85 225 $61.617 83588 12 December S87,265 5 May 598,630 $91,265 $89,727 S85,870 6 June $115.450 $107 000 $100,660 596,158 7 July $120,874 5118,162 5111,651 $105,714 10 11 October November $21.456 598,456 8 August September $68.453 $59.82 $94.663 $101 592 $100,852 9 10 es 111 loving Average 12 loving Average 13 loving Averate 14 15 16 72 18 10 20 22 Breakeven Analysis Forecast Prodytion MPS Financial Results Financial Results by Product 318 Production Planning al based on your monthly sales forecast and the selling price per unit, determine the total number of unit sales for each month (production plan wit auto calculate) b) Calculate the utilization of production for each month calculate the number of units to be produced for each product line for each month Chint - the total number of units produced for each item should equal your total production plan for that month) February March April May June July Sales Forecast $89,500 $87.965 $87,428 $87,476 588,602 $91,314 Selling Price per Unit $275 No of units forecasted to sell 325 320 318 322 332 Production Plan 320 910 122 324 351 Inventory 165 160 150 155 159 161 180 Work Days per month 20 21 22 23 20 23 Capacity 615 630 625 630 625 610 Utilization 52% 49% 52% 51% 52% 56% Percentage of total sales Widget A 15% 48 48 49 53 Widget B 22 20 68 71 71 71 77 Widget 38% 122 118 123 122 123 133 Widget D 25% 80 72 81 81 81 88 Total 320 310 323 322 324 351 323 46 IN Forecast bakeven Analysis Production MPS Financial Results Financial Results by Product MPS Master Production Schedule a) Calculate the total number of daily units that are planned to be produced each month b) Calculate the total number of daily units by each product line that are to be produced (hint: the total od these should equal the amounts determined in step by June February $89,500 March $87,965 April $87428 May $87.476 July $91,314 588,602 $275 318 323 Sales Forecast Selling Price per Unit No of units forecasted to sell Production Plan Inventory Work Days per month Capacity Total Units produced daily 332 351 180 161 165 325 320 160 20 615 16 Per Day 320 310 150 21 630 15 Per Day 2 318 322 159 23 630 14 Per Day 2 3 5 4 14 155 22 625 15 Per Day 2 3 6 4 15 625 16 Per Day 630 15 Per Day 8_888 Percentage of total sales 15% 22X Widget A Widget Widget Widget SANSEN 3 4 6 4 16 25% 15 16 Total 15 Breakeven Analysis Forecast Production MPS Finolal Results Financial Results by Product Planned Finandal Results From the data you have compled from your planned forecast and production, prepare a planned financial Income statement Your plan is to be completed for each month April $87,428 318 $19 $26 February $89,500 325 $6,184 $8,462 $14.545 $74,855 March $87,965 320 $6,078 $8,317 $14,394 $73,571 $6,040 $8,266 $14,306 $73,122 May $87,476 318 56,044 $8,270 $14,314 $73,161 June $88,602 322 56,122 $8,377 $14,499 $74,104 July $91,314 332 $6,309 58,633 $14,942 $76,372 Sales Forecast Units Sold Direct Materials (per unit) Direct Labour (per unit) Total Standard Costs Contibution Margins Expenses Rent Administarion Salaries Employee Benefits Insurance Equipment Rental Office Supplies Total Expenses Net Income (EBITDA) $20,000 $8,000 $2,500 $950 $19,000 $2.500 $52,950 $21.905 $20,000 $8,000 $2,500 $950 $24,500 $3100 $59.750 $13.821 $20,000 $8,000 $2.500 $950 $21,500 $3,000 $55,950 $17,172 $20,000 $8,000 $2,500 $950 $24.500 $3,800 $59,750 $13,411 $20,000 $8,000 $2,500 $950 $21,500 $3,000 $55,950 $18,154 $20,000 $8.000 $2.500 $950 $24,500 $3.800 $59.750 $16.622 Table Chart Text Add Category 100% Zoom Breakeven Analsysis Insert MPS Shape Media Comment Financial Results by Product Financial Results Forecast Production Sales and Contribution Martin.by.Item From the data you have compled from your planned financial income statement, determine the sales, standard costs, and contribution margin for each item in production March $87,965 320 February $89.500 325 $6,184 $8.462 $1466 $74,855 Sale Forecast Units Sold Direct Materiais (per unit) Direct Labour per unit) Total Standard Couts Contribution Margins 9 $6.078 May 587,476 318 $6,004 SH270 $14, 314 $73,161 June $88,602 322 $6,122 SE377 $14 499 574,100 July $91,314 332 $6,309 $8,633 516,942 $76,372 April $87,428 32 $6,040 $8.266 $14,306 $73,122 $19 $26 $8,317 $14,34 $73.571 Percentage of total sales Widget A 15 Sales for WA Direct Materiais (per unit) Direct Labour ter und Total Standard Costs Wet A Widget A Contribution Margin 313425 5928 $1200 52,197 $11,220 $13,195 $912 $1.245 $2.199 $11,036 $1), 114 $906 $1,200 $2.146 $10,968 $12121 3907 $1,241 $2.147 $10,974 $13,290 3918 51,257 $2,123 $11, 116 $13.697 5440 51.295 $2.2017 $11.456 22 Widget Sales for Widget Direct Merias (per unit) Detabout (per unit Tote Standard Costs Widget Widget Comitution Mari $19.60 S1360 31. $32 316,468 $19.252 $117 $1,810 $19.214 $1.329 $1.019 S1.14 516087 $19,245 $1 130 $1,819 $3149 $16,096 519.492 $1,147 $1843 190 $16,300 370 089 $1,388 $1,899 58787 $16.802 $16,186 3x 514010 52.350 331241 $2,291 $1,140 Watc Sves for wat Direct Materials for un Direct Labourer) Total Ward Cous Widget WidgetContribution Margin $14.690 57.197 5321 su $2.126 $3,13 $5.509 $2,159 313,223 52.295 31,141 $52430 $27.286 52100 $1160 3470 $27,957 31215 3515 $78,445 $20,021 $27.01 25 5>> 375 Widget Sales for Wider Direct Materia per una Direct Labour (per Tots Sandard Costs WD WidgetContribution Margin $21.991 $1,519 $2.079 $1.590 $18.90 501,857 $1,510 $7.066 515765 516,210 $3,115 586614 $10214 $22,151 S1,530 52.094 31.643 $125 $21,669 $1.511 $2.00 5.578.6 51 $22,828 51.527 $2.156 $1756 919.099 $89.500 Total Produrt Sales Total Product Direct Material Total Product Direct Labo Yo Product Standard Costs Total Product Contribution Margin $87.65 56.00 $6317 3144 $71,571 $8.462 $87421 56,040 SAN 314, 306 573, 122 $87.476 56044 58. 270 514314 573,161 SBR 602 54132 58.377 $14.499 574 104 $91,310 36109 $1,633 $14,942 $76,172 51465 $74355 Complete the excel sheet that can be found below and also answer the following questions: 1. Once you've completed building your models on the spreadsheet below, which forecast model would you choose? 2. Why would you choose the model you've selected? 3. What type of business intelligence analysis is this? Assignment 3 can be found here Forecast Planning Using the previous sales data below, create forecast model and determine which model you feel could be considered hester Explain the criteria you used to make your recommendation EMA 10 January SM2.500 1 March $42.650 February $74.300 4 April $87.500 5 May 3 Son New 6 une $1157450 Muly $130.24 August September SI 38.53 SL Period Month Previous Sales 2 Month Moving Average Month Moving Average 4 Month Moving Average EMA Che planation
