Question Details

[solution] » ACF5320 Business Analytics, Assignment 2 (individual

Brief item decscription

Step-by-step solution file


Item details:

ACF5320 Business Analytics, Assignment 2 (individual
More:

Hi

I need help with the task 1 of this assignment. I am not really sure as to how is the counties coverage column and choose row is calculated and how is it related to the B59 cell?


ACF5320 Business Analytics, Assignment 2 (individual assignment)

 

Prepared by Dr. Kristian Rotaru, September 2016 1 Assignment 2

 

*** Please note that this is an INDIVIDUAL assignment *** Part A. About the Assignment

 

Weighting:

 

Deadline: 20% of Total Mark Monday 10 October (Week 11), 1:00pm Submission made after the deadline will be automatically marked ?late?, and

 

penalty will be imposed on the submitted work. Work uploaded 1 week after

 

submission deadline will be discarded. In general, no late submission will be

 

accepted. In special circumstances, students may apply for extension by

 

consulting the lecturer at least 2 days prior to the submission date. If approved,

 

submission must be done within the indicated timeframe. The maximum

 

extension that can be granted is 3 days from the deadline. Submission: You are required to submit BOTH hardcopy and softcopy of

 

your work. Hardcopy (printed copy) of your work can be submitted through the

 

Assignment Drop Box on Level 3, Department of Accounting, Building H, Caulfield

 

campus. A redundant, softcopy submission should be made through Moodle. If the

 

(default) hardcopy submission does not exist, then the softcopy submission will

 

be marked. The receipt of the softcopy is absolutely necessary for your assignment

 

to be marked. So, please ensure you submit the correct version of your work ?

 

both in hardcopy and softcopy; and that the hardcopy is not submitted late

 

(passing deadline). It is imperative for students to do both submissions (hardcopy

 

and softcopy) to avoid missing submission. By having a redundant system, we are

 

preventing possible dispute with regards to lost submission. Students who only

 

rely on one mode of submission are taking the risk of their assignment being lost

 

in the system. Finally, please ensure that the softcopy of your submission (both

 

Excel file(s) and Word (or PDF) file) is not password protected. For the softcopy submission, zip all your files (including the excel file and the cover

 

sheet) and submit it via Moodle. You must use WinZip to compress your files

 

(please do not use any alternative software to compress your files). Attach the

 

signed cover sheet with your hardcopy submission. This signed cover sheet is a

 

declaration that your submission is your own work. Any attempt of plagiarism

 

will be heavily penalized. If deemed necessary, Lecturer/Tutor/Marker may opt 2 ACF5320 Business Analytics, Individual Assignment

 

Prepared by Dr. Kristian Rotaru, September 2016 to interview students on their submissions and this may be reflected on student?s

 

mark. Naming File: The zip file should be named X_Y_A2.zip

 

where

 

X is up to 5 characters is your SURNAME.

 

Y is up to 3 characters is your FIRSTNAME. Part B. Problem Description

 

Instruction:

 

This assignment consists of three cases (Case 1: Planning the Sales Territory, Case

 

2: Audit Delay, and Case 3: Corporate Bonds) and three sets of tasks. It is important

 

that you read and understand the topics and have acquired skills to successfully

 

develop spreadsheet models before carrying out the assignment. Please note that

 

these cases are not optional. You need to attempt three cases in this assignment

 

and all associated questions/tasks. Case 1: Planning the Sales Territory

 

Problem Statement

 

Caro-Life is a financial services firm that specialises in selling life-, auto-, and

 

home- insurance to residential consumers in the state of North Carolina. The

 

company is planning to expand and offer its services in South Carolina as well.

 

The company wants to open a set of 10 offices throughout the state in such a way

 

to ensure that all residents of the state can access at least 1 office in either their

 

county of residence or an adjacent county. The set of counties adjacent to the

 

country containing each office will be regarded as the sales territory for that

 

office. (Note that a county is considered to be adjacent to itself). Figure 1 (and

 

the file Planning_Sales_Territory.xlsm, which you will find in the relevant

 

Assignment 2 folder on Moodle) shows a portion of an Excel spreadsheet with a

 

matrix indicating county adjacencies throughout the state and the estimated

 

population and geographic size (in square miles) for each potential sales

 

territory (Values of 1 in the matrix indicate counties that are adjacent to one

 

another).

 

Sales of insurance products in a given area tend to be highly correlated with the

 

number of people leaving in the area. As a result, agents assigned to the various

 

offices want their sales territories to contain as many people as possible (to

 

maximise sales potential). On the other hand, territories containing large

 

amounts of people may also be comprised of a large geographic area that may ACF5320 Business Analytics, Assignment 2 (individual assignment)

 

Prepared by Dr. Kristian Rotaru, September 2016

 

require lots of travel on the part of the agents. So the goal of having a territory

 

with lots of people is sometimes in conflict with having a territory that is

 

compact in size. It is important for Caro-Life to design its sales territories in as

 

equitable manner as possible (that is, where the territories are similar in terms

 

of geographic size and sales potential). Figure 1: A screenshot of the data file for the Caro-Life sales territory

 

planning problem 3 4 ACF5320 Business Analytics, Individual Assignment

 

Prepared by Dr. Kristian Rotaru, September 2016 Task group 1 (7 marks):

 

(1.1) Assume Caro-Life wants to maximise the average sales potential of its 10

 

offices. Formulate an optimization model that would reflect the solution of this

 

problem. Use Analytic Solver Platform to solve this problem. Where should it

 

locate offices, and what is the population and geographic area associated with

 

each office?

 

(1.2) Assume Caro-Life wants to minimise the average geographic area covered

 

by each of its 10 offices. Formulate an optimization model that would reflect the

 

solution of this problem. Use Analytic Solver Platform to solve this problem.

 

Where should it locate offices, and what is the population and geographic area

 

associated with each office?

 

(1.3) Determine the solution that minimises the maximum percentage deviation

 

from the optimal objective function values identified in parts (1.1) and (1.2).

 

Formulate an optimization model that would reflect the solution of this problem.

 

Use Analytic Solver Platform to solve this problem. According to the solution,

 

where should Caro-Life locate its offices, and what is the population and

 

geographic area associated with each office?

 

(1.4) Suppose Caro-Life considers maximising average sales potential of its

 

territories to be twice as important as minimising the average geographic size of

 

its territories. Formulate/update the mathematical model supporting this

 

condition and, using Analytic Solver Platform, find the solution that minimises

 

the maximum weighted percentage deviations. According to this solution, where

 

should Caro-Life locate its offices, and what is the population and geographic

 

area associated with each office?

 

(1.5) What other issues would you suggest Caro-Life take into account in

 

modelling this decision problem? Your focus here should be on making the

 

model more ecologically valid (realistic/relevant given the nature of the problem

 

discussed in this case). ACF5320 Business Analytics, Assignment 2 (individual assignment)

 

Prepared by Dr. Kristian Rotaru, September 2016 5 Case 2: Audit Delay

 

Problem Statement

 

A study investigated the relationship between audit delay (the length of time

 

from a company?s fiscal year-end to the date of the auditor?s report) and

 

variables that describe the client and the auditor. Some of the independent

 

variables that were included in this study are presented in Table 1 below.

 

Table 1: A screenshot of the data file for the Caro-Life sales territory

 

planning problem

 

Variables

 

Industry

 

Public

 

Quality

 

Finished Description

 

A binary (also known as ?dummy?) variable coded 1 if the firm

 

was an industrial company, or 0 if the firm was a bank, savings

 

and loan, or insurance company.

 

A dummy variable coded 1 if the company was traded on an

 

organized exchange or over the counter; otherwise coded 0.

 

A measure of overall quality of internal controls, as judged by

 

the auditor, on a five-point scale ranging from ?virtually none?

 

(1) to ?excellent? (5).

 

A measure ranging from 1 to 4, as judged by the auditor, where

 

1 indicates ?all work performed subsequent to year-end? and 4

 

indicates ?most works performed prior to year-end?. A sample of 40 companies is provided in the Excel file Audit_delay.xlsx which

 

can be found in the relevant Assignment 2 folder on Moodle. Task group 2 (6.5 marks)

 

(2.1) Develop the estimated regression equation using all of the independent

 

variables included in the data.

 

(2.2) Test for an overall regression relationship at the 0.05 level of significance.

 

Is there a significant regression relationship?

 

(2.3) How much of the variation in the sample values of delay does this

 

estimated regression equation explain? What other independent variables could

 

you include in this regression model to improve the fit?

 

(2.4) Test the relationship between each independent variable and the

 

dependent variable at the 0.05 level of significance, and interpret the

 

relationship between each of the independent variables and the dependent

 

variable.

 

(2.5) On the basis of your observations about the relationships between the

 

dependent variable Delay and the independent variables Quality and Finished,

 

suggest an alternative regression equation to the model developed in your

 

answer to the question (2.1) to explain as much of the variability in Delay as

 

possible. 6 ACF5320 Business Analytics, Individual Assignment

 

Prepared by Dr. Kristian Rotaru, September 2016 Case 3: Corporate Bonds

 

Problem Statement

 

A sample containing years to maturity and yield (percent) for 40 corporate

 

bonds is presented in the data file named Corporate_bonds.xlsx. The data file

 

can be located in the Assignment 2 folder on Moodle. Task group 3 (6.5 marks)

 

(3.1) Develop a scatter chart of the data using years to maturity as the

 

independent variable. Does a simple linear regression model appear to be

 

appropriate? Explain your answer.

 

(3.2) Develop an estimated quadratic regression equation with years to maturity

 

and squared values of years to maturity as the independent variables. How much

 

variation in the sample values of yield does this regression model explain? Is the

 

overall regression relationship significant at a 0.05 level of significance? If not,

 

explain why it is the case. If yes, then test the relationship between each of the

 

independent variables and the dependent variable at a 0.05 level of significance.

 

How would you interpret this model?

 

(3.3) Create a plot of the linear quadratic regression lines overlaid on the scatter

 

chart of years to maturity and yield. Does this help you better understand the

 

difference in how the quadratic regression model and a simple linear regression

 

model fit the sample data? Which model reflected on this chart provides a

 

superior fit to the sample data? Why?

 

(3.4) What other independent variables could you include in your regression

 

model to explain more variation in the yield? Explain your answer.

 







About this question:
STATUS
Answered
QUALITY
Approved
ANSWER RATING

This question was answered on: Feb 21, 2020

PRICE: $24

Solution~00065744301.zip (18.37 KB)

Buy this answer for only: $24

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free copy (Deadline assured. Flexible pricing. TurnItIn Report provided)

Pay using PayPal (No PayPal account Required) or your credit card. All your purchases are securely protected by PayPal.
SiteLock

Need a similar solution fast, written anew from scratch? Place your own custom order

We have top-notch tutors who can help you with your essay at a reasonable cost and then you can simply use that essay as a template to build your own arguments. This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student. New solution orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

Order Now