1. Data Cleaning: There are several lines on the data provided by Kofi that have missing, duplicated and/or invalid data. Before starting your analyses, take care of those items and document the actions you took in your final memo (see requirement 6).

2. Create a Pivot Table where you summarize by client size:

a. Actual Overhead

b. Allocated overhead

c. Total number of rooms

d. Total direct materials costs

e. The dollar difference in actual versus allocated overhead costs,

f. The percentage differences in actual versus allocated overhead costs.

3. Alternative allocation bases:

a. Referring to chapter 3, create two regression analyses to determine if another allocation base would result in better allocations. Total overhead is the dependent variable. Run regressions for:

i. Total number of rooms; and

ii. Total direct material costs

b. Create overhead allocation rates based on the two alternative allocation bases above. Assume total budgeted overhead is $2,000,000 for the year.

4. Apply the overhead

a. Determine which overhead rate calculated in 3 above should be used. Use this rate to:

i. Apply overhead by client size class using the data you created in the pivot table in requirement 2 above.

ii. Compare the new overhead applied to actual overhead. Create a column with the difference between this method and the former applied overhead by client size.

iii. Calculate the percentage difference between the new applied overhead and the actual overhead by client size.

5. Create visualizations to display your work by client size.

a. Create graphs or charts to show

i. The original applied overhead and the actual overhead;

ii. The original applied overhead, the actual overhead, and the new method for applying overhead.

6. Conclusion: Write a memo to management explaining

a. The scope of your work

b. Which method you suggest management employ for overhead allocations and why.

Job CharacteristicsDirect CostsActual Overhead Incurred by JobTotal Summary Actual CostsNormal Costing System InformationJob No.Client Size

Number of rooms

Average Sq. feet per room

Total Square feet

Total Direct Material

Total Direct Labor

Total Indirect Labor

Total Indirect Material

Total Equipment

Other SupportTotal

Total Actual Job Cost

Cost per room

Cost per Sq. feet

Allocated overhead

OVH Normal vs. Actual

% Difference

Total Job Cost

Total Normal vs. Actual

% Difference

CP305Medium25232581,900409,50028,2245,241.62,77223,6882,84734,548.6472,272.61,453.1465.76620,475-14,073.6-40.74%458,199-14,073.6-2.98%CP306Large356420149,520747,60064,0809,006.82,49227,0566,98345,537.8857,217.82,040.9955.73337,380-8,157.8-17.91%849,060-8,157.8-0.95%CP307Large314389122,146610,73052,7527,410.45,02425,7485,62743,809.4707,291.41,818.235.79130,536.5-13,272.9-30.30%694,018.5-13,272.9-1.88%CP308Medium26533789,305178,61037,8954,014.753,18020,9352,52530,654.75247,159.75733.4122.7688,930.5-21,724.25-70.87%225,435.5-21,724.25-8.79%CP309Large356441156,996784,98085,44010,786.86,40827,4126,64651,252.8921,672.82,089.9615.87139,249-12,003.8-23.42%909,669-12,003.8-1.30%CP310Medium29932697,47497,47435,8804,584.6672,99025,4154,92737,916.667171,270.667525.371.7574,873.7-33,042.967-87.15%138,227.7-33,042.967-19.29%CP311Medium245477116,865350,59531,3606,206.6673,43018,6204,25832,514.667414,469.667868.9093.54717,529.75-14,984.917-46.09%399,484.75-14,984.917-3.62%CP312Large379387146,673733,36549,2706,215.63,03229,5627,27446,083.6828,718.62,141.3925.6536,668.25-9,415.35-20.43%819,303.25-9,415.35-1.14%CP313Medium17437965,946197,83832,5381,7402,95813,9204,69723,315253,691669.3693.8479,891.9-13,423.1-57.57%240,267.9-13,423.1-5.29%CP314Medium20137074,370371,85036,1802,4122,01017,0854,67526,182434,2121,173.5465.83918,592.5-7,589.5-28.99%426,622.5-7,589.5-1.75%CP315Medium21438281,748326,99228,8902,667.8672,35420,9723,07029,063.867384,945.8671,007.7124.70916,349.6-12,714.267-43.75%372,231.6-12,714.267-3.30%CP316Resort436480209,2802,511,360132,54414,707.7337,41238,8049,20670,129.7332,714,033.7335,654.23712.968125,56855,438.26779.05%2,769,47255,438.2672.04%CP31666Resort436480209,2802,511,360132,54414,707.7337,41238,8049,20670,129.7332,714,033.7335,654.23712.968125,56855,438.26779.05%2,769,47255,438.2672.04%CP317Medium21441187,954263,86238,5204,565.3333,42417,7623,39129,142.333331,524.333806.6293.76913,193.1-15,949.233-54.73%315,575.1-15,949.233-4.81%CP318Medium280416116,480582,40063,8409,6324,20021,5603,30738,699684,9391,646.4885.8829,120-9,579-24.75%675,360-9,579-1.40%CP319Medium17246980,668403,34034,0564,4721,54813,4164,44123,877461,273983.5255.71820,167-3,710-15.54%457,563-3,710-0.80%CP32010047147,10094,20019,8001,9501,2009,0001,79713,947127,947271.652.7164,710-9,237-66.23%118,710-9,237-7.22%CP321Large350367128,450770,70067,2006,737.52,45027,3006,20042,687.5880,587.52,399.4216.85538,535-4,152.5-9.73%876,435-4,152.5-0.47%CP322Resort421465195,7651,566,120151,5609,542.6675,89434,9438,75759,136.6671,776,816.6673,821.1119.07678,30619,169.33332.42%1,795,98619,169.3331.08%CP323Large380492186,960934,80082,08013,3003,04033,4406,46056,2401,073,1202,181.1385.7446,740-9,500-16.89%1,063,620-9,500-0.89%CP324Resort416435180,9601,447,68099,84013,228.84,57639,1048,66565,573.81,613,093.83,708.2628.91472,3846,810.210.39%1,619,9046,810.20.42%CP325Small9837136,35872,71615,0921,509.21,2748,4281,08312,294.2100,102.2269.8172.7533,635.8-8,658.4-70.43%91,443.8-8,658.4-8.65%CP326Medium27032888,560265,68038,8804,603.52,43020,7902,67130,494.5335,054.51,021.5083.78313,284-17,210.5-56.44%317,844-17,210.5-5.14%CP327Medium235497116,795233,59049,1157,911.6673,05518,8003,28333,049.667315,754.667635.3212.70311,679.5-21,370.167-64.66%294,384.5-21,370.167-6.77%CP328Small10641443,884175,53625,4404,324.81,1668,4802,50016,470.8217,446.8525.2344.9558,776.8-7,694-46.71%209,752.8-7,694-3.54%CP329Small10247648,552145,65618,3602,9581,4288,3641,04913,799177,815373.5613.6627,282.8-6,516.2-47.22%171,298.8-6,516.2-3.66%CP330Large350448156,800940,80089,25011,7602,45034,3005,39253,9021,083,9522,419.5366.91347,040-6,862-12.73%1,077,090-6,862-0.63%CP331Large342460157,320786,60049,2487,4104,44633,5166,68552,057887,9051,930.2285.64439,330-12,727-24.45%875,178-12,727-1.43%CP332Small10543745,885229,42521,0002,52084010,1852,24015,785266,210609.1765.80211,471.25-4,313.75-27.33%261,896.25-4,313.75-1.62%CP333Resort425480204,0002,244,000153,0008,953.3337,22539,9509,33165,459.3332,462,459.3335,130.12412.071112,20046,740.66771.40%2,509,20046,740.6671.90%CP334Small15046469,600278,40027,0003,8701,65013,8001,24220,562325,962702.5044.68313,920-6,642-32.30%319,320-6,642-2.04%CP335Medium300362108,600434,40042,9003,6802,70027,6002,95736,937514,2371,420.5444.73521,720-15,217-41.20%499,020-15,217-2.96%CP336Medium22541693,600374,40038,2503,6003,82517,5502,54527,520440,1701,058.1014.70318,720-8,800-31.98%431,370-8,800-2.00%CP337Medium290380110,200110,20048,7204,4664,64027,8404,57541,521200,441527.4761.8195,510-36,011-86.73%164,430-36,011-17.97%CP338Small10549551,975207,90016,8002,047.51,2607,9802,10913,396.5238,096.5481.0034.58110,395-3,001.5-22.41%235,095-3,001.5-1.26%CP339Medium300388116,400232,80032,4002,7004,50026,7004,38938,289303,489782.1882.60711,640-26,649-69.60%276,840-26,649-8.78%CP340Small15039759,550238,20020,2502,3701,20011,4002,41817,388275,838694.8064.63211,910-5,478-31.50%270,360-5,478-1.99%CP341Medium19034264,980259,92035,5302,251.53,23014,2504,98724,718.5320,168.5936.1654.92712,996-11,722.5-47.42%308,446-11,722.5-3.66%CP342Medium18542879,180237,54037,7403,1082,22014,9853,34923,662298,942698.4633.77511,877-11,785-49.81%287,157-11,785-3.94%CP343Resort421375157,8751,263,00093,0413,929.3334,21037,4699,43255,040.3331,411,081.3333,762.8848.93863,1508,109.66714.73%1,419,1918,109.6670.57%CP344Large380358136,040680,20051,3005,3015,32035,3405,48151,442782,9422,186.9895.75534,010-17,432-33.89%765,510-17,432-2.23%CP345Resort416328136,4481,091,58481,5366,489.67,07239,5208,83061,911.61,235,031.63,765.349.05154,579.2-7,332.4-11.84%1,227,699.2-7,332.4-0.59%CP346Small10332533,47566,95013,3901,189.651,8549,0641,88313,990.6594,330.65290.2482.8183,347.5-10,643.15-76.07%83,687.5-10,643.15-11.28%CP347Medium277469129,913129,91344,3208,790.1334,98621,0524,19239,020.133213,253.133454.6981.6426,495.65-32,524.483-83.35%180,728.65-32,524.483-15.25%CP348Large351477167,4271,004,562120,04211,044.85,61629,1336,91852,711.81,177,315.82,468.1677.03250,228.1-2,483.7-4.71%1,174,832.1-2,483.7-0.21%CP349Medium233452105,316315,94855,9205,778.42,09722,3683,42633,669.4405,537.4897.2073.85115,797.4-17,872-53.08%387,665.4-17,872-4.41%CP350Large395415163,925819,62567,5459,269.3333,16033,9705,79052,189.333939,359.3332,263.5165.7340,981.25-11,208.083-21.48%928,151.25-11,208.083-1.19%CP351Medium16445374,292297,16830,6686,193.7331,96816,2363,87928,276.733356,112.733786.1214.79314,858.4-13,418.333-47.45%342,694.4-13,418.333-3.77%CP352Medium20733769,759348,79531,0504,802.43,10518,6303,77630,313.4410,158.41,217.0875.8817,439.75-12,873.65-42.47%397,284.75-12,873.65-3.14%CP353Medium262467122,354611,77049,7806,838.23,14420,4363,41633,834.2695,384.21,489.0455.68330,588.5-3,245.7-9.59%692,138.5-3,245.7-0.47%CP354Large386375144,750723,75055,5844,953.6676,56232,4246,81750,756.667830,090.6672,213.5755.73536,187.5-14,569.167-28.70%815,521.5-14,569.167-1.76%CP355Large309352108,768543,84029,6643,986.13,70824,7206,99839,412.1612,916.11,741.2395.63527,192-12,220.1-31.01%600,696-12,220.1-1.99%CP356Medium276397109,572109,57232,2923,569.64,14020,9764,12832,813.6174,677.6439.9941.5945,478.6-27,335-83.30%147,342.6-27,335-15.65%CP357Large345362124,890624,45049,6804,088.256,21026,9105,44942,657.25716,787.251,980.0755.73931,222.5-11,434.75-26.81%705,352.5-11,434.75-1.60%C99999000CP358Resort425434184,4501,475,60096,90011,9004,25041,6508,69266,4921,638,9923,776.4798.88673,7807,28810.96%1,646,2807,2880.44%CP359Medium235498117,030117,03037,6006,454.6672,82022,3254,74336,342.667190,972.667383.4791.6325,851.5-30,491.167-83.90%160,481.5-30,491.167-15.97%CP360Large399382152,418762,09061,4464,249.355,98533,5165,15248,902.35872,438.352,283.875.72438,104.5-10,797.85-22.08%861,640.5-10,797.85-1.24%CP361Medium16349680,84880,84832,2745,183.41,14112,2253,58422,133.4135,255.4272.6921.6734,042.4-18,091-81.74%117,164.4-18,091-13.38%CP362Medium20335371,65971,65931,2622,679.62,63920,0974,68030,095.6133,016.6376.8181.8563,582.95-26,512.65-88.09%106,503.95-26,512.65-19.93%CP363Small11238242,784128,35217,1361,6801,4568,5121,15012,798158,286414.3613.76,417.6-6,380.4-49.85%151,905.6-6,380.4-4.03%CP364Small7542431,80031,80013,5002,777.55255,8501,72110,873.556,173.5132.4851.7661,590-9,283.5-85.38%46,890-9,283.5-16.53%CP365Resort3,000400,000,0007001,50002,200035352,235351.59%CP366Large350328114,800688,800100,8003,3603,50030,4505,34242,652832,2522,537.3547.2534,440-8,212-19.25%824,040-8,212-0.99%0Totals19,675N/AN/A35,351,1253,261,698357,683.133212,8131,432,337293,2432,296,076.13340,908,899.133N/AN/A1,767,556.25-528,519.883N/A40,380,379.25-528,519.883N/AAverage (Jobs)312.302411.9526,458,074.159561,128.96851,772.9845,769.0833,432.46823,102.214,729.72636,445.653649,347.6051,580.6165.12728,056.448-8,258.123-33.94%630,943.426-8,258.123-4.24%Notes:Total Budgeted Cost Pool2,000,000Kofi uses a normal costing system to account for its operations. Overhead is applied at a rate of 5% of direct materials costs.Client size is based number of rooms as follows:Small:less than 150 roomsMedium:between 150 room s and 300 roomsLarge:between 300 rooms and 400 roomsResort:more than 400 rooms