Master Budget for Hera Manufacturing Company.

Using HERA MANUFACTURING SALES excel sheet and the FORECAST excel sheet complete the excel pages called Sales Budget, S&A Exp. Budget, Direct Mat budget, Direct Labor Budget, Mfg OVH Budget, Production Budget, Cash Budget, Proforma Income Statement and the Proforma Balance Sheet.

In each page (except for Production Budget and the Proforma Income Statement page) to be completed there is a text box with information at bottom, so it is needed to zoom in and out the excel pages to see all so any information is not missed.

A B C D E F G H HERA Manufacturing Company Month Sales Jan 13.114 Feb 10.793 Background: Mar 12.765 Apr 13.573 These are the sales data of Hera, a winter sportwear May 20.930 manufacturer which manufacturers skis. You are Hera sales Jun 22.830 manager. Jul 25.206 Aug 18.360 Sep 10.199 You have to prepare a 12-month forecast for Year 5 in units Oct 12.672 and euros. Nov 12.851 Dec 16.246 You have the monthly unit sales data for the last 4 years; your Jan 15.716 average unit price to distributors has been 1056 last year and Feb 17.341 you do not think it is possible to increase it. Mar 20.032 Apr 14.853 Cash collection is as follows: May 24.324 Jun 27.890 80% of sales of month M are collected in M+1 Jul 29.185 19% of sales of month M are collected in M+2 Aug 20.494 1% of sales is never collected Sep 15.011 Oct 17.786 Nov 12.485 Dec 19.547 Jan 17.160 Feb 21.596 Mar 23.569 Apr 15.779 May 25.183 Jun 30.255 Jul 30.999 Aug 25.407 Se 15.542 Oct 20.495 Nov 19.115 Dec 20.520 Jan 21.242 Feb 19.968 Mar 24.674 Apr 21.972 May 23.949 Jun 35.586 Jul 40.042 Aug 32.246 Sep 17.480 Oct 21.599 Nov 16.875 Dec 21.242Hera Manufacturing - Year 5 Pro Forma Income Statement Year 5 Sales revenues COGS #DIV/O! S&A expenses Operating profit Interests Earnings before tax Taxes Net EaringsHera Manufacturing - Year 5 Pro Forma Balance Sheet Balance sheet as of Dec. 31 of Year 5 Assets Liabilities E E Cash Accounts payable Accounts receivable Inventory Current assets Current liabilities Plant & machinery 22.000.000 Long term debt Plant & machinery = 22.000.000 Depreciation Equity 22.000.000 Land Net earnings Dividend Total assets 22.000.000 22.000.000 Plant & machinery as of Dec. 31 of Year 4: 22'000'0006 Land value: 2'000'000 E Equity as of Dec. 31 of Year 4: 17'143'0796Hera Manufacturing Forecast in Units Month Sales MA-12 RSt DSt Jar 13.114 Year 1 | Year 2 Year 3 AON Year 4 Average 14.057 Feb 0.793 January 0,98 D,87 0,95 0,9 10.724 Mar 12.765 February 1,05 1,08 1,01 11.175 Ap 13.573 March 1,17 1,16 1,10 1,14 15.716 May 20.930 April 0,BE 0,77 0,96 0,86 17.136 Jun 22.830 May 1,35 1,23 1,05 1,22 15.075 Jul 25.206 June 1,55 1,46 1,53 1,51 15.924 Aug 18.360 July 1,59 1,49 1,67 1,58 15.928 Sep 10.199 Augus 1,1 1,20 1,15 13.372 10 Oct 12.672 September 0,7 0,73 0,76 13.522 11 Nov 12.851 October 1,92 0,95 0,94 16.973 12 Dec 6.246 15.795 1,03 November 0,65 D,87 0,76 16.492 13 Jar 5.716 16.012 0,98 December 1,03 1,00 0,93 0,99 16.846 14 Feb 17.341 16.557 1,05 12.86 17.230 15 Ma 20.032 17.163 1,17 17.537 16 Ap 14.853 17.270 0,86 17.198 17 May 24.324 17.552 1,39 19.915 18 Jun 27.890 17.974 1,55 18.416 19 Jul 29.185 18.306 1,59 18.438 20 Aug 20.494 18.484 1,11 17.778 21 Sep 15.011 18.885 0,79 19.682 22 Oct 17.786 19.311 0,92 18.979 23 Nov 12.485 19.280 0,65 16.490 24 Dec 19.547 19.555 1,00 19.844 25 Jan 17.160 19.676 0,87 18.394 26 Feb 21.596 20.030 1,08 21.457 27 Ma 23.569 20.325 1,16 20.634 28 Ap 15.779 20.402 0,77 18.270 29 May 25.183 20.474 1,23 20.618 30 Jun 30.255 20.671 1,46 19.978 31 Jul 30.999 20.822 1,49 19.584 32 Aug 25.407 21.231 1,20 22.041 33 Sep 15.542 21.276 0,73 20.378 34 Oct 20.495 21.501 0,95 21.870 35 Nov 19.115 22.054 0,87 25.246 36 Dec 20.520 22.135 0,93 20.831 37 Jan 21.242 22.475 0,95 22.769 38 Feb 19.968 22.340 0,89 19.840 39 Ma 24.674 22.432 1,10 21.601 40 Ap 21.972 22.948 0,96 25.440 41 May 23.949 22.845 1,05 19.609 42 Jun 35.586 23.289 1,53 23.498 43 Jul 40.042 24.043 1,67 25.298 44 Aug 32.246 24.613 1,31 27.974 45 Sep 17.480 24.774 0,71 22.918 46 Oct 21.599 24.866 0,87 23.048 47 Nov 16.875 24.679 0,68 22.288 Dec 21.242 24.740 0,86 21.564 Slope = 228 Intercept = 13.564Hera Manufacturing - Sales and Cash Collection Budget Year 4 Year 5 Year 6 Year 5 Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Total Sales Units [pairs of skis] 21.242 23.093 25.142 28.796 21.969 31.348 39.216 41.349 30.376 20.272 25.122 20.471 26.857 25.649 334.009 ASP [(] 105 105 105 105 105 105 105 105 105 105 105 105 105 105 Amount 2.230.374 2.424.761 2.639.906 3.023.537 2.306.778 3.291.526 4.117.639 4.341.623 3.189.436 2.128.570 2.637.802 2.149.430 2.819.978 2.693.185 35.070.984 80% of sales 2.111.924 2.418.829 1.845.422 2.633.221 3.294.111 3.473.298 2.551.549 1.702.856 2.110.241 1.719.544 2.255.982 19% of sales 160.705 501.582 574.472 138.288 625.390 782.351 324.908 605.993 404.428 501.182 408.392 Cash inflow 2.572.629 2.920.411 2.419.894 3.071.509 3.919.501 4.255.650 3.376.457 2.308.849 2.514.670 2.220.726 2.664.374 Accounts receivable 4.021.285 3.200.170 Cash Collection Expectations 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 collectedHera Manufacturing - Selling & Administrative Expense Budget Year 5 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Units [pairs of skis] ASP [3 Amount [(] Selling and Admin Expenses [(] Total Sales salaries &benefits Sales commissions Selling expenses (TML) Advertising expenses Promotions Not collected sales 7.5ME (Starting in March.) Selling expenses: Travel, meals and lodging of the 20 sales persons (Historical data indicate about 1KE/month.sales person) Advertising expenses: None in May to Aug. 100KE in Jan, Mar, Apr & Sept; 300KE in Feb and Oct; 500KE in Nov and Dec Promotions: Sell-in promotions of 50KE in Jun and Jul, of 60KE in Aug and Sell-out promotions of 500KE in Mar and Dec. Administrative salaries: 15 persons with average salary and benefits of 60KE/yr General management: Salaries &n benefits of the CEO (200KE), CMO (180KE), CFO (180KE) and a secretary (80KE) Shipping expenses: 1506 per 100 pairs of skis Office rent: 240KE/yr Office utilities: Historical data indicate average monthly expenses of 1KE Telecom expenses: Fixed monthly expenses: 1KE; Variable expenses : 0.0036 /Sales amount IT expenses: 656 per person employed (exluding CEO; CMO; CFO and secretary) Insurance: 8OKE paid quarterly at the begining of quarterHera Manufacturing - Direct Material Budget Year 5 Year 6 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Units [pairs of skis] ASP [(] Amount [(] Desired ending inventory Needed quantity Begining Inventory Quantity to be produced Direct Material Budget [(] Fiber glass 30(/ski pair Polymers 56/ski pair Steel 76/ski pair Plastic 1 6/ski pair Glue 1 6/ski pair Total Accounts payable 910.000 Cash outflow (2) 910.000 Inventory Desired ending inventory = 20% of following month units forecast to be sold Direct Material Budget: Fiber glass 306 per pair of skis Polymers SE per pair of skis Steel 76 per pair of skis Plastic 16 per pair of skis Glue 16 per pair of skis Cash disbursement: Suppliers are paid the amount of material used in month M in M+1 Closing inventory on Dec. 31, Year 4 was 5'027 pairs of skis Accounts payable on Dec. 31, Year 4 were 910'0006Desired ending inventory Needed quantity Begining Inventory Quantity to be produced Direct Labour expenses Direct salaries & benefits Direct labor overtime cost Direct supervision s&b Supervision overtime costs: Engineers' salaries & benefits Total Cash outflow (3) Direct Labour Expenses: Direct salaries & benefits: 56 workers with a fixed salary & benefits of 25KE/yr on average. A worker produces on average 25 pairs of skis per labor day. Direct labor overtime costs: 156 per overtime hour. 3 pairs of skis can be produced per hour. Direct supervision s&b: 8 foremen lead the 8 7-member teams. Each foreman costs 35KE/yr on average. Supervision overtime costs: 216 per overtime hour. Engineers' salaries & benefits: 2 engineers at 50KE/yr each. They maintain the 8 production lines, supervise the ski test process and ensure the material incoming inspectionHera Manufacturing - Manufacturing Overhead Budget Year 5 Year 6 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Units [pairs of skis] ASP [( Amount [(] - Desired ending inventory Needed quantity Begining Inventory Quantity to be produced Manufacturing Overhead Expenses [(] Depreciation of equipment General Production Management s&b Insurance Plant Utilities (electricity, energy, water) SV External services: Maintenance expenses: T expenses Total Cash outflow (4): Manufacturing Overhead Expenses: Depreciation of plant & equipment: 10% per year on plant and machinery worth 22ME (not part of the cash outflow) General Production Management s&b: Salaries & benefits of COO (170KE), manufacturing accountant (90KE), purchaser (90KE), secretary (60KE) Insurances: 200KE/yr paid in advance quarterly, starting in January Plant Utilities: Historical data indicate a monthly average of 50KE for electricity, energy (fuel, gas) and water plus 10KE when production runs over 29'400 pairs/month External services: Miscellaneous services from plumbing to recruiting agency. Historical data put these services at SKE/month Maintenance expenses: Maintenance contracts are worth 250KE/yr plus extras for special maintenance and repairs for 110KE/yr (historical data) IT expenses: Updates and upgrades of software and hardware of IT equipment. Contracts and historical data indicate a monthly average of 10KE.Hera Manufacturing - Production Budget Year 5 Year 6 Jan Feb Mar Ap May Jun Ju Aug Sep Oct Nov De Jan Units [pairs of skis] ASP [(] Amount [(] Desired ending inventory Needed quantity Begining Inventory Quantity to be produced Direct Material Budget [(] Direct Labor Budget [(] Manufacturing Overhead Budget Unit cost #DIVIO! Total COGS = #DIV/01 Inventory #DIV/O! Cash outflow (2) Cash outflow (3) Cash outflow (4) Cash outflow (5)Hera Manufacturing - Cash Budget Year 4 Year 5 Year 5 Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Units [pairs of skis] 21.242 ASP [( 105 Amount [(] 2.230.374 Opening cash balance Cash collection Cash inflow (1) Cash available S&A cash outflow Cash outflow (1) Production cash outflow Cash outflow (5) Equipment purchase Taxes Dividend Excess (deficiency) Minimum cash balance Interests on ST debt Interests on LT debt Borrowing Repayment Accumulated ST Borrowing Closing cash balance Cash budget elements: Minimum closing cash balance: 100'0006 Opening cash balance: 889'1136 Equipment purchases: 1750'0006 in March and 1'900'0006 in October Taxes: 800'0006 in April and 1'200'0006 in October Dividend: 50OKE at each quarter starting in February Interests on ST debt: 12% per annum payable monthly Interests in LT debt: 6% per annum on 8ME paid at each quarter end, starting in MarchThe 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) Please be aware that some computers prefer, instead of . and reverse. Or ; instead of ,and reverse.. Complete the Master Budget on Excel for Hera Manufacturing . Use the IF Functions Provide a brief analysis of the financial forecasted situation of the company