BUDGETING & CONTROL

Complete the excel file attached for Hera Manufacturing Company

It has to be done with IF Functions

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; Dec 16.246 your average unit price to distributors has been 1036 last Jan 15.716 year and 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 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 Sep 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.242\fUnits [pairs of skis] ASP [] Amount [] 80% of sales 19% of sales Cash Inow Accounts receivable Year4 Dec 21.242 103 2.161.691 | 3.979.221 Hera Manufacturing - Sales and Cash Collection Budget 41.349 30.376 26.857 Year 6 Jan 25.649 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 23.093 25.142 28.796 21.969 31.346 39.216 . 103 103 103 103 103 103 103 103 103 103 103 103 103 2.376.515 2.569.622 2.965.945 | 2.262.639 3.226.631 4.039.206 4.256.926 | 3.126.665 2.066.026 2.561.556 | 2.106.466 2.166.264 2.641.661 2.372 756 1.610.271 2.563.065 3.231.366 3.407.140 2.502.946 1.670.421 1.666.791 2.213.011 56 530 42 939 61 476 767 49 609 96 49 636 400.613 - - 2.523.621 2.373.601 3.013.004 3.644.644 4.114.590 3.312.144 2.264.611 2.176.426 2.613.624 3.139.214 Year 5 Total Sales 334.009 34.402366 Hera 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 [E] Amount Selling and Admin Expenses Total Sales salaries &benefits Sales commissions Selling expenses (TML) Advertising expenses Promotions Not collected sales Administrative salaries General Management Shipping expenses Office rent Office utilities (elect, water) n n 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: 150 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.003E /Sales amount IT expenses: 65E per person employed (exluding CEO; CMO; CFO and secretary) Insurance: 80KE paid quarterly at the begining of quarterHera Manufacturing - Direct Material Budget Year 5 Year 6 Jan Feb Mar Apr May Jun Jul Sep Oct NON Jan Units [pairs ofskls] ASP [g] Amount [g] Desired ending inventory Needed quantity Begining Inventory Quantity to be produced Dlrect Material Budget [g] Fiber 9 lass 30lski pair Polymers 5E/ski pair Steel 7/ski pair Plastic 1/ski pair Glue 1/ski pair Total Accounts payable 910.000 Cash outflow (2) 910.000 Inventory Fiber glass Polymers Steel Plastic Glue Dlrect Material Budget: 3062 per pair of skis 5 per pair of skis 7 per pair of skis 1 per pair of skis 1 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'000C Desired ending inventory = 20% of following month units forecast to be sold Hera Manufacturing - Direct Labor Budget Year 5 Year 6 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Units [pairs of skis] ASP [E] Amount [E] Desired 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: