Instructions Database includes 2 years worth of actual data and one year of budget data for a retail business with 2 locations. In 2017, there was just one location, North. In January 2018, a new...

How to create income statement and budget


Instructions Database includes 2 years worth of actual data and one year of budget data for a retail business with 2 locations. In 2017, there was just one location, North. In January 2018, a new location, South, opened. Budget data is based on best estimates of business activity. Actual data included: Some maintenance costs that were expensed in Q4 Other Expenses North location were unexpected and not budgeted for. Sales Discounts can be impacted by competition (more competition, more discounts must be offered). In retail, industry averages include the following: Percentage of Total Sales COGS33% Wages + Benefits25% Rent + Utilities10% Requirements: 1. Create an Income Statement by location and in total for both budget and actual data (row format) for both 2017 and 2018. 2. Use common size and specific ratios to analysize year over year results and to compare to budget data. 3. Create a 2019 Income Statement budget by location and in total including the following: 1. provide reasons for values 2. Budget for improved results and explain how the improvement will be achieved. In particular, the Net Profit for the South location must be improved. Use ratios, common size analysis and trend analysis to support your budget estimates and improvements. Data Act/BudMonthYearLocationSalesSales DiscountsNet SalesCOGSGross MarginWagesFringe benefitsRentDepreciationUtilitiesFreightPhone & InternetPackagingBank and MonerisMarketingOther ExpTotal ExpensesOp IncomeInterest ExpNet Income ActualJanuary2017North96,4093,85692,55331,81560,73822,1742,8837,2572,6144,2893,1817471,2731,8514,7243,70254,6956,0433,7962,247 ActualFebruary2017North124,6436,232118,41138,63979,77227,4214,1137,4183,8813,8765,7967571,5463,5523,5715,92167,85211,9203,5878,333 ActualMarch2017North110,1874,407105,78033,05672,72323,1393,4717,3653,0224,4974,2977362,3145,2893,2746,34763,7518,9724,1414,831 ActualApril2017North115,4234,617110,80635,78175,02524,2393,6367,2672,9163,2613,9368002,1474,4323,1315,54061,30513,7204,2679,453 ActualMay2017North115,1593,455111,70438,00273,70223,0322,7647,4902,7204,4844,5607511,5204,4684,6135,58561,98711,7143,8207,894 ActualJune2017North102,0503,06298,98930,61568,37424,4923,4297,2163,6554,1924,5927251,5314,9494,3992,97062,1506,2244,1892,035 ActualJuly2017North103,7963,114100,68236,32964,35422,8352,5127,4843,6894,0984,7236592,1805,0343,4885,03461,7362,6184,060(1,442) ActualAugust2017North101,0665,05396,01330,32065,69325,2672,7797,2353,2234,9164,5487811,8192,8803,9973,84161,2864,4073,950457 ActualSeptember2017North119,3312,387116,94435,79981,14529,8332,9837,2542,7404,0565,3706601,4325,8474,0615,84770,08311,0624,0547,008 ActualOctober2017North93,7274,68689,04128,11860,92323,4323,2807,4363,6393,0103,3747181,1254,4523,5045,34259,3131,6104,497(2,887) ActualNovember2017North87,9512,63985,31227,26558,04819,3492,9027,2573,0953,3074,0906351,3632,5593,9942,55951,1116,9363,6663,270 ActualDecember2017North109,1445,457103,68736,01867,66922,9203,2097,4363,0564,2745,0426101,8015,1843,5194,14761,1996,4703,6622,808 ActualJanuary2018North115,1013,453111,64839,13472,51426,4733,4427,4433,8174,6835,0877181,5653,3493,8445,58266,0046,5093,7442,765 ActualFebruary2018North115,4862,310113,17636,95676,22128,8724,0427,4392,7994,8695,5437281,8484,5273,3315,65969,6566,5644,2122,352 ActualMarch2018North96,5111,93094,58131,84962,73220,2672,2297,3513,5203,7514,4597331,5922,8373,8225,67556,2376,4954,2332,262 ActualApril2018North93,7872,81490,97330,01260,96222,5092,9267,1763,3613,3253,9026471,8012,7293,9163,63955,9305,0314,032999 ActualMay2018North82,2662,46879,79826,32553,47317,2762,2467,4553,5474,5813,9497791,3163,9903,8173,99052,9465273,540(3,013) ActualJune2018North105,0133,150101,86331,50470,35922,0533,0877,1752,6663,3324,7267761,2602,0374,8593,05655,02715,3324,16611,166 ActualJuly2018North108,0873,243104,84436,75068,09522,6983,4057,3662,8063,9044,4107671,8374,1943,5884,19459,1698,9264,2984,628 ActualAugust2018North121,8263,655118,17141,42176,75030,4573,9597,0772,5383,1334,5567282,8993,5453,1215,90967,9228,8284,4514,377 ActualSeptember2018North93,2923,73289,56027,98861,57322,3902,6877,2302,7374,6813,3597201,3991,7913,1355,37455,5036,0703,6292,441 ActualOctober2018North81,1834,05977,12428,41448,71018,6722,8017,1592,9443,7872,8416751,7051,5423,3233,08548,5351754,492(4,317) ActualNovember2018North120,7394,830115,90936,22279,68826,5633,1887,0293,8374,5915,0716382,5362,3184,5075,79566,07213,6154,2209,395 ActualDecember2018North112,0863,363108,72338,10970,61428,0223,9237,1953,2303,0044,9547172,2873,2624,6663,26264,5216,0943,9722,122 BudgetJanuary2018North113,2482,265110,98335,10775,87624,9152,4916,1913,6154,7913,5115791,4045,5493,4433,32959,81916,0584,88711,171 BudgetFebruary2018North89,4663,57985,88730,41855,46919,6832,3626,6962,9545,5603,0425911,2172,5773,2014,29452,1763,2934,688(1,395) BudgetMarch2018North93,2911,86691,42528,92062,50521,4572,3606,4742,7565,5022,3145021,1571,8293,5643,65751,57110,9343,4607,474 BudgetApril2018North120,0613,602116,45940,82175,63827,6143,3146,8363,5554,1113,2665272,0412,3293,1285,82362,54413,0953,1189,977 BudgetMay2018North113,9882,280111,70835,33676,37227,3572,7366,8593,7534,5073,1805881,0605,5853,5283,35162,50513,8674,0989,769 BudgetJune2018North87,5652,62784,93828,89656,04220,1402,4176,4083,6505,5153,1795471,4451,6993,6324,24752,8783,1644,906(1,742) BudgetJuly2018North123,8422,477121,36540,86880,49727,2452,9976,0913,1144,6155,3135072,0436,0683,4143,64165,04915,4494,93510,514 BudgetAugust2018North85,9352,57883,35727,49955,85819,7652,1746,8043,2894,2892,2005021,6501,6673,4703,33449,1456,7134,6162,097 BudgetSeptember2018North86,5692,59783,97228,56855,40419,0452,2856,2542,5095,6192,2855891,1434,1993,7793,35951,0664,3384,692(354) BudgetOctober2018North110,5092,210108,29936,46871,83125,4173,0506,5903,8664,7404,0115911,8232,1663,9094,33260,49611,3354,5806,755 BudgetNovember2018North114,6924,588110,10436,70173,40325,2322,5236,3913,4484,1832,9365221,4683,3033,9374,40458,34815,0554,05810,997 BudgetDecember2018North85,9743,43982,53528,37154,16421,4942,3646,0513,6455,8513,4055451,4192,4763,3872,47653,1121,0523,729(2,677) ActualJanuary2018South118,8373,565115,27239,21676,05626,1442,6147,1062,7243,1995,0986252,3533,4583,2616,91663,49912,5574,3738,184 ActualFebruary2018South91,9923,68088,31227,59860,71522,0782,2087,4302,9813,6263,3127011,3803,5324,6243,53255,4045,3103,5661,744 ActualMarch2018South112,2735,614106,65934,80571,85524,7002,9647,2822,9493,9674,8737432,4365,3333,2803,20061,72710,1283,5296,599 ActualApril2018South89,9373,59786,34027,88058,45922,4843,1487,4483,0274,3464,1827291,6731,7274,9503,45457,1671,2923,862(2,570) ActualMay2018South89,7252,69287,03329,60957,42420,6373,0967,0652,5213,6644,4417721,1842,6114,7473,48154,2193,2054,292(1,087) ActualJune2018South122,2706,114116,15737,90478,25325,6773,5957,0473,4614,9235,3077282,6535,8083,6403,48566,32311,9304,2027,728 ActualJuly2018South107,9134,317103,59632,37471,22326,9784,0477,0032,6933,8973,5616101,9423,1083,8844,14461,8679,3553,8515,504 ActualAugust2018South110,8054,432106,37333,24273,13125,4853,3137,4053,7043,2714,6547201,6625,3194,8854,25564,6738,4593,8474,612 ActualSeptember2018South90,7622,72388,03931,76756,27219,9681,9977,1733,6174,6924,4477521,5883,5224,6005,28257,638(1,366)4,099(5,465) ActualOctober2018South97,0441,94195,10332,02563,07924,2612,6697,1662,8833,7314,1636521,9211,9024,0133,80457,1665,9133,5712,342 ActualNovember2018South92,5931,85290,74130,55660,18519,4451,9447,1763,3234,0153,6676151,2222,7223,0372,72249,88810,2973,5096,788 ActualDecember2018South87,8834,39483,48928,12355,36620,2132,2237,3493,7894,8473,9377091,6873,3405,0002,50555,599(233)4,409(4,642) BudgetJanuary2018South100,0001,00099,00045,00054,00014,0001,7507,5002,5004,0002,2007501,2382,2504,0001,53541,72212,2784,8007,478 BudgetFebruary2018South100,0001,00099,00045,00054,00014,0001,7507,5002,5004,0002,2007501,2382,2504,0001,53541,72212,2784,8007,478 BudgetMarch2018South100,0001,00099,00045,00054,00014,0001,7507,5002,5004,0002,2007501,2382,2504,0001,53541,72212,2784,8007,478 BudgetApril2018South100,0001,00099,00045,00054,00014,0001,7507,5002,5004,0002,2007501,2382,2504,0001,53541,72212,2784,8007,478 BudgetMay2018South100,0001,00099,00045,00054,00014,0001,7507,5002,5004,0002,2007501,2382,2504,0001,53541,72212,2784,8007,478 BudgetJune2018South100,0002,00098,00045,00053,00014,0001,7507,5002,5004,0002,2007501,2252,2504,0001,51941,69411,3064,8006,506 BudgetJuly2018South100,0002,00098,00045,00053,00014,0001,7507,5002,5004,0002,2007501,2252,2504,0001,51941,69411,3064,8006,506 BudgetAugust2018South100,0002,00098,00045,00053,00014,0001,7507,5002,5004,0002,2007501,2252,2504,0001,51941,69411,3064,8006,506 BudgetSeptember2018South100,0002,00098,00045,00053,00014,0001,7507,5002,5004,0002,2007501,2252,2504,0001,51941,69411,3064,8006,506 BudgetOctober2018South100,0002,00098,00045,00053,00014,0001,7507,5002,5004,0002,2007501,2252,2504,0001,51941,69411,3064,8006,506 BudgetNovember2018South100,0002,00098,00045,00053,00014,0001,7507,5002,5004,0002,2007501,2252,2504,0001,51941,69411,3064,8006,506 BudgetDecember2018South100,0002,00098,00045,00053,00014,0001,7507,5002,5004,0002,2007501,2252,2504,0001,51941,69411,3064,8006,506
Dec 12, 2021
SOLUTION.PDF

Get Answer To This Question

Related Questions & Answers

More Questions ยป

Submit New Assignment

Copy and Paste Your Assignment Here