Excel Support Limited (ESL) You started working at ESL as an assistant accountant upon graduation from the Accounting certificate from York University. Currently, you are pursuing your CPA designation and are planned to write the CFE next year. Last year, the accountant of ESL, Else, asked you to go to her office and she shared with you her plan to retire in 2 years. She asked if you are interested to be the accountant of ESL as soon as she retired as she intended to take 1-2 years to train you up for the new role. You agreed without a second thought as you enjoy working as ESL. The first thing she taught you is to prepare the cash budget each year. Once she showed you the file on the server, you found that something isn't right as Else did not use any formula to calculate the numbers. She just calculated the numbers manually and input into the file. You remembered that you have learned how to make use of Microsoft Excel to prepare a cash budget and you wanted to prepare your own cash budget for the year. The cash budget for the current year is important because it will be used by ESL to be presented to the bank for a loan. You have gathered the following assumptions after several meetings with Else and the owner, Mike. All of you agreed with the assumptions and you started working on the cash budget soon after. ESL is contemplating several alternative means of financing their acquisition of $100,000 in new equipment in year 1. One option is to borrow $80,000 from a local bank. The bank has asked them to produce a 3-year cash budget broken down by year (Year 1, 2, and 3). Sales of $250,000 were earned in the prior year and are expected to increase each year thereafter by 15 percent. Prior year purchases were $195,500. Future purchases are based on an expected cost of sales of 75 percent and a required ending inventory of 20 percent of next year's sales. Prior year expenses included advertising expense of $15,000, depreciation expense of $1,000, wages expense of $20,000, supplies expense of $1,000, and utilities expense of $3,300. All expenses except depreciation and interest expense are paid in the year in which they are incurred and are expected to increase 5 percent each year. Interest expense is paid in the year incurred and is expected to remain constant at $4,000 each year for years 1-3. Collections in the year of sales are expected to be 80%, 10% will be collected the year after with the remaining 10% collected in the subsequent year. Payments in the year of purchase are expected to be 90 percent, with the remaining 10 percent paid in the next year. Proceeds from the $80,000 loan are expected at the beginning of year 1, and $100,000 of equipment will be purchased during year 1. In subsequent years, equipment purchases are expected to be $2,000 each year. Proceeds from projected equipment sales each year are expected to amount to $500. Annual payments of $10,360 on the loan occur at the end of each year. Cash and Inventory at the beginning of year 1 were $20,200 and $58,000 respectively. You are planning to use Excel's grouping feature to group operating cash receipts, operating cash payment, cash from (to) operating activities, cash from (to) investing activities, and cash from (to) financing activities and to define names as appropriate. https://www.coursehero.com/file/39689458/Assignment-4-ExcelAssignmentpdf/ This study resource was shared via CourseHero.com CSAC2511 Excel Assignment 2018 Summer (Morning session) 2 During the meeting, Mike asked you to write a Memo addressing the followings issues: 1. Mikes wants to know the cash balance base on different scenarios, use what-if analysis to calculate end-of-year cash assuming that the sales growth each year were a. 0% (Save the result in one worksheet and named "What-if-0%") b. 1% (Save the result in one worksheet and named "What-if-1%") c. 5% (Save the result in one worksheet and named "What-if-5%") d. 10% (Save the result in one worksheet and named "What-if-10%") e. 15% (Save the result in one worksheet and named "What-if-15%") You only need to summarize the amount of cash balance at the end of the period under each assumption in your memo. 2. Mike also wants to know the annual sales growth would be needed to produce an ending cash balance of $50,000 in year 3. You need to use "goal-seek function" to calculate the amount and save the work on a new worksheet "Goal-seek". Mike is curious to know if there is any other alternative that could help attain the cash balance of $50,000 in year 3 without counting on the sales growth. Please discuss 3 alternatives. 3. You found that the previous cash budget prepared by the accountant was saved on the server in which all of the employees could access. As ESL is expanding and that the information is very sensitive and it would be detrimental to the business if it is stolen by the competitor, please suggest some ways to improve the security and confidentiality of the financial information. Please ensure that your suggestion is feasible in terms of ESL's business nature and business size. Required: Please submit TWO files 1. Excel with filename: Student Name_Student #_CashBudget 2. Memo in Word format with file name: Student Name_Student #_Memo (800 - 1,000 words) Deadline: Aug 10, 2018 by 11:59pm (submission on Moodle) https://www.coursehero.com/file/39689458/Assignment-4-ExcelAssignmentpdf/ This study resource was shared via CourseHero.com Powered by TCPDF (www.tcpdf.org)