#### 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)

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

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

Answered

QUALITY

Approved

ANSWER RATING

This question was answered on: * Feb 21, 2020 *

* * Solution~00065744301.zip (18.37 KB)

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)

### 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.