ESSEX LTD
ESSEX LTD This activity gives you more chance to practice the techniques you have been learning. Please submit your completed spreadsheet to the drop box prior to next week. Essex Ltd's accountant has been preparing the cash budget for the company for the six months ending 31 st December. She has already collected a lot of the information required and has prepared a data sheet and cash budget worksheet, with a lot of data and formulae having already been inputted. This is available here as a spreadsheet called 'Essex Ltd - Cash Budget'. Essex Ltd - Cash Budget.xlsx Save this to an appropriate place e.g. on OneDrive. The accountant has now been called away urgently and you have been asked to complete the cash budget process for the period ending 31st December, taking into account the following information and instructions. - Make sure that you examine both the Essex Ltd Data Sheet and the Essex Ltd Cash Budget. - Note that you will need to add data to the data sheet and insert rows to the cash budget as appropriate. Additional information - A dividend payment to shareholders of 10,000 is to be made in November. - You notice that information on a bank loan is missing. The loan of 39,600 is to be received on 1 st July, with 46,800 being paid back in equal payments over 36 months, starting in August. Requirements a. Update the data sheet with this information on the loan and use this to deal with all of the receipts and payments that are predicted to take place in the cash budget making sure that you link the data sheet to the cash budget. b. Complete all the calculations and processing needed to complete the cash budget (credit receipts, total receipts, total payments, cash flow for each month, opening and closing balances, total column). Think about the opening balance in the total column! What should this be? Make sure that the closing balance in December reconciles with the closing balance in the total column of the cash budget. c. Calculate the overdraft interest charge per month. As is standard practice, interest is calculated if the balance at the end of a month is in overdraft. It is paid in the following month. For this task you will need to use an IF formula and a ROUND formula. The overdraft interest rate is recorded in the data sheet. d. Complete the following presentation tasks on the final version of your cash budget: Appropriately title the cash budget Remove the gridlines Format cells so that negative numbers are presented in brackets Put appropriate borders/shading onto the cash budget as necessary e. Use the IF function (text based IFs) and conditional formatting to alert management to any issues c. Calculate the overdraft interest charge per month. As is standard practice, interest is calculated if the balance at the end of a month is in overdraft. It is paid in the following month. For this task you will need to use an IF formula and a ROUND formula. The overdraft interest rate is recorded in the data sheet. d. Complete the following presentation tasks on the final version of your cash budget: Appropriately title the cash budget Remove the gridlines Format cells so that negative numbers are presented in brackets Put appropriate borders/shading onto the cash budget as necessary e. Use the IF function (text based IFs) and conditional formatting to alert management to any issues. f. Protect your model. g. Record the answers to these what if? questions in the data sheet. Remember to reset your model with the original values after each question i. What would the total overdraft interest be if the \% overdraft rate falls from 10% to 7% per annum? ii. What is the value for total sales receipts if collection from receivables moves from 70% to 60% in 1 month and 30% to 40% in 2 months? iii. What would be the effect on the 31 st December bank balance if the prediction for annual wages was 180,000 ? iv. What would be the effect on total payments for August if the predicted expenditure on vehicles turned out to be 44,000 ? h. Produce a bar chart of the closing balances for each of the six months July - December. i. Comment on Essex's cash flow position. ESSEX LTD CASH BUDGET FOR SIX MONTHS ENDING 31st DECEMBER 2019 Receipts Payments \begin{tabular}{|l|c|c|c|c|c|c|c|} \hline Purchases & 63000 & 75807 & 86648 & 89020 & 83599 & 86818 & \\ \hline Wages & & & & & & & \\ \hline General Expenses & 7500 & 7500 & 7500 & 7500 & 7500 & 7500 & \\ \hline Rent & 5000 & & & 5000 & & & \\ \hline Motor Expeneses & 750 & 750 & 750 & 750 & 750 & 750 & \\ \hline Dividends Paid & & & & & 0 & & \\ \hline Vehicles & & & & & & & \\ \hline Fixtures \& Fittings & & & & & & & \\ \hline Loan Repayments & & & & & & & \\ \hline Loan Interest & & & & & & & \\ \hline TOTAL & & & & & & & \\ \hline Cash Flow For Month & & & & & & & \\ \hline Opening Balance & & & & & & \\ \hline Closing Balance \end{tabular} Receivables Schedule Sales \begin{tabular}{|l|l|l|l|l|} \hline 98450 & 112530 & 115610 & 108570 & 112750 \\ \hline \end{tabular} Receipts From Sales Receivables- 1 months Receivables- 2 months Total Receipts From Credit Sales \begin{tabular}{|l|l|l|l|l|l|l|} \hline 63000 & & & & & & \\ \hline 24300 & 27000 & & & & & \\ \hline & & & & & & \\ \hline \end{tabular} DATA SHEET ESSEX LTD ACTIVITY CASH BUDGET ESSEX LTD ACTIVITY Ready ESSEX LTD This activity gives you more chance to practice the techniques you have been learning. Please submit your completed spreadsheet to the drop box prior to next week. Essex Ltd's accountant has been preparing the cash budget for the company for the six months ending 31 st December. She has already collected a lot of the information required and has prepared a data sheet and cash budget worksheet, with a lot of data and formulae having already been inputted. This is available here as a spreadsheet called 'Essex Ltd - Cash Budget'. Essex Ltd - Cash Budget.xlsx Save this to an appropriate place e.g. on OneDrive. The accountant has now been called away urgently and you have been asked to complete the cash budget process for the period ending 31st December, taking into account the following information and instructions. - Make sure that you examine both the Essex Ltd Data Sheet and the Essex Ltd Cash Budget. - Note that you will need to add data to the data sheet and insert rows to the cash budget as appropriate. Additional information - A dividend payment to shareholders of 10,000 is to be made in November. - You notice that information on a bank loan is missing. The loan of 39,600 is to be received on 1 st July, with 46,800 being paid back in equal payments over 36 months, starting in August. Requirements a. Update the data sheet with this information on the loan and use this to deal with all of the receipts and payments that are predicted to take place in the cash budget making sure that you link the data sheet to the cash budget. b. Complete all the calculations and processing needed to complete the cash budget (credit receipts, total receipts, total payments, cash flow for each month, opening and closing balances, total column). Think about the opening balance in the total column! What should this be? Make sure that the closing balance in December reconciles with the closing balance in the total column of the cash budget. c. Calculate the overdraft interest charge per month. As is standard practice, interest is calculated if the balance at the end of a month is in overdraft. It is paid in the following month. For this task you will need to use an IF formula and a ROUND formula. The overdraft interest rate is recorded in the data sheet. d. Complete the following presentation tasks on the final version of your cash budget: Appropriately title the cash budget Remove the gridlines Format cells so that negative numbers are presented in brackets Put appropriate borders/shading onto the cash budget as necessary e. Use the IF function (text based IFs) and conditional formatting to alert management to any issues c. Calculate the overdraft interest charge per month. As is standard practice, interest is calculated if the balance at the end of a month is in overdraft. It is paid in the following month. For this task you will need to use an IF formula and a ROUND formula. The overdraft interest rate is recorded in the data sheet. d. Complete the following presentation tasks on the final version of your cash budget: Appropriately title the cash budget Remove the gridlines Format cells so that negative numbers are presented in brackets Put appropriate borders/shading onto the cash budget as necessary e. Use the IF function (text based IFs) and conditional formatting to alert management to any issues. f. Protect your model. g. Record the answers to these what if? questions in the data sheet. Remember to reset your model with the original values after each question i. What would the total overdraft interest be if the \% overdraft rate falls from 10% to 7% per annum? ii. What is the value for total sales receipts if collection from receivables moves from 70% to 60% in 1 month and 30% to 40% in 2 months? iii. What would be the effect on the 31 st December bank balance if the prediction for annual wages was 180,000 ? iv. What would be the effect on total payments for August if the predicted expenditure on vehicles turned out to be 44,000 ? h. Produce a bar chart of the closing balances for each of the six months July - December. i. Comment on Essex's cash flow position. ESSEX LTD CASH BUDGET FOR SIX MONTHS ENDING 31st DECEMBER 2019 Receipts Payments \begin{tabular}{|l|c|c|c|c|c|c|c|} \hline Purchases & 63000 & 75807 & 86648 & 89020 & 83599 & 86818 & \\ \hline Wages & & & & & & & \\ \hline General Expenses & 7500 & 7500 & 7500 & 7500 & 7500 & 7500 & \\ \hline Rent & 5000 & & & 5000 & & & \\ \hline Motor Expeneses & 750 & 750 & 750 & 750 & 750 & 750 & \\ \hline Dividends Paid & & & & & 0 & & \\ \hline Vehicles & & & & & & & \\ \hline Fixtures \& Fittings & & & & & & & \\ \hline Loan Repayments & & & & & & & \\ \hline Loan Interest & & & & & & & \\ \hline TOTAL & & & & & & & \\ \hline Cash Flow For Month & & & & & & & \\ \hline Opening Balance & & & & & & \\ \hline Closing Balance \end{tabular} Receivables Schedule Sales \begin{tabular}{|l|l|l|l|l|} \hline 98450 & 112530 & 115610 & 108570 & 112750 \\ \hline \end{tabular} Receipts From Sales Receivables- 1 months Receivables- 2 months Total Receipts From Credit Sales \begin{tabular}{|l|l|l|l|l|l|l|} \hline 63000 & & & & & & \\ \hline 24300 & 27000 & & & & & \\ \hline & & & & & & \\ \hline \end{tabular} DATA SHEET ESSEX LTD ACTIVITY CASH BUDGET ESSEX LTD ACTIVITY Ready
