Hi,

Could you help me do the access based on this Excel data. I already High Light the requirements in the Description documents, please email me if you have any questions. The due date is 11/06, THANK YOU

Z Chedd'r Project Summary of Interview with Woody Guthrie, Clerk and Accounting Assistant to Chad Mitchell The following interview was recently conducted to provide more information on aspects of Z Chedd'r financial reporting processes. Woody usually assembles the end-of-quarter data necessary to prepare financial statements for each store and for Z Chedd'r overall. He understands that you will be performing this task, but he wanted to provide you some background on the process that he has used in the past. Income statement Sales 1. First, Woody takes the sales item transaction data (for retail, wholesale, and internet sales) and ensures that all transactions occur within the quarter. He then checks the Item Amount extension to make sure that sales amounts are calculated correctly. He calls the results the Sales Detail for Quarter. 2. Using the Sales Detail for Quarter, he then summarizes the sales for each store, sale type, and inventory item. In this step, he calculates the total quantity of each inventory item sold in the quarter and the total revenue for each item. He calls this result the Store Sales Detail. 3. He then uses the Store Sales Detail to summarize quarterly sales by store. He calls this result the IS Stores Sales. 4. Finally, he can summarize the sales amounts from the IS Stores Sales to produce total sales for Z Chedd'r. He calls this result IS Sales Total. Cost of Goods Sold 1. Calculating cost of goods is the hardest part of the income statement. In all cases, the transactions need to be limited to the quarter. Woody calculates each of following for the stores and then summarizes for Z Chedd'r. a. Beginning inventory. b. Purchases. c. Transfers Out. d. Transfers In. e. Promotions. f. Ending inventory. 2. The goods available for sale are calculated as beginning inventory plus purchases minus transfers out plus transfers in minus promotions. 3. Then, subtract ending inventory to compute cost of goods sold for each store and total to get the overall cost of goods for Z Chedd'r. Pay Expense 1. First, Woody limits the payroll transactions to those within the quarter and calculates the pay expense for each transaction. Pay expense includes gross pay plus employer contributions to FICA and MC. He calls this result Pay Expense Detail for Quarter. He can use this result to compute amounts owed to federal and state government for taxes. 2. Then, Woody summarizes the Pay Expense Detail for each store. He calls this result IS Stores Pay Expense. Finally, he uses IS Stores Pay Expense to calculate overall pay expense for Z Chedd'r. 1 Z Chedd'r Project General expenses 1. Woody first limits the miscellaneous expenses (rent, electricity, phones, and credit card bills for example) to those incurred during the quarter. These purchases are recorded when they are incurred, so there are no prepaid amounts. Woody calls this result Miscellaneous Expense Detail. 2. Woody then uses the Miscellaneous Expense Detail to summarize those expenses by store. He calls this result IS Stores Miscellaneous Expense. He then summarizes the expenses into one amount for Z Chedd'r general expenses. Promotion Expenses 1. Woody uses the information on promotion expenses subtracted from cost of goods sold as the promotion expense amount. Van and Store Depreciation 1. Woody divides the annual depreciation figures for each van and the owned store by 4 to calculate the quarterly depreciation expense. Credit Card Fees 1. Finally, Woody has to calculate the credit card fees as the difference between sales plus shipping amounts and the corresponding cash receipts. 2. First, in the same way that he summarized sales by store, he summarizes the sales plus the shipping amounts (which are zero except for the internet sales). He calls this result Store Sales plus Shipping. 3. Then, he summarizes cash receipts by store. He calls this result Store Cash Receipts. 4. Finally, he subtracts the total quarterly cash receipts by store from the total quarterly sales plus shipping amounts for each store to compute the credit card fees. He calls this result IS Stores Credit Card Fees. He totals the fees for the stores to determine the overall credit card fees for Z Chedd'r. Balance Sheet Woody says that the balance sheet is easier to prepare, since he doesn't need to calculate balance sheets for individual stores. He just prepares one overall balance sheet. Assets. Cash. The cash balance is calculated as the beginning cash balance plus cash receipts less cash disbursements (cash disbursements for inventory purchases, cash disbursements for miscellaneous expenses, and payroll). Accounts Receivable. Woody says that AR amounts are usually small. They only arise when one of the wholesale customers fails to pay by the end of the month. He calculates AR as wholesale sales minus cash receipts for those wholesale sales. Inventory. Woody uses the physical count of inventory at the end of quarter as the basis for the ending inventory value. Inventory is valued at average cost. 2 Z Chedd'r Project Stores and Vans. Woody uses the book values of the owned store and the vans. He computes accumulated depreciation including the depreciation for the current quarter. Liabilities. Again, he says accounts payable are usually small. Trade accounts payable only arise if Z Chedd'r is late on a payment to an inventory supplier. Other accounts payable can be more substantial, because those include taxes payable to federal and state governments. Z Chedd'r has no mortgages or notes outstanding. Z Equity. Since Mr. Z owns all the stock and he doesn't need a breakdown of stockholders' equity, Woody usually computes and single Stockholders' Equity value. 3 Guide to Importing EXCEL data into Access: Create a blank Access database, appropriately named. Select the \"External Data\" tab. Click on the Import from EXCEL icon. The import wizard will open. First, you need to select the EXCEL file to import. Select the file and click OK. The first screen asks you to select the specific worksheet to import. Be sure to check the box that specifies that the first row contains column headings. The 3rd screen in the wizard asks you to select a primary key. DO NOT LET ACCESS add a primary key. Either pick a primary key or select No Primary Key (you can designate the primary key after you import the file.) Accept or change the table name to import the data to. Click on the Finish button to complete the import. The table is now in ACCESS. You can open the table in design view to designate the primary key if necessary. Z Cheddr Project Fall 2016 Z Cheddr Project Description and Requirements Background Mr. Z (real name Josh Zubkoff) runs a successful chain of cheese (and wine) shops in Oregon that specialize in Oregon artisan cheeses. He also sells his cheeses over the internet. He is considering expanding his business, but he thinks his current accounting and financial systems are restricting his ability to expand. He has come to you for assistance. Instructions Your supervisor met with Mr. Z and discussed his requirements in detail. At that meeting, they agreed on the following deliverables for your project. 1. Document process activity using BPMN. After gaining an understanding of Z's business processes, you will document current processes using business process modeling notation (BPMN). This involves documenting at least five current business processes. These diagrams will serve multiple purposes. Mr. Z expects to use them to train new employees. Plus, they will provide information about potential improvements to those processes by the efficient use of information technology. Additionally, they will also highlight areas where Mr. Z is exposed to unnecessary risks in his business. 2. Prepare an integrated data model using UML class diagrams and the REA framework. This data model will serve as the blueprint for Z's prototype database that you will also develop. 3. Prepare a prototype database for Mr. Z. He has agreed to provide financial data from his business for the 1st quarter of his fiscal year. That data will be in EXCEL format. You will move the data into Access, structure it appropriately, and set relationships consistent with your data model. 4. Prepare financial reports using your database. You will prepare queries as necessary to provide the following reports and management information. a. Z Cheddr income statement for the 1st quarter. Remember that Z uses accrual accounting. 1 Z Cheddr Project Fall 2016 b. Z Cheddr balance sheet as of the end of the 1 st quarter. c. Income statements for each Z Cheddr store for the 1 st quarter. d. Analysis of product sales and profitability for the 1 st quarter. Determine product turnover and profitability for each inventory item. 5. Evaluate the financial performance of Z Cheddr for the 1 st quarter. Use your Access queries but also prepare formal financial statements and appropriate ratios to evaluate performance against the industry and past performance. 6. Assess the business case for use of information technology in Z's business (see chapters 13 and 14 in the text). In particular, Mr. Z wants to move to a cloud-based accounting system, such as Netsuite. He wants to upload transactions daily from new smart cash registers in each store. 7. Z Cheddr wants to structure their internal controls following the new COSO principles. Recommend actions that Z Cheddr should take to comply with each of the 17 COSO principles. (Hint: prepare the recommendation in table form with one column for the principle, one for what Z Cheddr does now, and the third for what they should do in the future). Report to Mr. Z Each group will submit all the deliverables in one report to Mr. Z at the end of the project. You will also submit your prototype Access database with all the queries along with that report. The report should be professionally prepared in an executive summary style and not longer than 6 pages not counting any appendices or attachments. The appendices or attachments should include financial statements, data model and activity diagrams, and other information necessary to support your analyses and conclusions. The content of the report should be clear to an accounting novice such as Mr. Z. Most of the content should be presented using tables, charts, and bullet points. In all cases, you should avoid long paragraphs. Specific content of the report should include: Financial performance evaluation as outlined in 4 and 5 above. Business case for technology as outlined in 6 above. COSO evaluation as outlined in 7 above. Administrative Information Submit your complete project through the dropbox on or before the date specified in the class schedule unless notified otherwise. The complete project includes one report with all appendices and the Access database with clearly named queries for each group. Each member of a group must submit their own individual peer evaluations. As soon as your group is ready, you should schedule an interim review of your data models and activity diagrams with me. I will provide feedback on the models that you should then incorporate before the final report is due. Grading criteria. I will provide detailed grading criteria by the time you are ready for your interim reviews. I will grade your project relative to the other groups. In general, however, I expect the following: I expect you to know how to prepare financial statements and how to explain them to a nonaccountant. I expect professional work; quality is important. 2 Z Cheddr Project Fall 2016 I expect accuracy (do not make assumptions; ask Mr. Z). I expect you to support your recommendations with facts; explain why Z Cheddr should follow your advice. Where possible, tie your recommendations to your activity and/or structure diagrams. Tip. The best grades typically go to the group that starts fast, gains a thorough understanding of Mr. Z's business through questions and examining the data, and does the best job of dividing up the workload. You should be curious about everything and check all your results at least twice. Lifelines. You get two free lifelines. In other words, if you are stuckyou know what to do but you don't know how to do it you can ask me for help twice without penalty. After that, it will cost you 5 points per request. The choice to use a lifeline should be a group decision. Group participation. I will reserve 25% of the grade to adjust according to your group's peer evaluations that must be submitted separately by each individual group member on or before the project due date. I will first determine the group grade. Then, using the group grade as the average grade for all group members, I will adjust grades upward for the group members that contributed the most and downward for the group members that contributed the least. If you fail to submit a peer evaluation, you will be penalized 5 points. 3