Step-by-step solution file
ACF5320 Business Analytics, Assignment 2 (individual
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
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
X is up to 5 characters is your SURNAME.
Y is up to 3 characters is your FIRSTNAME. Part B. Problem Description
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
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
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
(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
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
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
(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
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.
This question was answered on: Feb 21, 2020
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.