Using the Assignment Tools to Complete This Exam The tools for working with this exam are found in the Assignment menu. The Instruction Sheet icon displays all tasks for the exam at the same time in your Internet browser. The Submit icon initiates the grading process. Pay attention to any alerts that are displayed. The Task Guide icon displays each of the exam tas one at a time, in a floating window in Excel. The View Report icon displays your feedback from your last submission. s Exam displays each of the exam tasks, n a floating window in Excel. on displays your feedback from last submission. You have decided to purchase a new car. You plan to take out a loan to pay for the car. The loan amount is $18,900. You will pay an interest rate of 6.5% and plan to pay off the car in 48 months. You want to calculate the total amount of money and the total amount of interest you will pay over the course of the loan. To help you with these calculations, you decide to construct an amortization table. An amortization table provides details about the payment, interest amount, principal amount, and loan balance for every month of the loan. Perform the tasks below to complete the amortization table and loan summary calculations. Loan Amount Term (months) Interest Rate -$18,900.00 48 6.50% Payment $1,291.34 Period 0 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 Loan Summary Total Amount Paid Total Interest Paid SUM Check Calculation Buy new in Period 36? Payment Interest Principal $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 Balance $18,900.00 ### 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $1,291.34 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $0.07 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 $83.94 Four J's Painting is a home painting contractor that completes interior painting w employees: Jim, Jill, Jake, and Jennifer. You have been hired to determine how the tracked information on the last 20 painting projects in table 1. Employees receive require additional work to correct flaws (touch-up work). Information about the p summarizes information about the employees. Table 4 categorizes the projects ac to more efficiently and effectively run the company. Complete each task so that t columns of the tables. Do not name your lookup tables; rather, you should keep t Employee Hourly Wage Employee Wage Information Jim Jill $52 $50 Jake $47 Table 1 Job ID 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Job ID 5 8 11 12 13 16 Estimated Hours 50 40 80 40 80 40 50 40 50 40 70 50 70 60 70 50 50 60 60 80 Employee Jim Jill Jim Jake Jim Jake Jim Jennifer Jim Jill Jake Jennifer Jennifer Jake Jake Jill Jennifer Jill Jill Jennifer Table 2 - Touchup Projects Employee Difficulty Hours Worked 54 40 86 42 78 38 51 36 48 42 74 51 70 58 66 48 47 65 61 79 Cost $400 $600 $300 $700 $500 $250 Hourly Wage Difficulty 1 5 4 1 3 3 3 3 3 2 2 2 2 2 3 1 1 2 2 3 Employee Jim Jill Jake Jennifer Difficulty 20 $500 1 2 3 4 5 mpletes interior painting work for new construction projects. Four J's Painting has four hired to determine how the company can be more efficient and provide better service. You have table 1. Employees receive bonuses for efficient and high-quality work. Occasionally, some jobs k). Information about the projects that required touch-up work is stored in table 2. Table 3 categorizes the projects according to difficulty. Complete tables 1 through 4 to determine how omplete each task so that the formulas you use can be copied and pasted down to complete the s; rather, you should keep them as references (e.g. B16:C30). Bonus Information Time 10% Outcome 10% Jennifer $48 Table 1 - Painting Projects Quality Time Bonus? Outcome Bonus? 3 2 3 2 1 3 3 1 2 2 1 1 1 2 3 1 2 3 3 1 # of Jobs # of Jobs Total Hours Total Pay Job Pay $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Time Bonus $ Outcome Bonus $ Table 3 - Employee Summary Total Pay # of Touch-ups Cost Touch-ups Average Cost/Job 1 $400 #DIV/0! 1 $250 #DIV/0! 1 $300 #DIV/0! 4 $2,300 #DIV/0! Table 4 - Project Summary by Difficulty # of Touch-ups Cost Touch-ups Total Cost Average Cost/Job 4 7 7 1 1 $9,944 $21,315 $21,104 $4,919 $2,200 $9,944 $21,315 $21,104 $4,919 $2,200 $2,486 $3,045 $3,015 $4,919 $2,200 Total Pay $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 True Cost/Hour #DIV/0! #DIV/0! #DIV/0! #DIV/0! Average Hours/Job Comments Review the following two tables that detail the revenue for Google, Inc. from 2003 to 2010. Complete the tasks construct a column, stacked column, pie, and line chart and to discover patterns or trends in revenue across the years. Table 1. Google, Inc. Revenue by Quarter (in millions of dollars) Quarter 2003 2004 2005 Total Q1 Revenue $249 $652 $1,257 Total Q2 Revenue $311 $700 $1,384 Total Q3 Revenue $394 $806 $1,578 Total Q4 Revenue $512 $1,032 $1,919 Total Annual Revenue $1,466 $3,190 $6,138 2006 $2,254 $2,456 $2,690 $3,205 $10,605 2007 $3,664 $3,872 $4,232 $4,827 $16,595 2008 $5,186 $5,367 $5,542 $5,701 $21,796 Table 2. Google, Inc. Revenue by Business Segment (in millions of dollars) Segment 2003 2004 2005 2006 Google Web Sites $792 $1,590 $3,377 $6,332 Google Network Web Sites $628 $1,554 $2,688 $4,160 Other Revenues $46 $46 $73 $113 Total Annual Revenue 2 $1,466 $3,190 $6,138 $10,605 2007 $10,625 $5,788 $182 $16,595 2008 $14,414 $6,715 $667 $21,796 010. Complete the tasks to nds in revenue across the eight 2009 $5,509 $5,523 $5,945 $6,674 $23,651 2010 $6,775 $6,820 $7,286 $8,440 $29,321 2009 $15,723 $7,166 $762 $23,651 2010 $19,443 $8,793 $1,085 $29,321 You have created a worksheet (\"Spending\") to help you track your spending for the month of August. You are ab website, but the information is not presented in a clear way. The transaction information includes a time stamp merchant ID, and amount for each of the 63 transactions you made in August. You have decided to use what yo the information you received from your bank and make it easier to read and understand. Complete the table be table, you will create a statement about each transaction. This statement follows the format of the template in field of the table, use the functions suggested in each of the task descriptions. Template Length Position of "#" Position of "$" Statement P1 Statement P2 Statement P3 Time Stamp 40756.788588 40757.455930 40757.629305 40757.866335 40758.693496 40759.640262 40759.708581 40759.777827 40760.749738 40761.346613 40763.369599 40763.377290 40763.600053 40763.698378 40764.409008 40765.505280 40765.890658 40765.910262 40766.751562 40766.841718 40767.456824 40767.481102 40767.539629 40767.559364 40767.615509 40767.658124 40767.677884 40768.460862 40768.799808 40770.371531 40770.429205 Merchant ID 4931 4329 8103 8234 4556 4277 4277 6158 4931 4670 6132 4931 4670 4670 7927 7927 4277 5031 4172 5056 6329 4329 6132 8103 4205 5031 6329 5031 8234 4556 7927 I spent $ at merchant # on: Amount $24.06 $26.99 $24.25 $9.58 $16.59 $22.08 $23.06 $23.00 $15.81 $6.81 $16.80 $5.10 $16.48 $19.96 $8.54 $4.87 $18.70 $3.29 $21.10 $16.46 $22.80 $28.40 $8.17 $6.43 $17.32 $16.62 $24.32 $28.16 $5.95 $12.51 $24.86 Month Day Hour 40770.438833 40770.784586 40771.591442 40771.670240 40773.460275 40773.565961 40773.880570 40774.473899 40774.582839 40775.492153 40775.531251 40775.543907 40775.780794 40777.731126 40778.512033 40778.589545 40778.841680 40779.526600 40781.681088 40781.717628 40782.598417 40782.740048 40784.351818 40784.442599 40784.882174 40784.910421 40785.616307 40785.830413 40786.461586 40786.675453 40786.683317 40786.829942 40786.832489 8234 8234 8520 4205 5031 4931 4205 4172 8520 4556 4556 4670 6329 8103 6158 4556 7927 8234 4329 4205 5056 5056 6132 4172 4931 7927 4172 4931 4172 4205 7927 8234 8103 $25.00 $23.10 $13.52 $8.84 $13.15 $25.66 $14.54 $20.18 $28.14 $14.99 $25.56 $25.52 $23.98 $19.11 $7.53 $5.10 $6.44 $24.26 $24.13 $12.07 $22.83 $4.14 $10.62 $8.02 $9.00 $24.38 $13.71 $22.14 $23.59 $17.54 $18.98 $28.87 $4.94 onth of August. You are able to download your debit card transactions from your bank's tion includes a time stamp (a number that the bank associates with a specific date and time), ve decided to use what you know about how Excel handles dates, times, and text to reformat nd. Complete the table below by performing each of the tasks. In the rightmost column of the format of the template in cell D9. Because there may be more than one way to complete each Minute Second Date Transaction Statement Product Type Cost of Goods Sold (COGS) Commission Fixed Costs Employees (% of Expected Profit) Salary (per employee) Arena Fee Model Inputs Programs 40% 20% $5,000.00 0.05% $50.00 $100,000.00 Food 60% 10% $10,000.00 0.20% Merchandise 70% 5% $20,000.00 0.03% Event Profitability Analysis Product Type Gross Profit Programs Food Merchandise Operating Expenses Programs Food Merchandise Sales Cost of Goods Sold (COGS) Gross Profit Product Type Salary Expenses Commissions Fixed Costs Total Operating Expenses Profit before Arena Fee Number of Employees Product Type Sales Gross Profit Total Operating Expenses Profit before Arena Fee Arena Fee Net Profit Profit Summary Total Input Analysis 100 # of Events Statistic Attendance Programs Food Food Merchandise Food Merchandise Merchandise Average Standard Deviation 95% Confidence Interval Product Type Correlation with Attendance Programs Attendance 18000 Product Type Sales Forecast Upper Limit for Sales Lower Limit for Sales Programs What-if Analysis Arena Fee 15000 Attendance Forecast 18000 21000 $50,000.00 $75,000.00 $100,000.00 $125,000.00 $150,000.00 $175,000.00 $200,000.00 Questions Question 1: The sales for which product type are most highly correlated with attendance? Question 2: How likely is it that food sales would exceed $220,000 if attendance is 18000? Question 3: Which product generates the largest gross profit? Question 4: Which product will require the fewest employees? Question 5: Would the event be profitable with a 150,000 arena fee if 15000 people were in attendance? You are the owner of a small business that manages the program, food, and merchandise sales at the Excel Center, where the local professional basketball team plays. In addition to basketball games, the Excel Center hosts dozens of events a year. Your company handles all sales made inside the arena. There are three major products you sell: programs, food, and merchandise. You have decided to build a spreadsheet model to help you analyze and project the profitability of future events. The \"Event Profit\" worksheet contains this model. You have randomly selected sales and attendance data from a number of past events to help build your model. This data can be found on the \"Past Events\" worksheet. You will analyze this data in order to obtain appropriate values to develop the model. Complete the tasks to finish the model. Whenever possible, reference cells rather than hard-coding values in the cells of your worksheet. Randomly Selected Past Events Event 1123 1125 1214 1217 1322 1416 1419 1621 1713 1716 1723 1919 2119 2317 2321 2322 2416 2418 2419 2523 2524 2527 2615 2620 2621 2919 2921 2924 3121 3216 3920 3922 4122 4416 4422 4520 4719 4819 4820 4919 5114 5119 Crowd Size Attendance Fair 11055 Good 19689 Good 16657 Fair 11383 Good 15334 Fair 12081 Excellent 20082 Fair 10643 Good 18421 Good 18485 Good 17012 Good 16884 Fair 14350 Good 15901 Poor 8789 Fair 13629 Fair 13331 Poor 8569 Excellent 21016 Fair 13281 Excellent 21538 Fair 12465 Excellent 20611 Excellent 20145 Poor 8800 Excellent 20999 Good 15443 Fair 10848 Good 19953 Good 18903 Poor 9074 Excellent 20498 Fair 11573 Excellent 21249 Fair 11257 Good 19235 Fair 13275 Fair 13378 Good 18449 Fair 13725 Good 15710 Fair 11382 Programs $28,190 $168,538 $74,457 $32,100 $102,584 $70,191 $106,836 $39,486 $128,947 $63,219 $35,045 $29,040 $35,014 $34,187 $63,544 $59,968 $95,583 $35,047 $56,323 $23,109 $119,536 $79,153 $176,430 $58,018 $30,096 $97,645 $123,390 $67,149 $91,983 $147,632 $34,390 $53,705 $87,608 $170,417 $99,624 $25,006 $19,382 $13,512 $44,093 $80,291 $41,946 $61,235 Food $76,722 $113,409 $239,861 $181,217 $98,598 $68,620 $246,808 $139,210 $223,078 $187,992 $204,484 $128,318 $72,181 $221,183 $131,659 $211,250 $161,172 $106,084 $305,362 $113,951 $129,228 $142,101 $166,125 $234,689 $87,032 $223,639 $237,822 $172,483 $265,774 $157,840 $123,769 $304,190 $63,420 $320,010 $150,394 $181,194 $96,775 $206,423 $216,407 $71,233 $217,426 $127,934 Merchandise $146,037 $499,116 $351,629 $271,826 $618,267 $407,975 $561,292 $448,070 $615,814 $251,396 $137,967 $695,621 $569,552 $244,557 $103,798 $457,253 $419,527 $117,310 $774,860 $454,210 $987,087 $121,160 $581,642 $283,843 $292,336 $893,927 $177,749 $72,356 $906,265 $504,710 $288,281 $565,335 $213,175 $127,919 $413,132 $483,760 $300,679 $526,424 $126,007 $199,562 $238,321 $195,087 Type Sporting Event Sporting Event Sporting Event Sporting Event Concert Sporting Event Sporting Event Concert Sporting Event Sporting Event Other Event Concert Concert Sporting Event Other Event Sporting Event Sporting Event Sporting Event Concert Sporting Event Concert Other Event Sporting Event Sporting Event Sporting Event Concert Other Event Other Event Concert Sporting Event Sporting Event Sporting Event Sporting Event Other Event Sporting Event Sporting Event Sporting Event Concert Other Event Sporting Event Sporting Event Sporting Event 5125 5218 5223 5317 5323 5425 5520 5619 5620 5714 5720 5821 5916 5927 6216 6423 6522 6619 6625 6627 6712 6722 6820 6821 6917 7120 7215 7216 7320 7618 7714 7821 7822 7919 8213 8216 8220 8221 8223 8319 8418 8523 8524 8815 8823 Fair Fair Good Fair Excellent Good Good Good Good Fair Poor Fair Good Fair Poor Fair Fair Poor Excellent Fair Good Fair Poor Fair Fair Fair Good Good Fair Good Good Fair Good Fair Fair Good Good Poor Good Fair Good Fair Excellent Fair Fair 12437 14828 18468 13952 20051 19744 19205 16309 15234 14435 9173 14906 19484 10847 8675 11434 11306 8830 21934 11879 19774 10582 8627 13029 13004 12217 18696 16938 13861 15925 18782 12453 19820 14174 11515 16560 16116 9469 17639 12817 17723 13497 21799 13596 11375 $18,656 $46,856 $71,102 $120,406 $120,707 $138,405 $129,826 $62,953 $116,845 $80,980 $41,737 $71,698 $77,741 $27,551 $37,476 $48,709 $64,105 $31,347 $196,529 $95,270 $152,655 $10,899 $25,191 $88,467 $54,617 $57,176 $144,707 $18,462 $25,920 $20,225 $30,051 $60,646 $106,235 $69,169 $48,593 $129,830 $100,564 $57,761 $56,092 $21,789 $60,967 $91,375 $66,051 $52,073 $43,111 $125,241 $84,520 $117,641 $113,011 $201,312 $154,990 $273,863 $137,159 $117,149 $200,502 $63,477 $86,008 $198,932 $82,871 $131,947 $74,321 $62,296 $87,770 $133,578 $90,874 $137,232 $84,339 $99,987 $112,571 $207,804 $157,721 $288,479 $124,325 $188,371 $102,398 $247,735 $109,213 $121,695 $215,445 $75,193 $136,289 $176,309 $114,291 $135,997 $122,787 $97,122 $70,859 $268,346 $183,002 $81,900 $126,236 $661,625 $378,963 $602,308 $555,012 $560,927 $701,367 $492,043 $609,512 $467,550 $309,680 $498,606 $812,678 $285,276 $339,193 $72,949 $77,785 $175,276 $911,138 $149,913 $817,062 $176,931 $352,499 $488,718 $81,145 $538,037 $364,198 $364,675 $426,503 $162,913 $389,727 $462,878 $696,277 $84,335 $70,357 $620,669 $488,798 $181,994 $543,458 $420,526 $97,831 $491,291 $152,811 $525,078 $282,783 Other Event Concert Sporting Event Concert Sporting Event Sporting Event Sporting Event Sporting Event Concert Sporting Event Sporting Event Sporting Event Concert Sporting Event Concert Other Event Other Event Sporting Event Concert Other Event Concert Sporting Event Concert Concert Other Event Concert Sporting Event Sporting Event Sporting Event Other Event Sporting Event Concert Sporting Event Other Event Other Event Concert Sporting Event Sporting Event Sporting Event Sporting Event Other Event Sporting Event Other Event Concert Sporting Event 8914 8916 8921 9127 9220 9421 9423 9515 9520 9717 9720 9818 9922 Excellent Good Good Poor Good Fair Good Good Good Poor Poor Fair Poor 21663 18469 16811 8414 15448 13303 17784 19791 18932 9893 8276 11471 8100 $158,140 $83,665 $125,746 $53,261 $105,973 $64,520 $157,744 $48,290 $67,587 $40,363 $23,504 $71,694 $51,516 $197,567 $197,249 $96,495 $113,673 $189,701 $89,396 $252,355 $252,929 $286,820 $77,363 $59,835 $163,462 $123,444 $872,152 $472,437 $383,795 $162,306 $194,645 $112,942 $726,832 $390,872 $112,077 $387,113 $279,067 $166,674 $360,774 Concert Sporting Event Sporting Event Sporting Event Other Event Other Event Concert Sporting Event Other Event Concert Sporting Event Sporting Event Concert You are the owner of a small business that manages the program, food, and merchandise sales at the Excel Center, where the local professional basketball team plays. In addition to basketball games, the Excel Center hosts dozens of events a year. Your company handles all sales inside the arena. There are three major products you sell: programs, food, and merchandise. You have randomly selected sales and attendance data from a number of past events to help build your model. This data can be found on the \"Past Events\" worksheet. You have classified each event as a \"Sporting Event,\" \"Concert,\" or \"Other Event.\" You have also classified each event by the attendance at that event as \"Poor\" (less than 10,000 people in attendance), \"Fair\" (between 10,000 and 15,000 people in attendance), \"Good\" (between 15,000 and 20,000 people in attendance), or \"Excellent\" (more than 20,000 people in attendance). Complete the tasks to build each of the PivotTables described. Make sure you create a new PivotTable when a task asks you to construct a PivotTable; place that PivotTable on an appropriately named worksheet. For each question, select the answer from the drop-down list that is closest to the answer you found in your analysis. PivotTable Questions Question 1 - Which type of event (\"Concert,\" \"Sporting Event,\" or \"Other Event\") has the largest average attendance? Question 2 - For only those events with \"Excellent\" crowd sizes, how much total merchandise revenue was earned? Question 3 - What percent of events with \"Fair\" crowd sizes were sporting events? Question 4 - What percent of all concerts had a crowd size of \"Excellent\"? Question 5 - How much more money from food sales has been generated at concerts than at other events, not including sporting events, for events in the sample? se sales at the all games, the ena. There are elected sales and be found on the t,\" or \"Other ess than 10,000 d\" (between ttendance). new PivotTable ly named to the answer Your bakery makes a variety of products: cookies, cupcakes, muffins, and donuts. You want to maximize your profit by optimizing the mix of products you make. You cannot use any more materials or labor than is available, nor do you want to make more goods than the amount that is demanded because the excess would go to waste. You only make batches of a complete dozen, so make sure the changing cells are integers. Use Solver to calculate the optimal number of dozens of product to make. Produced in dozens Demand in dozens Cookies Cupcakes Muffins 50 50 50 600 500 400 Donuts 50 300 Price/Dozen Cost/Dozen $2.50 $1.80 $3.25 $1.50 $3.50 $2.00 $5.00 $2.70 Profit/Dozen $0.70 $1.75 $1.50 $2.30 Materials used per dozen Flour Sugar Eggs Butter Chocolate Chips Labor Hours Flour Sugar Eggs Butter Chocolate Chips Labor Hours Profit 0.5 0.3 0.4 0.2 0.5 0.5 1.25 0.75 1 0.3 0 0.4 Used 337.5 105 220 50 50 95 Available 2000 500 1200 500 600 800 $312.50 1.75 0.3 1 0.25 0.5 0.4 3.25 0.75 2 0.25 0 0.6