Reference no: EM133012875

Accounting Information Systems

Develop practical competence in using computers in the business environment by:
a. becoming a stronger user of MS Excel at the intermediate level
b. understanding the fundamentals of relational database design and being able to create a simple relational database;
c. develop queries and reports using MS Access or equivalent;
d. demonstrate the ability to perform queries into an accounting database using external programs such as MS Access or Crystal Reports
e. design and draw system flowcharts and or process flow diagrams using MS Visio or equivalent (Smart Draw or Open Office Draw)

QUESTION 1

A. SPREAD SHEET DESIGN

The Buds and Bloomers is contemplating several alternative means of financing their annual acquisition of $85, 000 in equipment. One option is to borrow $350,000 from a local bank for 5 years at 11 percent per annum. The bank has asked them to produce a 4-year cash budget broken down by year (2020 through 2023).

Sales of $850,000 are expected in 2020, with sales increasing each year thereafter by 17.5 percent. Sales in 2019 were $700,000. Purchases are based on an expected cost of sales of 40 percent and a required ending inventory of 15 percent of next year's sales. Purchases in 2019 were $300,000, and beginning inventory was $107, 500. Annual expenses include advertising expense of $20,000, marketing expense of $7,000, depreciation expense of $9,000, interest expense of $45,000, salaries expense of $250,000, wages expense of $75,000, supplies expense of $8,000, and utilities expense of $12,000. All expenses except depreciation are paid in the year in which they are incurred and are expected to increase 7.5 percent each year.

Collections in the year of sale are expected to be 90 percent, with the remaining 10 percent collected in the next year. Payments in the year of purchase are expected to be 92 percent, with the remaining 8 percent paid in the next year. Proceeds from the $350,000 loan from the bank are expected in 2020, and $85,000 of facilities will be purchased each year. Proceeds from expected equipment sales each year are expected to amount to $20,500. Annual repayments of $96,572 on the loan also begin in 2020. The beginning cash balance in 2020 was $30,000.

Required:

Create a cash budget based on the assumptions above. 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.

Ignore income tax and GST in your calculations.

Then you have to do the following:

1. Print the newly completed worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer.

2. Print the worksheet from part a, above, in Formula view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print only columns A, B, and C of the cash budget, no assumptions.

3. Collapse rows to level 2; then print the worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print cash budget only, no assumptions.

4. Collapse rows to level 2, and then use what-if analysis to calculate end-of-year cash if the sales growth each year were 20 percent. Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print cash budget only, no assumptions.

5. Undo the what-if analysis performed in part d. Collapse rows to level 2, and then use goal seek to determine what annual sales growth would be needed to produce an ending cash balance of $150,000 in 2022. Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print cash budget and assumptions. (5 marks)

QUESTION 2

B. Relational Databases

You should download the excel file from Moodle (BLT Raw Files.xls) to complete the requirements of this question. For questions 1 and 2, please include examples cited from the Balance Consultancy's Invoices' data to validate the points in your discussions. You may present your answers through MS Word.

Required:

1. Discuss the problems that may occur if all invoices from Balance Consultancy will be stored in one Uniform data table? How about variations of the number of Columns?

2. Evaluate each basic requirements in designing Balance Consultancy's relational database and discuss how each requirements can eliminate the identified problems in Question 1.

3. Using the normalization approach in database design, work with the provided unnormalized data (excel file you have downloaded) to create a Third Normal Form (3NF) for your relational database for Balance Consultancy Ltd. (Please provide a screen shot for each form)

4. Develop queries and report, using MS Access, for the following queries: (you are required to take a screen shot of your query showing the relational database and the report)
a. How many sales were made for the entire year of 2020?
b. Who are the customers that don't need to pay GST?
c. How many hours of each services were rendered from 2019 to 2020?
d. What are the services offered by the BCL and which service was offered most frequently?
e. Who are the top clients that make most use of the each services of BCL?
f. Present each consultant's sales amount before tax for the 2019 year in descending order?
g. What were the total sales after tax in the year 2020?
h. What was the average amount of sales after tax transaction for the year 2020?

i. Which customers (show their names) has spent hours with V. Wilson, stating how many hours, type of services, and the total amount before and after tax they spent?
j. To whom and what were the services and hours that was provided by BLT under invoice INV0069? How much did the client pay after tax for each services supplied?
 
QUESTION 3

C. System Flowcharts
As the new internal auditor for Absolute Trading Ltd, you have been asked to document the company's acquisition/payment system. Based on your documentation, Absolute Trading Ltd hopes to develop a plan for revising the current system to eliminate unnecessary delays in acquisition/payment processing. The head of purchasing, Xyril Laurent explained the system:

A purchase requisition is sent from the inventory system to Xyril. Xyril prepare a purchase order using the vendor and inventory files and mails it to the vendor. The vendor returns a vendor acknowledgement to Xyril indicating receipt of the purchase order. Xyril then sends a purchase order notification to Xander Jake, an accounting payable clerk.

When receiving department accepts vendor goods, the inventory system sends Xander a receiving report. Xander also receives invoices from the various vendors. Xander matches the invoices with the purchase order notification and the receiving authorization to the accounting department. There, Xeena Francesca prepares and mails a cheque to the vendor. When the cheque is issued, the system automatically updates the accounts payable master file and the general ledger.

Required:
1. Develop a context diagram and a level 0 DFD of the acquisition/payment system At Absolute Trading Ltd.
2. Prepare a document flowchart to document the acquisition/payment system at Absolute Trading Ltd.

Attachment:- Accounting Information Systems.rar