Instruction-
1-First make sure if you have "Data Analysis" under "Data" tab in your Excel. If you do not have it, go to "file" then "Options" choose "add-ins" then press "Go...'' button, tick the check box of "analysis toolpak" and "OK" . So you will have the "Data Analysis" under "Data".
2-Find the "Question" worksheet, read the question. Each student will have one set of unique data by entering the "student number" in the provided box.
3-After entering your student number in the box and having your data ready, you need to use "Regression" in "Data Analysis" to prepare Excel Regression output for scenario(a and b). Make sure that you use the (Y output) for "set-up Cost" column and (X output ) for cases "a" and "b" ,so you will have two regression outputs.Tick the "Labels" box if you select headings.
4-Prepare Scatter plot for both scenarios(a and b) and the trendlines (requirement 2). If you do not know how, use the following link for help;
https://www.online-tech-tips.com/ms-office-tips/add-a-linear-regression-trendline-to-an-excel-scatter-plot/
5- Compare two scenarios (a and b) and complete your assignment by a conclusion and recommendation for the company to choose "number set-ups" or "number of set-up hours" as a cost driver.(requirement 3)
6- Once you have all requirements ready, put them all in a Word file and upload it in "Moodle" and "turnitin" both. Instruction has been emailed to you by Barbara and you can find it as follows;
Assignment Question-
"Dilbert Toys (DT) makes the popular Floppin' Freddy Frog and Jumpin ‘ Jill Junebug doll in batches. DT incurs set-up costs for each batch 0f dolls that it produces. DT uses ‘number of set-ups' as the cost driver for set-up costs.
DT has just hired Bec Williams, an accountant. Bec thinks that ‘number of set-up hours' might be better cost driver because the set-up time for each product is different. Bec collects the following data: (you should enter your student number(without letter)in the following box to get your own data to work on. you will get zero mark if you use the current numbers in the following table)"
| Month |
Number of set-ups |
Number of set-ups hours |
Set-up costs |
| 1 |
300 |
1840 |
104600 |
| 2 |
410 |
2680 |
126700 |
| 3 |
150 |
1160 |
57480 |
| 4 |
480 |
3800 |
236840 |
| 5 |
310 |
3680 |
178880 |
| 6 |
460 |
3900 |
209620 |
| 7 |
420 |
2980 |
209620 |
| 8 |
300 |
1200 |
90080 |
| 9 |
270 |
3280 |
221040 |
"Required:
1- Using Excel, estimate the regression equation and provide the regression output for:
a- Set-up costs and Number of set-ups
b- Set-up costs and Number of set-up hours
2- On two different graphs plot the data and the regression lines for Question 1 (a & b).
3- Evaluate the regression models (a and b) as the cost driver and based on analysis, advise which cost driver (number of set-ups/number of set-up hours) is better and should be used by Dilbert Toys and why?"