Accounting Information Systems Assignment | Online Assignment

I am in a middle of an exam and need the questions to be answered ASAP. Must be knowledgable in excel and accounting informations systems. You will have to do the work on excel and answer the questions being asked and upload excel work documents.

) Use the data set Excel Question 1.xlsx to perform the required analyses below. Submit your worked excel file to get credit. Hint: the primary keys for Sales_Subset, FGI_Product, and Customer are Sales_Order_ID, Product_Code, and Customer_ID, respectively.

Requirements:

1. a) In the ‘Sales_Subset’ table, create a new column named ‘Product_Description’ using information from the ‘FGI_Product’ table. (3 points)
2. b) In the ‘Sales_Subset’ table, create a new column named ‘Customer_St’ using information from the ‘Customer’ table. (3 points)
3. c) In the ‘Sales_Subset’ table, create a new column named ‘Revenue’ which shows the revenue for each sales order. (3 points)
4. d) In a new worksheet, show the TOTAL QUANTITY SOLD and TOTAL REVENUE for each Product_Description. (3 points)
5. e) In a new worksheet, show the TOTAL QUANTITY SOLD and TOTAL REVENUE for each Customer_St. (3 points)

2) Use the excel file Excel Question 2.xlsx to perform analyses and answer the following questions. The second worksheet is intentionally left blank for you to show your work. Submit worked excel file to get credit. Provide answers to questions a) and b) in the designated cells in your submitted excel file.

The bank offers you a $300,000 loan at 12% annual interest with quarterly payments for the next 25 years.

1. a) What is the quarterly mortgage payment? (3 points)
2. b) What is the total interest paid during the 25 years? (3 points)
3. c)  Create a line chart in excel showing the interest payment and principal payment for each payment period over the 25 years. The two lines must be in the same chart. (4 points)

3) Use the excel file Excel Question 3.xlsx to perform analyses and answer the following questions. Submit worked excel file to get credit. Provide answers to questions a), b), and c) in the designated cells in your submitted excel file.

A company borrows $200,000 in bonds today (year 0), paying a coupon of $5,000 twice a year over the next 15 years. The market interest rate is 6% per annum.

1. a) What is the coupon rate per annum? (3 points)
2. b) What is the present value of the cash flows related to the bond? (4 points)
3. c) Is the bond issued at a premium, at a discount, or at face value? Why? (4 points)

4) Benford’s Law also applies to the second digit. According to Benford’s Law, the distribution of the second digit is as follows:

Use the data set Excel Question 4.xlsx to perform the required analyses below. Submit your worked excel file to get credit. Provide answer to question c) in the designated cell in your submitted excel file.

1. a) Show the actual distribution of the second digit of Sales. Hint: the formula in Excel to extract the second character of a given cell A1 is MID(A1,2,1) (3 points)
2. b) Create a Combo Chart that shows both the actual percentage of each digit and the expected percentage of each digit according to Benford’s Law. (3 points)
3. c) Perform a statistical test to test if the distribution of second-digits is significantly different from that expected by Benford’s Law. Explain your conclusion. (4 points)

5) Use Excel to answer the following questions regarding the provided data set Excel Question 5.xlsx, which contains a random subset of the 2013 declined loan data from Lending Club. Submit your worked excel file to get credit. Provide answers to questions a) – g) in the designated cells in your submitted excel file.

Variable Descriptions

1. a) Show the number of observations for each Risk Score Bucket. Which Risk Score Bucket has the most and least observations? (3 points)
2. b) Show the number of observations for each DTI Bucket. Which DTI Bucket has the most and least observations? (3 points)
3. c) What is the number of observations that are in the Poor Risk Score Bucket, High DTI Bucket, and have an Employment Length of 8 years? (3 points)
4. d) Run a regression. Set the dependent variable to be the Amount Requested in the loan application, and the independent variables to be both Risk_Score and Debt-To-Income Ratio. What is the R-Squared of the regression? (3 points)
5. e) Are Risk_Score and Debt-To-Income Ratio statistically significnat predictors of Amount Requested at the conventional 5% significance level? Explain your conclusion. (3 points)
6. f) Interpret the coefficient of Risk_Score. (3 points)
7. g) Compare actual Amount Requested to the estimated Amount Requested using a scatter plot. What is the predicted value for Amount Requested for an application with a Risk_Score of 737 and Debt-To-Income Ratio of 16%? (6 points)