As a follow-up assignment to our review of the Schafer Lemonade Stand (SLS) Case, you have been engaged to automate as much of the Revenue Cycle of SLS as possible. Since the SLS is owned and operated by two young adults, you have been asked to complete the automation in Excel. In the course of your work, you are to observe the conditions and requirements listed below.
Conditions and Requirements:
Your project must meet the following requirements:
- Your Revenue Cycle Automation project is for the month of July, 2013 (typically the busiest month of the year for SLS)
- All items shown in the attached chart must be included in your finished product. They include:
1. Input Forms
2. Data Tables
3. Output Forms and Reports
- All automation is to be included in one Excel workbook, utilizing separate worksheets. No data item should be input more than once in the workbook. All "results" or "computations" must be produced using Excel functions and formulas.
- All "user" items (input forms, reports) should be formatted for ease of use and professional in appearance.
- All tables, calculations and critical data should be appropriately protected or hidden (if you use passwords for protection you will need to submit your passwords with your finished project).
- To allow for a realistic experience, you will need to create and appropriately process the following:
1. A Golf Course Member List of at least 15 members including all of the pertinent information relating to each member
2. The company information for the Golf Course as needed
3. An initial Product Inventory listing with opening amounts on hand including each of the flavors identified in the SLS Case abstract
4. Sales transactions for the month of July (at least 30 transactions) that include each of the 15 members and all of the offered flavors
- The assumptions of this project include:
1. Only one stand at one course
2. There are two employees
3. Cups and ice are not listed in inventory
4. Lemonade is purchased in bottles and delivered with a cup and ice
5. Members receive monthly statements (in one envelope from each "store" such as the lemonade stand, pro-shop, restaurant, etc.) from the golf course to be remitted
6. The members send a check made payable to the lemonade stand for all of their lemonade stand charges monthly
7. The golf course receives a statement from the lemonade stand for the total sales for the month to charge rent for the facilities (lemonade stand).
8. For this exercise, our scope is to limit the lemonade stand to the revenue process (sales and cash receipts).
9. We are not creating entries for monthly expenses such as rent and preparing the financial statements.
10. The golf course provides information on new members as they join, and Emma adds their information to our member records.
- The case narrative is purposely vague and closer to what you would find in a conversation with a client than a textbook problem. The narrative is designed such that it might come from someone with a background in viewing a system from a document flowchart view. You need to realize that many businesses might have some strange and "extra" processes and documents floating around that may or may not be needed for efficient and effective processing of the business information and paper that is not needed in a computerized system.
Attachment:- Project Data.xlsx