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.

Don't use plagiarized sources. Get Your Custom Essay on
Accounting Information Systems Assignment | Online Assignment
For $10/Page 0nly
Order Essay

 

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:

Digit 0 1 2 3 4 5 6 7 8 9
Probability 12.0% 11.4% 10.9% 10.4% 10.0% 9.7% 9.3% 9.0% 8.8% 8.5%

 

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

Variable Description
Amount Requested Requested loan amount
Application Date Date of loan application
Loan Title Brief description of loan purpose
Risk_Score LendingClub’s calculated risk score
Debt-To-Income Ratio Debt to income ratio
Zip Code First 3 digits of applicant’s zip code
State State
Employment Length Employment length in years. Possible values are between 0 and 10 where 0 means less than one year and 10 means ten or more years.
Policy Code Internal Number
DTI Bucket High if Debt-To-Income Ratio > 20%

Mid if Debt-To-Income Ratio is 10-20%

Low if Debt-To-Income Ratio is < 10%

Risk Score Bucket Excellent if Risk_Score is 800-850

Very good if Risk_Score is 750-799

Good if Risk_Score is 700-749

Fair if Risk_Score is 650-699

Poor if Risk_Score is 600-649

Very bad if Risk_Score is 300-599

 

  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)

 

Calculator

Calculate the price of your paper

Total price:$26

Need a better grade?
We've got you covered.

Order your paper