| | | | Susie's Beauty Products cash flow budget (2021) |
| Cleansers | 6000 | | | April | May | June | July | August | September |
| Toners | 3000 | | Production |
| Moisturisers | 9000 | | Cleanser | 6000 | 6240 | 6490 | 6749 | 7019 | 7300 |
| | | | Toner | 3000 | 3030 | 3060 | 3091 | 3122 | 3153 |
| Cleanser % change in production | 4% | | Moisturiser | 9000 | 9180 | 9364 | 9551 | 9742 | 9937 |
| Toner % change in production | 1% |
| Moisturiser % change in production | 2% | | Income |
| | | | Cleanser | £72,000 | £74,880 | £77,875 | £80,990 | £84,230 | £87,599 |
| Cleanser selling price per unit | £ 12.00 | | Toner | £24,000 | £24,240 | £24,482 | £24,727 | £24,974 | £25,224 |
| Toner selling price per unit | £ 8.00 | | Moisturiser | £171,000 | £174,420 | £177,908 | £181,467 | £185,096 | £188,798 |
| Moisturiser selling price per unit | £ 19.00 | | Total income | £267,000 | £273,540 | £280,266 | £287,184 | £294,300 | £301,621 |
| Cleanser cost of materials | £ 7.00 | | Expenses |
| Toner cost of materials | £ 4.00 | | Cleanser cost of materials | £ 42,000.00 | £ 43,680.00 | £ 45,427.20 | £ 47,244.29 | £ 49,134.06 | £ 51,099.42 |
| Moisturiser cost of materials | £ 15.00 | | Toner cost of materials | £ 12,000.00 | £ 12,120.00 | £ 12,241.20 | £ 12,363.61 | £ 12,487.25 | £ 12,612.12 |
| | | | Moisturiser cost of materials | £ 135,000.00 | £ 137,700.00 | £ 140,454.00 | £ 143,263.08 | £ 146,128.34 | £ 149,050.91 |
| Labour cost per unit | £0.40 | | Labour cost | £7,200.00 | £7,380.00 | £7,565.40 | £7,756.38 | £7,953.14 | £8,155.87 |
| | | | Rent | £1,333.33 | £1,333.33 | £1,333.33 | £1,333.33 | £1,333.33 | £1,333.33 |
| Premises rent per year | £ 16,000.00 | | Electricity per quarter | £660.00 | | | £660.00 |
| Premises rent per month | £ 1,333.33 | | Manager salary | £2,200.00 | £2,200.00 | £2,200.00 | £2,200.00 | £2,200.00 | £2,200.00 |
| | | | Manager bonus | £0.00 | £0.00 | £2,802.66 | £2,871.84 | £2,943.00 | £3,016.21 |
| Electricity per month | £220.00 | | Total expenses | £ 200,393.33 | £ 204,413.33 | £ 212,023.79 | £ 217,692.54 | £ 222,179.13 | £ 227,467.86 |
| Electricity per quarter | £660.00 |
| | | | Net income | £66,606.67 | £69,126.67 | £68,242.21 | £69,491.46 | £72,121.09 | £74,153.20 |
| Manager salary | £2,200.00 | | Balance brought forward | £30,500.00 | £97,106.67 | £166,233.33 | £234,475.54 | £303,967.00 | £376,088.09 |
| Manager bonus | 1% | | Balance carried forward | £97,106.67 | £166,233.33 | £234,475.54 | £303,967.00 | £376,088.09 | £450,241.29 |
| Manager bonus threhold | £280,000.00 |
| Balance brought forward | £30,500.00 |
Michelle_Michy
10_Seminar_ExcelExercise6-FinancialForecasting_SusiesBeautyProducts_Answers.xlsx
