Hi,

I need help with accounting .I have attached the document.

assist me with question 5 only

UNITED STATES INTERNATIONAL UNIVERSITY CHANDARIA SCHOOL OF BUSINESS MASTER OF BUSINESS ADMINISTRATION ACT6010: MANAGERIAL ACCOUNTING WEEK 3 - COST ESTIMATION Question One The following cost data has been obtained from the records of Olympia Computer Systems; a firm that assembles computers. Month Number assembled January February March April May June July August September October November of 160 90 110 112 116 120 130 136 140 150 170 computers Total Costs incurred (Ksh.) 164,000 112,000 100,000 96,000 120,000 124,000 128,000 130,000 140,000 148,000 180,000 Required: (a) (b) (c) (d) (e) (f) (g) (h) (i) (j) Use Excel to create a scatter plot. Identify any outliers and explain why they are outliers. Use the regression analysis feature in Excel to generate a complete regression output. Determine the \"regression line.\" Use Excel to add the regression equation and related R 2 statistic to the scatter plot. Use Excel to draw in the \"high-low\" line, and visually confirm the line by calculating the highlow equation. Visually inspect the scatter plot and comment on which line (\"high-low\" or \"regression\") is more representative of the data points. Comment on why the two lines are different. Comment on what the R2 value means in general and what the R2 value specifically tells about the regression line developed from this data set. How confident should a manager be in using this cost equation to estimate costs at different volumes? Predict and compare cost estimates for the month of December when 182 computers were assembled using both the \"high-low\" equation and the \"regression\" equation. If you were the manager, which method of cost estimation would you use and why? QUESTION TWO 1 The following data relates to the total costs incurred in Muigai Garage in a period of 8 weeks. Week 1 2 3 4 5 6 7 8 (a) (b) (c) (d) (e) (f) (g) (h) (i) Number of cars repaired 90 100 120 150 160 220 300 340 Total Costs incurred (Ksh.) 5,200,000 6,000,000 6,200,000 3,530,000 3,850,000 4,300,000 5,870,000 7,150,000 Use Excel to create a scatter plot. Identify any outliers and explain why they are outliers. Use the regression analysis feature in Excel to generate a complete regression output. Determine the \"regression line.\" Use Excel to add the regression equation and related R 2 statistic to the scatter plot. Use Excel to draw in the \"high-low\" line, and visually confirm the line by calculating the highlow equation. Visually inspect the scatter plot and comment on which line (\"high-low\" or \"regression\") is more representative of the data points. Comment on why the two lines are different. Comment on what the R2 value means in general and what the R2 value specifically tells about the regression line developed from this data set. How confident should a manager be in using this cost equation to estimate costs at different volumes? Predict and compare cost estimates for week 9 when 12 cars are to be repaired. QUESTION THREE Limuru Company's total factory overhead costs fluctuate considerably from year to year according to the number of direct labour hours worked in its factory. These costs at high and low levels of activity for recent years are given below: Level of activity Direct labour hours Total factory overheads Low 60,000 Sh. 244,000 High 80,000 Sh. 282,000 The factory overhead costs above consist of indirect materials, rent and maintenance. The company has analyzed these costs at the 60,000 direct labour hour's level of activity and has determined that at this activity level, these costs exist in the following proportions: Sh. 90,000 100,000 54,000 244,000 Indirect materials Rent (fixed) Maintenance (mixed) For planning purposes, the company wants to break down the maintenance costs into variable and fixed cost elements. 2 Required: (a) Determine how much of the Sh. 282,000 factory overhead cost at high level of activity above consists of maintenance costs. (b) By means of the high low method of cost analysis determine the cost formula for maintenance. (c) Express the company's maintenance costs in the linear equation of the form; Y = a + bX. (d) What total overhead costs would you expect the company to incur at an operating level of 70,000 direct labour hours? QUESTION FOUR The following data relates to the last 40 months in the operation of a company. Mont h 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Total Cost Number of units produced 35761 37417 36837 37417 39073 41142 41804 41556 43874 45861 44536 42798 42549 43874 46275 48758 52152 50827 52483 53145 52235 54553 55629 57202 58278 59188 61989 61989 62176 64790 65910 67404 68151 69831 70951 74125 74686 1432 1452 1445 1452 1472 1497 1505 1502 1530 1554 1538 1517 1514 1530 1559 1589 1630 1614 1634 1642 1631 1659 1672 1691 1704 1715 1745 1753 1741 1793 1787 1820 1822 1861 1875 1919 1926 Number of machine hours 9960 10470 10480 10560 11000 11100 11170 11230 11320 11070 11940 12180 12390 12790 12680 12570 12660 13180 13600 14040 14160 14480 14180 14080 14780 15260 15930 16070 16840 17470 17360 17790 18040 17870 18100 18650 19110 3 38 39 40 73752 76179 77673 1926 1957 1977 19460 18730 19240 Required: (a) Use Excel to create a scatter plot for each of the two cost drivers and identify any outliers. Explain why you deem certain points to be outliers while others are not. (b) Use Excel to generate the \"regression line\" for each of the two cost drivers. (c) Use Excel to add the regression equation and related R 2 statistic to the scatter plots. (d) Use Excel to draw in the \"high-low\" line, and visually confirm the line by calculating the highlow equation for both cost drivers. (e) Comment on what the R2 value means in general and what the R 2 value specifically tells about the regression lines developed from this data set. How confident should a manager be in using these cost equation to estimate costs at different volumes? (f) Predict and compare cost estimates for the 43 rd month when 2000 units will be produced and 19205 machine hours used up in production by use of both the \"high-low\" equation and the \"regression\" equation. (g) If you were the manager, which cost driver would you use for making cost estimates and why? (h) Using the data set given, demonstrate how multiple regression can provide a better estimate for the future costs. QUESTION FIVE Tupeleke Vans is a distribution business, operating a fleet of five vans, all of which run on lead free petrol. The practical capacity of the business is 12000 hours per year, which is equivalent to 185,500 kilometers. However a practical capacity usage is never 100%. For 60% of the capacity usage is 90%, for the remainder of the time, capacity usage is 50%. Tupeleke Vans employs five fulltime drivers. Typical costs on annual basis are given below: Driver's wages (per driver) Driver expenses (per driver including accommodation and meals) - paid by the company Depreciation cost per vehicle Maintenance cost per vehicle General administrative costs License fee per vehicle Insurance cost per vehicle Tyres per van for every 30000 KM Lead free petrol (fuel costs) Spares and other costs per vehicle Average kilometers per liter $3,834 $3,578 $3,284 $714 $37,760 $140 $410 $400 per set $0.40 per litre $20 per 1000 KM 4 General administrative costs are absorbed in to the cost of jobs at 20% of total costs (other than general administration costs). 4 Required: (a) Briefly describe the cost units that are appropriate for a transport business (b) Draw a graph which shows the total cost per kilometer as practical capacity usage increases from 50% to 100%. (Plot costs at 9,000 kilometer intervals). (c) For a job that requires one vehicle to drive 100 kilometers, calculate the total and variable costs that would be charged to the job. 5