1008 Business Decision Making
1008 Business Decision Making
Trimester 1, 2023
Assignment-1
Instructions:
This computing assignment is worth 40 marks - 40% of the overall assessment for the course.
Submission due date/time: Friday, 12 May 2023, 5:00pm
Complete all numerical calculations, graphs, plots and charts using Excel.
Copy and paste the Excel outputs (e.g. graphs, charts, numerical outputs, regression outputs, etc) to your Word document to display working on your assignment.
Marks will not be awarded if the appropriate Excel outputs are not provided.
Your final submission document must be presented in a Word document in .doc, docx or pdf format. No Excel files should be submitted!
You are required to keep a hard copy and an electronic copy of your submitted assignment to re-submit, in case the original submission is lost for some reason.
Important Notice: As this is an individual assessment item, students MUST work on their own and present their individual assignment submission. If found to have cheated, your submission would receive a mark of zero for this assessment.
Answer all questions.
QUESTION 1 4 marks
Using the data given in the excel file in Sheet1 are the number of overseas arrivals and overseas departures in different states in September 2022.
Using the information presented in worksheet Q1, answer the following questions.
Which graphical technique would you use for making comparison in the number of overseas arrivals in each state? Explain your reasoning in selecting this graphical chart. Using Excel, construct and present the chart. 1 marks
Which graphical technique would you use for making comparison of the proportion of the number of overseas arrivals in each state? Explain your reasoning in selecting this graphical chart. Using Excel, construct and present the chart.
1.5 marks
Which graphical technique would you use for making comparison in the number of overseas arrivals and overseas departures in each state? Explain your reasoning in selecting this graphical chart. Using Excel, construct and present the chart.
1.5 marks
QUESTION 2 11 marks
A researcher wishes to draw a histogram in presenting graphical summaries of the data on the two main variables concerned Weekly income and consumption expenditure. These variables are given in Sheet2 in the excel data file.
She decided to use 7 class intervals to construct a histogram for each variable. Explain how the researcher could have decided in using 7 as the number of class intervals to be used. 2.5 marks
The researcher specified class intervals $200 < X $300, $300 < X $400, , $800 < X $900 for the weekly income variable. Explain how the researcher could have decided on the width of each class for the income variable.
2.5 marks
Using Excel, draw and display the histogram for the weekly income variable using the class intervals specified in part (b). Comment on the shape of the distribution of the data set as displayed in the histogram. 3 marks
Using Excel, draw and display the histogram for the weekly consumption variable using the appropriate class intervals. Specify the steps and provide reasons for your selection. Comment on the shape of the distribution of the data set as displayed in the histogram.
3 marks
QUESTION 3 6 marks
The researcher would like to present numerical descriptive measures to summarize the data on the two variables: weekly income and consumption given in Sheet2.
Using the relevant Excel function, prepare and display a numerical summary report for each of the two variables including measures such as mean, median, mode, range, variance, standard deviation, smallest and largest values, and the three quartiles. Also, calculate and display the coefficient of variation value for each of the two variables.
3 marks
Using the relevant Excel function, compute the correlation value of the relation between the weekly income and consumption. Display the correlation value. Then, provide an interpretation of this correlation value in terms of the relationship between the two variables.
3 marks
QUESTION 4 12 marks
Sheet 3 in the excel data file provides data on gross profit as a percentage of total sales and annual operating expenses as a percentage of total sales. You are assigned to analyse the data by estimating a linear regression analysis to predict gross profit as a percentage of total sales.
Based on the information provided above, specify the dependent variable and the independent variable for doing the regression analysis. Briefly explain the reasoning for your selection. 2 marks
Using Excel, draw a chart to evaluate the gross profit over time. Display your chart and provide a brief comment on what you can observe from the chart. 2 marks
Using Excel, draw a chart to evaluate relationship between gross profit percentages and operating expense percentages. Display your chart and provide a brief comment on what you can observe from the chart.
2 marks
Using Excel, calculate the correlation value of the relationship between the two concerned variables. Then, provide an interpretation of the correlation calculated in terms of the relationship between the two variables.
2 marks
Use Excel to estimate a simple linear regression model. Display the Excel regression summary table and write the estimated linear equation. Then, provide an interpretation of the slope coefficient estimate of the linear model and coefficient of determination.
3 marks
Predict gross profit percentages when operating expense percentages is 0.51 (51%)
1 mark
QUESTION 5 _______ 7 marks
Car manufacturers believe that electric car battery life is more than 200,000 kilometres. To test if this belief is correct, a random sample of 225 batteries is tested and their lifetime is recorded. The sample mean of lifetime was found to be 215,000 kilometres. Assume that lifetime is normally distributed with a population standard deviation of 150,000 kilometres. Conduct a hypothesis test to find out whether car manufacturers belief is true at 1% significant level. What will happen to the test statistics when sample size is increased while everything remains the same?
3.5 marks
Health research claims that the average 31 of percent Australians suffer from a chronic disease when they are over 50 years old. To test this claim, a random sample of 500 Australians were tested, and found that 170 persons suffer from a chronic disease. Can we conclude at 2.5% significance level that enough evidence exists to show that the research finding is true? What is the p-value of the test?
3.5 marks
