Question

1 Approved Answer

Posted on Jul 14, 2024

create a master budget A HERA Manufacturing Company Y a Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan r

create a master budget image text in transcribed

A HERA Manufacturing Company Y a Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan r 1 Background: These are the sales data of Hera, a winter sportwear manufacturer which manufacturers skis. You are Hera sales manager. You have to prepare a 12-month forecast for Year 5 in units and euros. You have the monthly unit sales data for the last 4 years; your average unit price to distributors has been 105 last year and you do not think it is possible to increase it. Cash collection is as follows: 80% of sales of month M are collected in M+1 19% of sales of month M are collected in M+2 1% of sales is never collected Feb Y e Mar Apr May Jun a r 2 Sales 13.114 10.793 12.765 13.573 20.930 22.830 25.206 18.360 10.199 12.672 12.851 16.246 15.716 17.341 20.032 14.853 24.324 27.890 29.185 20494 15.011 17.786 12.4AS 19.547 17160 21.596 23.569 15.779 25.183 30.255 30.999 25.407 15.542 20.495 19.115 20.520 21.242 19.968 24.674 21.972 23.949 35.586 40.042 32.246 17.480 21.599 16.875 21.242 Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Y e r 3 Jan Y e a r Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 4 L M N P R . Hera Manufacturing Forecast in Units RS 09 DS 140 10.720 111 101 May 11 15.075 10 No ON ON Jan December 13 12 19 14 15 16012 1855T 10 LES 17270 24 17 18 19 20 1844 1840 1 18 NOY 22 22 24 25 20 29 101 2014 550 19311 19.200 DE STILTE 185 100 1978 ORT 200301 20.325 110 2044 2005 1821 My 30 30 33 20 2022 20 21331 140 120 2341 34 35 22 Feb 210 192340 246 21ST 2284510 1980 40 41 43 4 44 4 2:19 Nov FOR 24740 25 30 53 O 61 21 RNRAI D O Hera Manufacturing - Sales and Cash Collection Budget Year 5 Mer N Der ren Yeard Det 21242 105 2.230.34 Years Total Sales 3.000 O 25.122 05 30 ASP 105 10 105 104 105 105 105 100 1023 35070004 AIM 111 1102 5012 ST 43820 03 408 2014 Cashow Accounts receivable 1.200.170 Cash Collection Expectations Oftes of month Meeted in Met Mera Manufacturing - Selling & Administrative Expense Budget Ye Feb wer w Selling and Administrative Expenses tesben Wine rende Indice AM fedt onder Are wanden Eerdmangestures best CHOROGORO Monte Puede coment predding GRO MO, and rearyl RRRRRRERES Hera Manufacturing - Direct Material Budget Years Mar App un pairs oil .... Needed Begining inventory Quantity to be produced Direct Material Budget 19 Polymer Plastic hu ...... Accounts payable 990.000 910.000 Desired ending inventory -20% of following month units forecast to be sold Fiberglass per pair of skis Polymers pair of skis per pair of skis Plastik per pair Gue per pair of Cash disbursement Suppliers we paid the amount of material used in month in 1 Gosing inventory on Dec. 31 Year 4 was 5027 pairs of skis Accounts payable on Dec. 31. Year 4 were 3000K c D M N 1 Hera Manufacturing - Direct Labor Budget Years M Age Oct Das 5 pairs of & TAPE 7 Amour 9 10 Dewranding inventory 11 Need 12 Beginnerstory 13 Quantity to be produced 14 15 Direct 16 Directories & bene 17 Oorverton 18 Direct supervision 19 Supervision overtime 20 Engineers & benefit 20 To 2 23 Cachow F V Direct Labour Directories Abent Direct labor overtime cost Direct supervisions Supervision overtimeco Engineensies en 56 workers with a fixed salary & benefits of 25 yon trage. A worker produces on average 25 pairs of skis per bor day 1.5 per overtime hour 3 pair of skis can be produced per hour formen lead the member team. Each forem costs/y on average 21 per avere 2 engineers Seach. They maintain the production lines be the best process and the incoming spection Hera Manufacturing - Manufacturing Overhead Budget Years AR ent relage w maneno Hera Manufacturing Production Budget thep Director GOOO merry For Hera Manufacturing - Production Budget Years N Des D Hera Manufacturing - Cash Budget Years Ae N Years Jan Feb Mer Sep Oct ASP NI Amount Des 21.242 900 2.210.314 Cashout SA cash out Production hout vrent purchase Dividend 21 mchance 22 on ST son LT Papa Acome STB Minimum desing cash balance 10000K Opening woment purchase 4000 in March and in Detober April and 100000 in Ober per non paid teachquarter and starting in March E F B D Hera Manufacturing - Year 5 Pro Forma Income Statement OOOOO AWN Year 5 () Sales revenues COGS #DIV/0! S&A expenses 10 11 Operating profit 12 Interests Eamings before tax Taxes 13 14 15 16 17 18 19 20 21 22 Net Eamings Hera Manufacturing - Year 5 Pro Forma Balance Sheet Balance sheet as of Dec. 31 of Year 5 Assets Liabilities Accounts payable Cash Accounts receivable Inventory Current assets Current abates 22.000.000 Plant & machinery 22.000.000 Plant & machinery Depreciation Land 12 13 14 Long term debt Equity Neteaming Dividend 22.000.000 Total assets 22.000.000 22.000.000 Plant & machinery as of Dec. 31 of Year 4:22'000 OOOC Land value: 2000/000 Equity as of Dec. 31 of Year 4: 17'1430796 23 24 25 26 27 28 29 30 The IF function The basic form or syntax of the function is: =IF(logic test, value if true, value if false) - Selling and Administrative Expense Budget, Sales Commission: =IF (SUM (C7:E7)>7500000;0.1*SUM(C10:E10),0) - Direct Labor Budget, Direct Labor overtime costs: =IF(C13>29400, (C13-29400)*15/3,0) Supervision Overtime costs: =IF(C13>29400;(C13-29400) *21/3,0) - Manufacturing Overhead Budget, Utilities: =+IF(C13>29400;50000+10000,50000) A HERA Manufacturing Company Y a Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan r 1 Background: These are the sales data of Hera, a winter sportwear manufacturer which manufacturers skis. You are Hera sales manager. You have to prepare a 12-month forecast for Year 5 in units and euros. You have the monthly unit sales data for the last 4 years; your average unit price to distributors has been 105 last year and you do not think it is possible to increase it. Cash collection is as follows: 80% of sales of month M are collected in M+1 19% of sales of month M are collected in M+2 1% of sales is never collected Feb Y e Mar Apr May Jun a r 2 Sales 13.114 10.793 12.765 13.573 20.930 22.830 25.206 18.360 10.199 12.672 12.851 16.246 15.716 17.341 20.032 14.853 24.324 27.890 29.185 20494 15.011 17.786 12.4AS 19.547 17160 21.596 23.569 15.779 25.183 30.255 30.999 25.407 15.542 20.495 19.115 20.520 21.242 19.968 24.674 21.972 23.949 35.586 40.042 32.246 17.480 21.599 16.875 21.242 Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Y e r 3 Jan Y e a r Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 4 L M N P R . Hera Manufacturing Forecast in Units RS 09 DS 140 10.720 111 101 May 11 15.075 10 No ON ON Jan December 13 12 19 14 15 16012 1855T 10 LES 17270 24 17 18 19 20 1844 1840 1 18 NOY 22 22 24 25 20 29 101 2014 550 19311 19.200 DE STILTE 185 100 1978 ORT 200301 20.325 110 2044 2005 1821 My 30 30 33 20 2022 20 21331 140 120 2341 34 35 22 Feb 210 192340 246 21ST 2284510 1980 40 41 43 4 44 4 2:19 Nov FOR 24740 25 30 53 O 61 21 RNRAI D O Hera Manufacturing - Sales and Cash Collection Budget Year 5 Mer N Der ren Yeard Det 21242 105 2.230.34 Years Total Sales 3.000 O 25.122 05 30 ASP 105 10 105 104 105 105 105 100 1023 35070004 AIM 111 1102 5012 ST 43820 03 408 2014 Cashow Accounts receivable 1.200.170 Cash Collection Expectations Oftes of month Meeted in Met Mera Manufacturing - Selling & Administrative Expense Budget Ye Feb wer w Selling and Administrative Expenses tesben Wine rende Indice AM fedt onder Are wanden Eerdmangestures best CHOROGORO Monte Puede coment predding GRO MO, and rearyl RRRRRRERES Hera Manufacturing - Direct Material Budget Years Mar App un pairs oil .... Needed Begining inventory Quantity to be produced Direct Material Budget 19 Polymer Plastic hu ...... Accounts payable 990.000 910.000 Desired ending inventory -20% of following month units forecast to be sold Fiberglass per pair of skis Polymers pair of skis per pair of skis Plastik per pair Gue per pair of Cash disbursement Suppliers we paid the amount of material used in month in 1 Gosing inventory on Dec. 31 Year 4 was 5027 pairs of skis Accounts payable on Dec. 31. Year 4 were 3000K c D M N 1 Hera Manufacturing - Direct Labor Budget Years M Age Oct Das 5 pairs of & TAPE 7 Amour 9 10 Dewranding inventory 11 Need 12 Beginnerstory 13 Quantity to be produced 14 15 Direct 16 Directories & bene 17 Oorverton 18 Direct supervision 19 Supervision overtime 20 Engineers & benefit 20 To 2 23 Cachow F V Direct Labour Directories Abent Direct labor overtime cost Direct supervisions Supervision overtimeco Engineensies en 56 workers with a fixed salary & benefits of 25 yon trage. A worker produces on average 25 pairs of skis per bor day 1.5 per overtime hour 3 pair of skis can be produced per hour formen lead the member team. Each forem costs/y on average 21 per avere 2 engineers Seach. They maintain the production lines be the best process and the incoming spection Hera Manufacturing - Manufacturing Overhead Budget Years AR ent relage w maneno Hera Manufacturing Production Budget thep Director GOOO merry For Hera Manufacturing - Production Budget Years N Des D Hera Manufacturing - Cash Budget Years Ae N Years Jan Feb Mer Sep Oct ASP NI Amount Des 21.242 900 2.210.314 Cashout SA cash out Production hout vrent purchase Dividend 21 mchance 22 on ST son LT Papa Acome STB Minimum desing cash balance 10000K Opening woment purchase 4000 in March and in Detober April and 100000 in Ober per non paid teachquarter and starting in March E F B D Hera Manufacturing - Year 5 Pro Forma Income Statement OOOOO AWN Year 5 () Sales revenues COGS #DIV/0! S&A expenses 10 11 Operating profit 12 Interests Eamings before tax Taxes 13 14 15 16 17 18 19 20 21 22 Net Eamings Hera Manufacturing - Year 5 Pro Forma Balance Sheet Balance sheet as of Dec. 31 of Year 5 Assets Liabilities Accounts payable Cash Accounts receivable Inventory Current assets Current abates 22.000.000 Plant & machinery 22.000.000 Plant & machinery Depreciation Land 12 13 14 Long term debt Equity Neteaming Dividend 22.000.000 Total assets 22.000.000 22.000.000 Plant & machinery as of Dec. 31 of Year 4:22'000 OOOC Land value: 2000/000 Equity as of Dec. 31 of Year 4: 17'1430796 23 24 25 26 27 28 29 30 The IF function The basic form or syntax of the function is: =IF(logic test, value if true, value if false) - Selling and Administrative Expense Budget, Sales Commission: =IF (SUM (C7:E7)>7500000;0.1*SUM(C10:E10),0) - Direct Labor Budget, Direct Labor overtime costs: =IF(C13>29400, (C13-29400)*15/3,0) Supervision Overtime costs: =IF(C13>29400;(C13-29400) *21/3,0) - Manufacturing Overhead Budget, Utilities: =+IF(C13>29400;50000+10000,50000)