LinkBack URL
About LinkBacks
If anyone can help with this i would be most grateful
Joe Soap the plumber is starting a business and wants to model its financial performance for the first 12 months. He predicts income of £1000 in the first month and this to grow at 6% per month for the 12 months. The cost of materials will be 25% of income. He has to pay 500 a month lease on the company van and hire of a shed to store materials, and 400 a month wages and National Insurance to the apprentice who works for him.
If he has an overdraft facility with his bank costing 2.8% per month interest on the previous month’s outstanding balance, which he uses to fund the business, how much will he be in credit/owe the bank at the end of each month?
Create a worksheet to model Joe Soap’s finances for the first 12 months where he starts with a bank account with no money in it and uses the overdraft facility. Assume all income and all payments happen at the end of each month (i.e. everything happens at the same time, once per month). Use built-in functions ABS and IF to calculate how much Joe needs to pay for the overdraft facility each month.
One difficult part of this problem is how to express the balance on the account at the end of each month. A simple way to think about this is that the money he has in the bank at the end of each month (and, therefore at the start of the next month) is what he had in the bank at the beginning of the month with the income for the month added and the expenditure for the month taken away. The expenditure may include the overdraft interest, which is calculated on the amount he had in the bank at the start of the month, if this was negative. Thus the balance at the end of the month will be an expression involving the balance at the start of the month (the balance at the end of the previous month except the first month where it is 0) and the income and expenditure for that month.
Important: The layout of the spreadsheet should have the constant or 'static' data outside of the main part of the spreadsheet so that these values can be easily changed. If a change is made to one of these values e.g. increase in the percentage growth in income, then it need only be done once and not throughout the spreadsheet. The rest of the spreadsheet will mainly contain cell references to these values.
Register To Reply
Bookmarks