T
theukego
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.
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.