create master budget

HERA Manufacturing Company Y e Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct a 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 Nov Y e 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 20.494 15.011 17.786 12.485 19.547 17.160 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 Y Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec a r 3 Y e r 4 B D E F G H 1 Hera Manufacturing - Sales and Cash 2 3 Year 4 Year 4 Dec Jan Feb Mar Apr May Jun 5 Units (pairs of skis) 21.242 23.093 25.142 28.796 21.969 31.348 39.216 6 ASP () 105 105 105 105 105 105 105 7 Amount () 2.230.374 2.424.761 2.639.906 3.023.537 2.306.778 3.291.526 4.117.639 8 9 10 80% of sales 2.111.924 2.418.829 1.845.422 2.633.221 11 19% of sales 460.705 501.582 574.472 438.288 12 Cash inflow 2.572.629 2.920.411 2.419.894 3.071.509 13 14 Accounts receivable 4.021.285 15 16 17 18 Cash Collection Expectations 19 20 80% of sales of month M are collected in M+1 21 19% of sales of month M are collected in M+2 22 1% of sales is never collected 23 24 25 26 HERA Manufacturing Company Y e Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct a 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 Nov Y e 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 20.494 15.011 17.786 12.485 19.547 17.160 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 Y Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec a r 3 Y e r 4 B D E F G H 1 Hera Manufacturing - Sales and Cash 2 3 Year 4 Year 4 Dec Jan Feb Mar Apr May Jun 5 Units (pairs of skis) 21.242 23.093 25.142 28.796 21.969 31.348 39.216 6 ASP () 105 105 105 105 105 105 105 7 Amount () 2.230.374 2.424.761 2.639.906 3.023.537 2.306.778 3.291.526 4.117.639 8 9 10 80% of sales 2.111.924 2.418.829 1.845.422 2.633.221 11 19% of sales 460.705 501.582 574.472 438.288 12 Cash inflow 2.572.629 2.920.411 2.419.894 3.071.509 13 14 Accounts receivable 4.021.285 15 16 17 18 Cash Collection Expectations 19 20 80% of sales of month M are collected in M+1 21 19% of sales of month M are collected in M+2 22 1% of sales is never collected 23 24 25 26