Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

Prepare only a cash budget in table form. You are working as a financial advisor for Mr.…

  1. 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.

  2. Assistant

    Cash Budget for Year 1:

    JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
    Sales33750037500041250045000067500090000012000001500000
    Cost of Sales202500225000247500270000405000540000720000900000
    Gross Profit135000150000165000180000270000360000480000600000
    Variable Production Costs300000
    Production Labor39900039900039900039900039900039900039900039900059850079800010640001330000
    Utilities Expense100000100000100000100000150000150000150000150000225000300000400000500000
    Maintenance150000150000150000150000200000200000200000200000300000400000550000700000
    G&A Costs200000210000210000210000213750217500221250225000228750232500236250240000
    Total Costs1249000859000859000859000
    EBITDA(1249000)(859000)(859000)(859000)135000150000165000180000270000360000480000600000
    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 Expense5000049737478164589343974420616169381224102198122173
    Tax Expense130278134248166217184023201628219063328592438121584161730200
    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.