{"text_format":"1","workbook":"xlsx","n_sheets":21,"sheets":["Cover","Multiplication","Multiplication (an)","Video 02","Budget","Budget (an)","Budget & Assumption T.","Budget & Assumption T. (an)","Pension","Pension (an)","Payroll","Payroll (an)","HW==>>","HW(1)","HW(1an)","HW(2)","HW(2an)","HW(3)","HW(3an)","HW(4)","HW(4an)"],"not_rendered":{"charts":0,"images":6,"macros":0,"external_links":1,"defined_names":4,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Cover","index":0,"n_rows":12,"n_cols":15,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Excel Basics 16: Mixed Cell References in Formulas & Functions to Save Time"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Office 2016 Video #28"]
[]
[]
[null,null,"Examples:"]
[null,1,{"$f":"Multiplication!A1","v":"Example 1: Mixed Cell References in Multiplication Table"}]
[null,2,{"$f":"'Budget & Assumption T.'!A1","v":"Example 2: Mixed Cell References & Assumption Tables."}]
[null,3,{"$f":"Budget!A1","v":"Example 3: Mixed Cell References for Budget with Income Statement Accounts"}]
[null,4,{"$f":"Pension!A1","v":"Example 4: Mixed Cell References for Retirement Savings Table"}]
[null,5,{"$f":"'Payroll (an)'!A1","v":"Example 5: Mixed Cell References & Correct Assumption Table for Dynamic Payroll Table"}]

{"sheet":"Multiplication","index":1,"n_rows":31,"n_cols":19,"rows_kept":31,"cut":false,"hidden":false,"merged":[]}
["Example 1: Mixed Cell References in Multiplication Table"]
[]
[{"$f":"\"Mutiplication Table for my Little \"&S31","v":"Mutiplication Table for my Little Sister"}]
[null,{"$f":"S29","v":1},{"$f":"B4+$S30","v":2},{"$f":"C4+$S30","v":3},{"$f":"D4+$S30","v":4},{"$f":"E4+$S30","v":5},{"$f":"F4+$S30","v":6},{"$f":"G4+$S30","v":7},{"$f":"H4+$S30","v":8},{"$f":"I4+$S30","v":9},{"$f":"J4+$S30","v":10},{"$f":"K4+$S30","v":11},{"$f":"L4+$S30","v":12}]
[{"$f":"B4","v":1}]
[{"$f":"A5+S$30","v":2}]
[{"$f":"A6+S$30","v":3}]
[{"$f":"A7+S$30","v":4}]
[{"$f":"A8+S$30","v":5}]
[{"$f":"A9+S$30","v":6}]
[{"$f":"A10+S$30","v":7}]
[{"$f":"A11+S$30","v":8}]
[{"$f":"A12+S$30","v":9}]
[{"$f":"A13+S$30","v":10}]
[{"$f":"A14+S$30","v":11}]
[{"$f":"A15+S$30","v":12}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Assumptions"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Start Number",1]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Increment",1]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Family Member","Sister"]

{"sheet":"Multiplication (an)","index":2,"n_rows":31,"n_cols":19,"rows_kept":31,"cut":false,"hidden":false,"merged":[]}
["Example 1: Mixed Cell References in Multiplication Table"]
[]
[{"$f":"\"Mutiplication Table for my Little \"&S31","v":"Mutiplication Table for my Little Sister"}]
[null,{"$f":"S29","v":1},{"$f":"B4+$S30","v":2},{"$f":"C4+$S30","v":3},{"$f":"D4+$S30","v":4},{"$f":"E4+$S30","v":5},{"$f":"F4+$S30","v":6},{"$f":"G4+$S30","v":7},{"$f":"H4+$S30","v":8},{"$f":"I4+$S30","v":9},{"$f":"J4+$S30","v":10},{"$f":"K4+$S30","v":11},{"$f":"L4+$S30","v":12}]
[{"$f":"B4","v":1},{"$f":"B$4*$A5","v":1},2,3,4,5,6,7,8,9,10,11,12]
[{"$f":"A5+S$30","v":2},2,4,6,8,10,12,14,16,18,20,22,24]
[{"$f":"A6+S$30","v":3},3,6,9,12,15,18,21,24,27,30,33,36]
[{"$f":"A7+S$30","v":4},4,8,12,16,20,24,28,32,36,40,44,48]
[{"$f":"A8+S$30","v":5},5,10,15,20,25,30,35,40,45,50,55,60]
[{"$f":"A9+S$30","v":6},6,12,18,24,30,36,42,48,54,60,66,72]
[{"$f":"A10+S$30","v":7},7,14,21,28,35,42,49,56,63,70,77,84]
[{"$f":"A11+S$30","v":8},8,16,24,32,40,48,56,64,72,80,88,96]
[{"$f":"A12+S$30","v":9},9,18,27,36,45,54,63,72,81,90,99,108]
[{"$f":"A13+S$30","v":10},10,20,30,40,50,60,70,80,90,100,110,120]
[{"$f":"A14+S$30","v":11},11,22,33,44,55,66,77,88,99,110,121,132]
[{"$f":"A15+S$30","v":12},12,24,36,48,60,72,84,96,108,120,132,144]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Assumptions"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Start Number",1]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Increment",1]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Family Member","Sister"]

{"sheet":"Video 02","index":3,"n_rows":9,"n_cols":7,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["2018 NI Projections","Jan","Feb","Mar","Apr","May","Total"]
["Revenue",{"$f":"B7","v":6500},{"$f":"B2*$B$8","v":6890},{"$f":"C2*$B$8","v":7303.4000000000005},7741.6040000000012,8206.1002400000016,{"$f":"SUM(B2:F2)","v":36641.104240000001}]
["Expenses",{"$f":"B2*$B$9","v":5525},{"$f":"C2*$B$9","v":5856.5},6207.89,6580.3634000000011,6975.1852040000012,{"$f":"SUM(B3:F3)","v":31144.938604000003}]
["Net Income",{"$f":"B2-B3","v":975},{"$f":"C2-C3","v":1033.5},1095.5100000000002,1161.2406000000001,1230.9150360000003,5496.1656360000006]
[]
["Assumptions (Formula Inputs)"]
["Start Revenue for Jan",6500]
["Revenue % Increase",1.06]
["Expense as % of Revenue",0.85]

{"sheet":"Budget","index":4,"n_rows":35,"n_cols":17,"rows_kept":35,"cut":false,"hidden":false,"merged":[]}
["Example 3: Mixed Cell References for Budget with Income Statement Accounts"]
[]
["Assumptions (Formula Inputs)"]
["Start Revenue for Jan",5000,null,"2018 NI Projections","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","Total"]
["Revenue % Increase",1.4999999999999999E-2,null,"Revenue"]
["Expense as % of Revenue:",null,null,"Expenses"]
["COGS",0.375,null,"COGS"]
["Salaries and Wages",0.13500000000000001,null,"Salaries and Wages"]
["Rent ",7.4999999999999997E-2,null,"Rent "]
["Supplies ",1.4999999999999999E-2,null,"Supplies "]
["Depreciation ",4.7500000000000001E-2,null,"Depreciation "]
["Utilities",3.5000000000000003E-2,null,"Utilities"]
["Advertising",3.7499999999999999E-2,null,"Advertising"]
["Administration ",2.5000000000000001E-2,null,"Administration "]
["Phone and Internet",1.4999999999999999E-2,null,"Phone and Internet"]
["Repairs & Maintenance",0.01,null,"Repairs & Maintenance"]
["Travel",0.01,null,"Travel"]
["Interest ",2.5000000000000001E-2,null,"Interest "]
["Tax ",8.7499999999999994E-2,null,"Tax "]
[null,null,null,"Total Expenses"]
["Total of Expenses %",{"$f":"SUM(B7:B19)","v":0.89250000000000007},null,"Net Income"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,4462.5,4529.4374999999991,4597.3790624999974,4666.3397484374973,4736.3348446640593,4807.3798673340189,4879.4905653440301,4952.6829238241889,5026.9731676815518,5102.3777651967757,5178.9134316747277,5256.5971331498477]
[null,null,null,null,537.5,545.5625,553.74593750000076,562.05212656250023,570.48290846093732,579.04015208785222,587.7257543691685,596.54164068470709,605.48976529497759,614.57211177440149,623.79069345101652,633.14755385278204]

{"sheet":"Budget (an)","index":5,"n_rows":35,"n_cols":17,"rows_kept":35,"cut":false,"hidden":false,"merged":[]}
["Example 3: Mixed Cell References for Budget with Income Statement Accounts"]
[]
["Assumptions (Formula Inputs)"]
["Start Revenue for Jan",5000,null,"2018 NI Projections","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","Total"]
["Revenue % Increase",1.4999999999999999E-2,null,"Revenue",{"$f":"B4","v":5000},{"$f":"ROUND(E5*(1+$B5),2)","v":5075},{"$f":"ROUND(F5*(1+$B5),2)","v":5151.13},5228.3999999999996,5306.83,5386.43,5467.23,5549.24,5632.48,5716.97,5802.72,5889.76,{"$f":"SUM(E5:P5)","v":65206.19000000001}]
["Expense as % of Revenue:",null,null,"Expenses"]
["COGS",0.375,null,"COGS",{"$f":"ROUND(E$5*$B7,2)","v":1875},1903.13,1931.67,1960.65,1990.06,2019.91,2050.21,2080.9699999999998,2112.1799999999998,2143.86,2176.02,2208.66,{"$f":"SUM(E7:P7)","v":24452.32}]
["Salaries and Wages",0.13500000000000001,null,"Salaries and Wages",675,685.13,695.4,705.83,716.42,727.17,738.08,749.15,760.38,771.79,783.37,795.12,{"$f":"SUM(E8:P8)","v":8802.84}]
["Rent ",7.4999999999999997E-2,null,"Rent ",375,380.63,386.33,392.13,398.01,403.98,410.04,416.19,422.44,428.77,435.2,441.73,4890.4500000000007]
["Supplies ",1.4999999999999999E-2,null,"Supplies ",75,76.13,77.27,78.430000000000007,79.599999999999994,80.8,82.01,83.24,84.49,85.75,87.04,88.35,978.11]
["Depreciation ",4.7500000000000001E-2,null,"Depreciation ",237.5,241.06,244.68,248.35,252.07,255.86,259.69,263.58999999999997,267.54000000000002,271.56,275.63,279.76,3097.29]
["Utilities",3.5000000000000003E-2,null,"Utilities",175,177.63,180.29,182.99,185.74,188.53,191.35,194.22,197.14,200.09,203.1,206.14,2282.2199999999998]
["Advertising",3.7499999999999999E-2,null,"Advertising",187.5,190.31,193.17,196.07,199.01,201.99,205.02,208.1,211.22,214.39,217.6,220.87,2445.2499999999995]
["Administration ",2.5000000000000001E-2,null,"Administration ",125,126.88,128.78,130.71,132.66999999999999,134.66,136.68,138.72999999999999,140.81,142.91999999999999,145.07,147.24,1630.1499999999999]
["Phone and Internet",1.4999999999999999E-2,null,"Phone and Internet",75,76.13,77.27,78.430000000000007,79.599999999999994,80.8,82.01,83.24,84.49,85.75,87.04,88.35,978.11]
["Repairs & Maintenance",0.01,null,"Repairs & Maintenance",50,50.75,51.51,52.28,53.07,53.86,54.67,55.49,56.32,57.17,58.03,58.9,652.04999999999995]
["Travel",0.01,null,"Travel",50,50.75,51.51,52.28,53.07,53.86,54.67,55.49,56.32,57.17,58.03,58.9,652.04999999999995]
["Interest ",2.5000000000000001E-2,null,"Interest ",125,126.88,128.78,130.71,132.66999999999999,134.66,136.68,138.72999999999999,140.81,142.91999999999999,145.07,147.24,1630.1499999999999]
["Tax ",8.7499999999999994E-2,null,"Tax ",437.5,444.06,450.72,457.49,464.35,471.31,478.38,485.56,492.84,500.23,507.74,515.35,5705.5300000000007]
[null,null,null,"Total Expenses",{"$f":"SUM(E7:E19)","v":4462.5},{"$f":"SUM(F7:F19)","v":4529.4700000000012},4597.380000000001,4666.3500000000004,4736.34,4807.3900000000003,4879.4900000000007,4952.6999999999989,5026.9799999999996,5102.3700000000008,5178.9399999999987,{"$f":"SUM(P7:P19)","v":5256.61},58196.520000000004]
["Total of Expenses %",{"$f":"SUM(B7:B19)","v":0.89250000000000007},null,"Net Income",{"$f":"E5-E20","v":537.5},{"$f":"F5-F20","v":545.52999999999884},553.74999999999909,562.04999999999927,570.48999999999978,579.04,587.73999999999887,596.54000000000087,605.5,614.59999999999945,623.78000000000156,633.15000000000055,7009.6699999999983]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,4462.5,4529.4374999999991,4597.3790624999974,4666.3397484374973,4736.3348446640593,4807.3798673340189,4879.4905653440301,4952.6829238241889,5026.9731676815518,5102.3777651967757,5178.9134316747277,5256.5971331498477]
[null,null,null,null,537.5,545.5625,553.74593750000076,562.05212656250023,570.48290846093732,579.04015208785222,587.7257543691685,596.54164068470709,605.48976529497759,614.57211177440149,623.79069345101652,633.14755385278204]

{"sheet":"Budget & Assumption T.","index":6,"n_rows":25,"n_cols":11,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["Example 2: Mixed Cell References & Assumption Tables."]
["Goal: Calculate Expenses for Budgeting Process."]
[]
["Month","Jan","Feb","Mar","Apr","May"]
["Revenue",65487,75894,65007,95449,99220,null,null,null,"COGS",0.46455983312744287]
["COGS",null,null,null,null,null,null,null,null,"Operating",0.19478928289353775]
["Operating",null,null,null,null,null,null,null,null,"Administrative",0.15438749265882257]
["Administrative",null,null,null,null,null,null,null,null,"Other",4.0371412948824396E-2]
["Other"]
["Total Expenses",{"$f":"SUM(B6:B9)","v":0},{"$f":"SUM(C6:C9)","v":0},{"$f":"SUM(D6:D9)","v":0},{"$f":"SUM(E6:E9)","v":0},{"$f":"SUM(F6:F9)","v":0}]
["Net Income",{"$f":"B5-B10","v":65487},{"$f":"C5-C10","v":75894},{"$f":"D5-D10","v":65007},{"$f":"E5-E10","v":95449},{"$f":"F5-F10","v":99220}]
[]
["This Assumption Table can be used with Mixed Cell References because"]
["expenses in Assumption Table are oriented in the same direction as the formula table."]
["Assumption Table (Formula Inputs):"]
["COGS",0.46455983312744287,null,"<<= Expenses are Vertical in Assumption Table & Vertical in Formula Table"]
["Operating",0.19478928289353775,null,"Horizontal / Horizontal works."]
["Administrative",0.15438749265882257,null,"Vertical / Vertical works."]
["Other",4.0371412948824396E-2,null,"Horizontal / Vertical will NOT work."]
[]
["This Assumption Table can NOT be used with Mixed Cell References"]
["because expenses in Assumption Table are NOT oriented in the same direction as the formula table."]
["Assumption Table (Formula Inputs):"]
["COGS","Operating","Administrative","Other"]
[0.46455983312744287,0.19478928289353775,0.15438749265882257,4.0371412948824396E-2]

{"sheet":"Budget & Assumption T. (an)","index":7,"n_rows":25,"n_cols":6,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["Example 2: Mixed Cell References & Assumption Tables."]
["Goal: Calculate Expenses for Budgeting Process."]
[]
["Month","Jan","Feb","Mar","Apr","May"]
["Revenue",65487,75894,65007,95449,99220]
["COGS",{"$f":"ROUND(B$5*$B16,2)","v":30422.63},{"$f":"ROUND(C$5*$B16,2)","v":35257.300000000003},30199.64,44341.77,46093.63]
["Operating",12756.17,14783.34,12662.67,18592.439999999999,19326.990000000002]
["Administrative",10110.370000000001,11717.08,10036.27,14736.13,15318.33]
["Other",2643.8,3063.95,2624.42,3853.41,4005.65]
["Total Expenses",{"$f":"SUM(B6:B9)","v":55932.970000000008},{"$f":"SUM(C6:C9)","v":64821.67},{"$f":"SUM(D6:D9)","v":55523},{"$f":"SUM(E6:E9)","v":81523.75},{"$f":"SUM(F6:F9)","v":84744.599999999991}]
["Net Income",{"$f":"B5-B10","v":9554.0299999999916},{"$f":"C5-C10","v":11072.330000000002},{"$f":"D5-D10","v":9484},{"$f":"E5-E10","v":13925.25},{"$f":"F5-F10","v":14475.400000000009}]
[]
["This Assumption Table can be used with Mixed Cell References because"]
["expenses in Assumption Table are oriented in the same direction as the formula table."]
["Assumption Table (Formula Inputs):"]
["COGS",0.46455983312744287,null,"<<= Expenses are Vertical in Assumption Table & Vertical in Formula Table"]
["Operating",0.19478928289353775,null,"Horizontal / Horizontal works."]
["Administrative",0.15438749265882257,null,"Vertical / Vertical works."]
["Other",4.0371412948824396E-2,null,"Horizontal / Vertical will NOT work."]
[]
["This Assumption Table can NOT be used with Mixed Cell References"]
["because expenses in Assumption Table are NOT oriented in the same direction as the formula table."]
["Assumption Table (Formula Inputs):"]
[{"$f":"TRANSPOSE(A16:B19)","v":"COGS"},"Operating","Administrative","Other"]
[0.46455983312744287,0.19478928289353775,0.15438749265882257,4.0371412948824396E-2]

{"sheet":"Pension","index":8,"n_rows":37,"n_cols":7,"rows_kept":37,"cut":false,"hidden":false,"merged":[]}
["Example 4: Mixed Cell References for Retirement Savings Table"]
[]
["Yearly PMT",-3000]
[]
["What will your Pension be worth when you retire?"]
["Years/Rate",{"$f":"B34","v":0.03},{"$f":"B6+$B35","v":0.04},{"$f":"C6+$B35","v":0.05},{"$f":"D6+$B35","v":6.0000000000000005E-2},{"$f":"E6+$B35","v":7.0000000000000007E-2},{"$f":"F6+$B35","v":0.08}]
[{"$f":"B36","v":10}]
[{"$f":"A7+B$37","v":15}]
[20]
[25]
[30]
[35]
[40]
[45]
[50]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Start rate",0.03]
["increment",0.01]
["Start years",10]
["Increment",5]

{"sheet":"Pension (an)","index":9,"n_rows":37,"n_cols":7,"rows_kept":37,"cut":false,"hidden":false,"merged":[]}
["Example 4: Mixed Cell References for Retirement Savings Table"]
[]
["Yearly PMT",-3000]
[]
["What will your Pension be worth when you retire?"]
["Years/Rate",{"$f":"B34","v":0.03},{"$f":"B6+$B35","v":0.04},{"$f":"C6+$B35","v":0.05},{"$f":"D6+$B35","v":6.0000000000000005E-2},{"$f":"E6+$B35","v":7.0000000000000007E-2},{"$f":"F6+$B35","v":0.08}]
[{"$f":"B36","v":10},{"$f":"FV(B$6,$A7,$B$3)","v":34391.637934412181},{"$f":"FV(C$6,$A7,$B$3)","v":36018.321368875841},37733.677606646488,39542.384827142727,41449.343883838526,43459.68739772954]
[{"$f":"A7+B$37","v":15},{"$f":"FV(B$6,$A8,$B$3)","v":55796.741660076448},60070.762913018756,64735.690764682069,69827.909654984614,75387.066030657195,81456.341782435178]
[20,80611.123466941324,89334.235727506588,99197.862308665237,110356.77361064238,122986.47696369339,137285.892894349]
[25,109377.79296542138,124937.72486155675,143181.29645396315,164593.5359871744,189747.11314812387,219317.81985822468]
[30,142726.2471189659,168254.81325206565,199316.54250903975,237174.55864566297,283382.35897122981,339849.63334025419]
[35,181386.24543715225,220956.67456589575,270960.92205553508,334304.33961561747,414710.63505494711,516950.41103702097]
[40,226203.77919990718,285076.54709524947,362399.32272747892,464285.8968562964,598905.33596601232,777169.55612999585]
[45,278159.584165134,363088.17610960559,479100.46760956873,638230.54137205146,857247.93251459545,1159516.8521359963]
[50,338390.60187070863,458001.25097087416,628043.98714522109,871007.71374957438,1219586.7884173426,1721310.4692461742]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Start rate",0.03]
["increment",0.01]
["Start years",10]
["Increment",5]

{"sheet":"Payroll","index":10,"n_rows":19,"n_cols":10,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
["Example 5: Mixed Cell References & Correct Assumption Table for Dynamic Payroll Table"]
[]
["Employee Take Home Pay Table"]
["Names","January\nSales","Commission\non Sales","Base\nSalary","Gross Pay",{"$f":"F13","v":"FICA\nDeduction"},{"$f":"G13","v":"Medicare Deduction"},{"$f":"H13","v":"Federal Withholdings"},"Total Deductions","Net Pay"]
["Steve",125000,{"$f":"ROUND(B5*C$14,2)","v":2500},2000,{"$f":"SUM(C5:D5)","v":4500},null,null,null,{"$f":"SUM(F5:H5)","v":0},{"$f":"E5-I5","v":4500}]
["Abdi",97855,{"$f":"ROUND(B6*C$14,2)","v":1957.1},2000,{"$f":"SUM(C6:D6)","v":3957.1},null,null,null,{"$f":"SUM(F6:H6)","v":0},{"$f":"E6-I6","v":3957.1}]
["Tyrone",181250,{"$f":"ROUND(B7*C$14,2)","v":3625},2000,{"$f":"SUM(C7:D7)","v":5625},null,null,null,{"$f":"SUM(F7:H7)","v":0},{"$f":"E7-I7","v":5625}]
["Aya",95500,{"$f":"ROUND(B8*C$14,2)","v":1910},2000,{"$f":"SUM(C8:D8)","v":3910},null,null,null,{"$f":"SUM(F8:H8)","v":0},{"$f":"E8-I8","v":3910}]
["Bobbi",78550,{"$f":"ROUND(B9*C$14,2)","v":1571},2000,{"$f":"SUM(C9:D9)","v":3571},null,null,null,{"$f":"SUM(F9:H9)","v":0},{"$f":"E9-I9","v":3571}]
["Totals",{"$f":"SUM(B5:B9)","v":578155},11563.1,10000,21563.1,0,0,0,0,21563.1]
[]
[null,null,null,null,null,"Assumption Table"]
[null,null,"Commission Rate",null,null,"FICA\nDeduction","Medicare Deduction","Federal Withholdings"]
[null,null,0.02,null,null,6.2E-2,1.4500000000000001E-2,0.2]
[]
[null,null,null,null,null,{"$f":"F12","v":"Assumption Table"}]
[null,null,null,null,null,{"$f":"F13","v":"FICA\nDeduction"},{"$f":"F14","v":6.2E-2}]
[null,null,null,null,null,{"$f":"G13","v":"Medicare Deduction"},{"$f":"G14","v":1.4500000000000001E-2}]
[null,null,null,null,null,{"$f":"H13","v":"Federal Withholdings"},{"$f":"H14","v":0.2}]

{"sheet":"Payroll (an)","index":11,"n_rows":19,"n_cols":11,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
["Example 5: Mixed Cell References & Correct Assumption Table for Dynamic Payroll Table"]
[]
["Employee Take Home Pay Table"]
["Names","January\nSales","Commission\non Sales","Base\nSalary","Gross Pay",{"$f":"F13","v":"FICA\nDeduction"},{"$f":"G13","v":"Medicare Deduction"},{"$f":"H13","v":"Pension"},{"$f":"I13","v":"Federal Withholdings"},"Total Deductions","Net Pay"]
["Steve",125000,{"$f":"ROUND(B5*C$14,2)","v":2500},2000,{"$f":"SUM(C5:D5)","v":4500},{"$f":"ROUND($E5*F$14,2)","v":279},{"$f":"ROUND($E5*G$14,2)","v":65.25},90,900,{"$f":"SUM(F5:I5)","v":1334.25},{"$f":"E5-J5","v":3165.75}]
["Abdi",97855,{"$f":"ROUND(B6*C$14,2)","v":1957.1},2000,{"$f":"SUM(C6:D6)","v":3957.1},{"$f":"ROUND($E6*F$14,2)","v":245.34},57.38,79.14,791.42,{"$f":"SUM(F6:I6)","v":1173.28},{"$f":"E6-J6","v":2783.8199999999997}]
["Tyrone",181250,{"$f":"ROUND(B7*C$14,2)","v":3625},2000,{"$f":"SUM(C7:D7)","v":5625},348.75,81.56,112.5,1125,{"$f":"SUM(F7:I7)","v":1667.81},{"$f":"E7-J7","v":3957.19}]
["Aya",95500,{"$f":"ROUND(B8*C$14,2)","v":1910},2000,{"$f":"SUM(C8:D8)","v":3910},242.42,56.7,78.2,782,{"$f":"SUM(F8:I8)","v":1159.32},{"$f":"E8-J8","v":2750.6800000000003}]
["Bobbi",78550,{"$f":"ROUND(B9*C$14,2)","v":1571},2000,{"$f":"SUM(C9:D9)","v":3571},221.4,51.78,71.42,{"$f":"ROUND($E9*I$14,2)","v":714.2},{"$f":"SUM(F9:I9)","v":1058.8000000000002},{"$f":"E9-J9","v":2512.1999999999998}]
["Totals",{"$f":"SUM(B5:B9)","v":578155},11563.1,10000,21563.1,1336.91,312.66999999999996,{"$f":"SUM(H5:H9)","v":431.26},4312.62,6393.46,15169.64]
[]
[null,null,null,null,null,"Assumption Table"]
[null,null,"Commission Rate",null,null,"FICA\nDeduction","Medicare Deduction","Pension","Federal Withholdings"]
[null,null,0.02,null,null,6.2E-2,1.4500000000000001E-2,0.02,0.2]
[]
[null,null,null,null,null,{"$f":"F12","v":"Assumption Table"}]
[null,null,null,null,null,{"$f":"F13","v":"FICA\nDeduction"},{"$f":"F14","v":6.2E-2}]
[null,null,null,null,null,{"$f":"G13","v":"Medicare Deduction"},{"$f":"G14","v":1.4500000000000001E-2}]
[null,null,null,null,null,{"$f":"I13","v":"Federal Withholdings"},{"$f":"I14","v":0.2}]

{"sheet":"HW==>>","index":12,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"HW(1)","index":13,"n_rows":16,"n_cols":13,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
["Using Mixed Cell References, complete the multiplication table."]
[]
["Mutiplication Table for my Little Sister"]
[null,1,2,3,4,5,6,7,8,9,10,11,12]
[1]
[2]
[3]
[4]
[5]
[6]
[7]
[8]
[9]
[10]
[11]
[12]

{"sheet":"HW(1an)","index":14,"n_rows":16,"n_cols":13,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
["Using Mixed Cell References, complete the multiplication table."]
[]
["Mutiplication Table for my Little Sister"]
[null,1,2,3,4,5,6,7,8,9,10,11,12]
[1,{"$f":"B$4*$A5","v":1},2,3,4,5,6,7,8,9,10,11,12]
[2,2,4,6,8,10,12,14,16,18,20,22,24]
[3,3,6,9,12,15,18,21,24,27,30,33,36]
[4,4,8,12,16,20,24,28,32,36,40,44,48]
[5,5,10,15,20,25,30,35,40,45,50,55,60]
[6,6,12,18,24,30,36,42,48,54,60,66,72]
[7,7,14,21,28,35,42,49,56,63,70,77,84]
[8,8,16,24,32,40,48,56,64,72,80,88,96]
[9,9,18,27,36,45,54,63,72,81,90,99,108]
[10,10,20,30,40,50,60,70,80,90,100,110,120]
[11,11,22,33,44,55,66,77,88,99,110,121,132]
[12,12,24,36,48,60,72,84,96,108,120,132,144]

{"sheet":"HW(2)","index":15,"n_rows":19,"n_cols":5,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
["Create the formulas to complete the Projected Net Income Statement."]
[]
[]
[null,"Jan","Feb","Mar","Apr"]
["Revenue",5000,6000,7500,6900]
[{"$f":"A15","v":"Expense 1"}]
[{"$f":"A16","v":"Expense 2"}]
[{"$f":"A17","v":"Expense 3"}]
[{"$f":"A18","v":"Expense 4"}]
[{"$f":"A19","v":"Expense 5"}]
["Total Expenses"]
["Net Income"]
[]
["Assumption Table (Formula Inputs)"]
["Expense 1",0.2]
["Expense 2",0.15]
["Expense 3",0.14000000000000001]
["Expense 4",0.05]
["Expense 5",0.33]

{"sheet":"HW(2an)","index":16,"n_rows":19,"n_cols":5,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
["Create the formulas to complete the Projected Net Income Statement."]
[]
[]
[null,"Jan","Feb","Mar","Apr"]
["Revenue",5000,6000,7500,6900]
[{"$f":"A15","v":"Expense 1"},{"$f":"ROUND(B$5*$B15,2)","v":1000},1200,1500,1380]
[{"$f":"A16","v":"Expense 2"},750,900,1125,1035]
[{"$f":"A17","v":"Expense 3"},700,840,1050,966]
[{"$f":"A18","v":"Expense 4"},250,300,375,345]
[{"$f":"A19","v":"Expense 5"},1650,1980,2475,2277]
["Total Expenses",{"$f":"SUM(B6:B10)","v":4350},5220,6525,6003]
["Net Income",{"$f":"B5-B11","v":650},780,975,897]
[]
["Assumption Table (Formula Inputs)"]
["Expense 1",0.2]
["Expense 2",0.15]
["Expense 3",0.14000000000000001]
["Expense 4",0.05]
["Expense 5",0.33]

{"sheet":"HW(3)","index":17,"n_rows":22,"n_cols":10,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
["In the green cells create a formula that calculates the expenses as a percent of Income."]
[]
[null,"Month","Income",{"$f":"D16","v":"Expense 1"},"Expense 2","Expense 3","Expense 4","Expense 5","Expense 6","Expense 7"]
[null,"Jan",5000]
[null,"Feb",6000]
[null,"Mar",7500]
[null,"Apr",8000]
[null,"May",8000]
[null,"Jun",8000]
[null,"Jul",10000]
[null,"Aug",11000]
[null,"Sep",15000]
[]
[]
["Assumptions (Formula Inputs)",null,null,"Assumptions (Formula Inputs)"]
["Expense 1",0.1,null,"Expense 1","Expense 2","Expense 3","Expense 4","Expense 5","Expense 6","Expense 7"]
["Expense 2",0.05,null,0.1,0.05,0.12,4.4999999999999998E-2,0.21,0.04,0.03]
["Expense 3",0.12]
["Expense 4",4.4999999999999998E-2]
["Expense 5",0.21]
["Expense 6",0.04]
["Expense 7",0.03]

{"sheet":"HW(3an)","index":18,"n_rows":22,"n_cols":10,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
["In the green cells create a formula that calculates the expenses as a percent of Income."]
[]
[null,"Month","Income",{"$f":"D16","v":"Expense 1"},"Expense 2","Expense 3","Expense 4","Expense 5","Expense 6","Expense 7"]
[null,"Jan",5000,{"$f":"ROUND($C4*D$17,2)","v":500},250,600,225,1050,200,150]
[null,"Feb",6000,600,300,720,270,1260,240,180]
[null,"Mar",7500,750,375,900,337.5,1575,300,225]
[null,"Apr",8000,800,400,960,360,1680,320,240]
[null,"May",8000,800,400,960,360,1680,320,240]
[null,"Jun",8000,800,400,960,360,1680,320,240]
[null,"Jul",10000,1000,500,1200,450,2100,400,300]
[null,"Aug",11000,1100,550,1320,495,2310,440,330]
[null,"Sep",15000,1500,750,1800,675,3150,600,450]
[]
[]
["Assumptions (Formula Inputs)",null,null,"Assumptions (Formula Inputs)"]
["Expense 1",0.1,null,"Expense 1","Expense 2","Expense 3","Expense 4","Expense 5","Expense 6","Expense 7"]
["Expense 2",0.05,null,0.1,0.05,0.12,4.4999999999999998E-2,0.21,0.04,0.03]
["Expense 3",0.12]
["Expense 4",4.4999999999999998E-2]
["Expense 5",0.21]
["Expense 6",0.04]
["Expense 7",0.03]

{"sheet":"HW(4)","index":19,"n_rows":17,"n_cols":10,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["Create the formulas to complete the Projected Net Income Statement."]
[]
[null,"Jan","Feb","Mar","Apr","May","Jun"]
["Sales",100,200,300,400,500,600,null,"Assumptions"]
["Ex1",null,null,null,null,null,null,null,"Ex1",0.03]
["Ex2",null,null,null,null,null,null,null,"Ex2",0.04]
["Ex3",null,null,null,null,null,null,null,"Ex3",0.05]
["Ex4",null,null,null,null,null,null,null,"Ex4",0.06]
["Ex5",null,null,null,null,null,null,null,"Ex5",7.0000000000000007E-2]
["Ex6",null,null,null,null,null,null,null,"Ex6",0.08]
["Ex7",null,null,null,null,null,null,null,"Ex7",0.09]
["Ex8",null,null,null,null,null,null,null,"Ex8",0.1]
["Ex9",null,null,null,null,null,null,null,"Ex9",0.11]
["Ex10",null,null,null,null,null,null,null,"Ex10",0.12]
["Ex11",null,null,null,null,null,null,null,"Ex11",0.13]
["Total Ex"]
["Net Income"]

{"sheet":"HW(4an)","index":20,"n_rows":17,"n_cols":10,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["Create the formulas to complete the Projected Net Income Statement."]
[]
[null,"Jan","Feb","Mar","Apr","May","Jun"]
["Sales",100,200,300,400,500,600,null,"Assumptions"]
["Ex1",{"$f":"ROUND(B$4*$J5,2)","v":3},6,9,12,15,18,null,"Ex1",0.03]
["Ex2",4,8,12,16,20,24,null,"Ex2",0.04]
["Ex3",5,10,15,20,25,30,null,"Ex3",0.05]
["Ex4",6,12,18,24,30,36,null,"Ex4",0.06]
["Ex5",7,14,21,28,35,42,null,"Ex5",7.0000000000000007E-2]
["Ex6",8,16,24,32,40,48,null,"Ex6",0.08]
["Ex7",9,18,27,36,45,54,null,"Ex7",0.09]
["Ex8",10,20,30,40,50,60,null,"Ex8",0.1]
["Ex9",11,22,33,44,55,66,null,"Ex9",0.11]
["Ex10",12,24,36,48,60,72,null,"Ex10",0.12]
["Ex11",13,26,39,52,65,78,null,"Ex11",0.13]
["Total Ex",{"$f":"SUM(B5:B15)","v":88},{"$f":"SUM(C5:C15)","v":176},264,352,440,528,null,null,{"$f":"SUM(J5:J15)","v":0.88}]
["Net Income",{"$f":"B4-B16","v":12},24,36,48,60,72]