Prepare only a cash budget in table form. You are working as a financial advisor for Mr.…
- User
Prepare only a cash budget in table form. You are working as a financial advisor for Mr. Buzzi, who is a well-known investor. Today Mr. Buzzi has explained to you a new business project that he has, and he wants you to prepare projected financial statements to have an idea of the profitability of the project. Following we have the data that Mr. Buzzi has delivered to you: Sales: The expected sales are 500000 units for the first year, and the sales growth is expected to be 100% for the year 2, 100% for the year 3, 120% for the year 4 and 130% for the year 5. The sales price per unit is 9€ per unit for the first year, and the estimation is that the price would be growing at a 7% every year. Variable production cost: The recipe of the product explains that the cost will be of 6€ per unit on the first and second year, and will be growing at a 2% year after year afterwards. Production labor: The first year the plant needs 6 technicians with a cost of 45000€ per person per year, 56 production workers with an annual cost of 30.000€ per employee per year, and 1 manager with a cost of 92.000€ per person per year. The next years the production labor will increase, due to the fact that the production of the plant will also be increasing. The year 2 the cost will be 50% higher than year 1, the year 3 the cost will be 50% higher than year 2, the year 4 the cost will be 55% higher than year 3 and in year 5 the cost will be 55% higher than year 4. Investment: the required investment is 13.500.000€, of which 10.000.000 will be depreciated over a period of 10 years and the rest will be depreciated over a period of 4 years. No more investments will be required during the years 2 until 5. Utilities expense: We expect a cost of 0.20€ per unit on the first year, the expectation for the following years is to have a cost increase of 4% year after year. Maintenance: the maintenance will be outsourced, and we expect a cost of 0.3 per unit produced year after year.
General and administration costs are expected to be 200.000€ for the first year, 210000 for the second year, and for the following years the cost should increase in line with the sales volume, having a cost increase of 0.5% of sales of the additional sales.
The days to collect accounts receivable are 90, the days to pay suppliers are 30. The company will need an inventory of 45 days. Mr. Buzzi is ready to invest 8.000.000€. The investments need to be paid upfront and the rest of money needed will come from a bank credit line, you should make your own estimation of the amount needed. The tax rate is 25% and the interest rate is 5%. Mr. Buzzi is requesting from you a proposal of Profit & loss account for the next 5 years and a balance sheet for the same period. A few weeks later, Mr Buzzi asks you to prepare a cash planning for the first year based on the following assumptions. Consider that the raw materials will be purchased on the previous month (starting in December) The raw materials inventory should be based on 45 days of the next month sales. Labor cost (also G&A) is paid divided between 12 months, always at the end of the month. Utilities and maintenance are paid every month depending on the consumption. Income tax is paid on the month of march. Interest expense is paid on the following month.
- Assistant
Cash Budget for Year 1:
January February March April May June July August September October November December Sales 337500 375000 412500 450000 675000 900000 1200000 1500000 Cost of Sales 202500 225000 247500 270000 405000 540000 720000 900000 Gross Profit 135000 150000 165000 180000 270000 360000 480000 600000 Variable Production Costs 300000 Production Labor 399000 399000 399000 399000 399000 399000 399000 399000 598500 798000 1064000 1330000 Utilities Expense 100000 100000 100000 100000 150000 150000 150000 150000 225000 300000 400000 500000 Maintenance 150000 150000 150000 150000 200000 200000 200000 200000 300000 400000 550000 700000 G&A Costs 200000 210000 210000 210000 213750 217500 221250 225000 228750 232500 236250 240000 Total Costs 1249000 859000 859000 859000 EBITDA (1249000) (859000) (859000) (859000) 135000 150000 165000 180000 270000 360000 480000 600000 Depreciation (338890) (338890) (338890) (338890) (325000) (350000) (375000) (400000) (525000) (650000) (825000) (975000) EBIT (1587890) (1196890) (1196890) (1196890) (190000) (200000) (210000) (220000) (255000) (290000) (345000) (375000) Interest Expense 50000 49737 47816 45893 43974 42061 61693 81224 102198 122173 Tax Expense 130278 134248 166217 184023 201628 219063 328592 438121 584161 730200 Net Income (1587890) (1196890) (1445168) (1496475) (411033) (429916) (448746) (467510) (692296) (917082) (1151209) (1356373) Cash Position (8000000) (6929000) (8570180) (8873993) (9281576) (9773937) (10262394) (10729805) (11522101) (12314497) (12942698) (13563730) Note: There is a typo in the Sales column of the table. January and February should be blank and the sales should start from March.