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
The IF function
The basic form orsyntaxof 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)
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 You have to prepare a 12-month forecast for Year 5 in units Sep 10.199 and euros. Oct 12.672 Nov 12.851 You have the monthly unit sales data for the last 4 years; your Dec 16.246 average unit price to distributors has been 1056 last year and Jan 15.716 you do not think it is possible to increase it. Feb 17.341 Mar 20.032 Cash collection is as follows: Apr 14.853 May 24.324 80% of sales of month M are collected in M+1 Jun 27.890 19% of sales of month M are collected in M+2 Jul 29.185 1% of sales is never collected Aug 20.494 Sep 15.011 Oct 17.786 Nov 12.485 De 19.547 Jan 17.160 Feb 21.596 Ma 23.569 Apr 15.779 May 25.183 Jun 30.255 Jul 30.999 Aug 25.407 Sep 15.542 Oct 20.495 Nov 19.115 Dec 20.520 Jan 21.242 Feb 9.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.242C D E F G P Q R Hera Manufacturing Forecast in Units Month Sales MA-12 RSt DSt Jan 13.114 Year 1 Year 2 Year 3 Feb 10.793 Year 4 Average 14.057 January 0,98 Mar 0,87 12.765 0,95 10.724 February 1,08 0,93 1,05 Apr 13.573 0,89 1,01 11.175 March 1,17 1,16 May 20.930 April 1,14 15.716 0,86 0,77 Jun 22.830 0,96 0,86 17.136 May 1,39 Jul 1,23 1,05 25.206 1,22 15.075 June 1,55 1.46 1,53 1,51 5.924 8.360 July 1,59 1.49 1.67 Sep 1,58 10.199 5.928 10 August 1,11 1,20 Oct 12.672 13.372 0.79 Nov September 0,73 12.851 October Dec 16.246 15.795 1,03 0,92 0,76 13.522 0,95 0.94 16.973 13 Jan 15.716 16.012 0,98 November 0,65 0,87 0,76 16.492 Feb 17.341 16.557 1,05 December 1,03 1,00 0,93 0,99 16.846 Mar 20.032 17.163 1,17 12,86 17 230 16 Apr 14.853 17 270 0,86 17.537 18 24.324 17.552 1,39 17.198 Jun 19.915 19 Jul 27 .890 17.974 1,55 19.185 18.306 1,59 18.416 20 Aug 20.494 21 18.484 1,11 18.438 Sep 15.011 18.885 0,79 17.778 22 Oct 17.786 19.311 0,92 19.682 23 12.485 19.280 0,65 18.979 24 Dec 19.547 19.555 1,00 16.490 25 Jan 17.160 19.676 0,87 19.844 26 Feb 21.596 20.030 1,08 18.394 27 Mar 28 3.569 20.325 1,16 21.457 Apr 15.779 20.402 0,77 20.634 29 May 30 25.183 20.474 123 18.270 Jun 80 255 20.671 146 20.618 31 Jul 19.978 32 30.999 20.822 149 Aug 33 Sep 25.407 21231 1,20 19.584 15.542 21.276 0,73 22.041 34 Oct 20.495 21.501 0,95 20.378 35 21.870 36 19.115 22.054 0,87 Dec 20.520 22.135 0,93 25.246 Jan 21 242 22.475 0,95 20.831 Feb 22.769 39 9.968 22.340 0,89 24.674 22.432 1,10 19.840 BORED Apr 21.972 22.948 0,96 21.601 May 25.440 42 23.949 22.845 1,05 Jun 35.586 23.289 1,53 19.609 Jul 40.042 24.043 1,67 23.498 45 Aug 2 246 24.613 1,31 25.298 Sep 27.974 46 17 480 24.774 0,71 Oct 22.918 47 21.599 24.866 0,87 Nov 16.875 24.679 0,68 23.048 Dec 21.242 24.740 0,86 22.28 21.564 Slope = 228 Intercept = 13.564 Year 5 forecast in units 48 It Ft Jan 24.753 Feb 49 23.093 50 Mar 24.981 25.142 51 25.209 28.796 Apr May 52 25.438 21.969 53 Jun 25.666 54 31.348 Jul 25.894 39.216 55 26.123 41.349 Aug 56 Sep 26.351 57 30.376 26.580 Oct 20.272 58 Nov 26.808 25.122 59 27.036 20471 Dec 60 27 265 26.857 Jan 61 27 493 25.649Hera Manufacturing - Sales and Cash Collection Budget Year 6 Year 4 Dec Aug Sep Units [pairs of skis] 21.242 30. 376 20.272 ASP [E] 105 105 105 105 105 105 105 105 105 105 105 105 105 105 Amount [E] 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.126.570 2.637.602 2.149.430 2.819.976 2.693.185 80% of sales 2.111.924 2 418 829 1.845.422 2.633.221 3.294.111 3473.298 2 551 549 1.702.856 2.110.241 1.719.544 2.255.982 19% of sales 460.705 574.472 438.288 625.390 782. 351 605. 993 404.428 501.182 408. 392 Cull Inow 2.572.620 2.410.894 3.071.500 3.919.501 4.255.650 2.308.049 2.514.670 2.220.726 2.664.374 Accounts receivable 4.021.285 3.200.110 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 collected Year 5 Total Sales 334.009 35.070984 B C D E F G H K L M IN O P Q R Hera Manufacturing - Selling & Administrative Expense Budget Year 5 Jan Feb Mar Apr May Jun Ju Aug Sep Oct Nov Dec Total 5 Units [pairs of skis] ASP Amount Selling and Admin Expenses Total Sales salaries &benefits 11 Sales commissions 12 Selling expenses (TML) 13 Advertising expenses 14 Promotions 15 Not collected sales 16 Administrative salaries 17 General Management 18 Shipping expenses 19 Office rent 20 Office utilities (elect, water) 21 Telecom expenses SV 22 T expenses 23 nsurance 24 Total 25 6 Cash outflow (1) Selling and Administrative Expenses Sales salaries & benefits: 20 sales persons at average 100'000E/yr . Sales commissions: 10% of salary & benefits paid quarterly if quarter 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) 38 39 Shipping expenses: 150 per 100 pairs of skis 40 Office rent: 240KE/yr Office utilities: Historical data indicate average monthly expenses of 1KE Telecom expenses: Fixed monthly expenses: 1KE; Variable expenses : 0.003E /Sales amount IT expenses: 65E per person employed (exluding CEO; CMO; CFO and secretary) Insurance: 80KE paid quarterly at the begining of quarterA B C D E F G H J K L M N O Hera Manufacturing - Direct Material Budget Year 5 Year 6 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan 5 6 Units [pairs of skis] ASP [E] Amount 10 Desired ending inventory Needed quan 12 Begining Inventory 13 Quantity to be produced .. 14 15 Direct Material Budget 16 Fiber glass 30E/ski pair 17 Polymers 5E/ski pair 18 Steel 7E/ski pair 19 Plastic 16/ski pair 20 Glue 16/ski pair Total 23 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 30 per pair of skis Polymers 56 per pair of skis Stee 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 Min M+1 Closing inventory on Dec. 31, Year 4 was 5'027 pairs of skis Accounts payable on Dec. 31, Year 4 were 910'000EA B C D E F G H J K M N O 10 Desired ending inventory 11 Needed quantity 12 Begining Inventory 13 Quantity to be produced 14 15 Direct Labour expenses 6 Direct salaries & benefits F 17 Direct labor overtime cost 18 Direct supervision s&b Supervision overtime costs: 20 Engineers' salaries & benefits 1
