please show formulas used in excel

4. Chock figures: - total cevt of skirt purchavest 5371,380 - total manufactariag everhead! 591,031 - ending cash balances 551.824 - Eet eperating income: 5175,370 5. Perform - what if 7 analysis and 10x the cificacy of your master budects: if expected unit sales for 92 is 7,000 shirts, net operating income shorell be 5177.218 SU DEVHIS SWEATSHIRT C.ALOKE. Aferer gratuating from Sourthocesem University in Colloge Town. USA with a degree in business, Eituae Johnsion realiacd that he wanted to remain in Colloge Tewn. After a number of unsuceessful attempts at getting a job in lis discipline, Fithan decided to go ieta business for hittwelf. In thinkieg about his business veatsec. Fthan deteraninad that he bad four eriteria foe the nsw busincos. - Fird, be wankdd to do womething that he woull cnjoy. - Secoed, he wanted a besiness that would give back to the community- - Third, he wased a businces thal would grow and be mute isececeviul etery year - Foarth, realizing that he was going to have to work very hard, Ethan wanted a business that woeld generate s minimum ect income of 5150.000 annually: While reflecting on the crileria he kad callined. Ethan, who had hece prosident ef tais fratemity and served as an officer in several other stasknt organizations, tealized that there was tos place as Cellege Towa wo have custom sweatdirts made anithy a silk-mereeti proces. When student orgatizatiots wanted sw catshirts for their members or to maket en campos, the officers had to make a trip 10 a city 100 miles away 10 visit Fhirs and Mere." Ethan had worked as a part-time employee at Shirs and Moee while he was in high school and had cmisiceod ewning swch a shop. He realizod that a sw catshirt stop in College Town had the pocential so mect all four crilcria he had set. Ethan set op an appoctement with Jayne Sted, the ouncr of Stits and More, to cotain informaticen useful is getiing his shep started. Because Jayne liked Ethan and was intrigaed by him entrepresseurial spirit, she answered many of Fthan's questions. In addition, 1ayne provided information concerning the rype of eqpaipmean Ethan would necd for his busisess and the average uscful hife. Jyyne knows a competinor who is rctiring and wosld like to sell his cyajpencm. Ethan can purchase the cquipment at the begineing of 2023 and the owner is willing to give him tenms of 50 , dee ugoe purthase and 50 his tue the quarter following the purchasc. Ethan will purthase the following equipenent January 1. 2023: Ethat will use the straight-line method (assuming no resudual valuci ia rocond the stepreciation of the equipencat. Ithan has decided wo use the sueatshint supplier recommended by leyne. He learned the purchase cost per saveatshirt to be sitk-screened (direct materials) would be $12.40. fapne encouraged Ethan to maintain an ending invenlory of shists equal to 25% of the accil encouraged Eitan 70 maintain an ending inverilory of ahints cepal to 25% of the ned quarter's sales. Jayne has also encouraged Ethan to ask the swcatshirt sapplier for terms of aons of a quarter's parchaves to be paid in the qaarter of parchave with the remaining 60. of the quarter's parthaves to be paid in the quarter following the perchase Ethan also leamed frum talking with Jayte that the ink (indirect malerials) used in the alk-screen poocest costs appectumately $.8 Z per shirt. Knowing that the silk-sereen prosess is somerehat labor intensive, Ethan plans to hire college students to help with the silk wereen prosess with the wage nate of $13.5 per hear. Each shirt needs approainaicty 0.1 hovirs 10 complete the silk-screen process. In addition, Ethan will niced one porvon lo take orders. bell cusemorn, and perale the each register. Mary Ann Smith, who is currenty Ditecter of Siludet Developenent at Fabsern University, has appeoached Fithan ahenit a job in ales. Mary Ama knews the edisecr of all of the student ofgaciratices on College. In aldition, she is very active in the community. Ethan thiniss Mary Ane can bring in a ke of busaness, In adsirice sbe alse has the clerical shils needed for the position. Because of her contacts, Ethan is willing to pay Mary Ame $1,220 per month plus a commission of 8.64 is of cales revenuce. Fithas realizes that he will have difficulty in finding a pervat akillod in computer praphics 6o gentrale the designs to be printed ee the shimbs. Jayne resently hired a jraphics desigher in thet powition for \$hirts and More at a rate of $630 per motel plas $.24 for each shist priased. Fituan believer he can find a university graphics devige shadest oo work for the same rate layne is paying her decipner. Lthan was fortunate in finding a commercial terilding for ent near the Universiry and the dowmens arca. The landlord requires a onc-ycar lease. Althonch the monthly ront of SI, 500 is moee than Ethan had anticiguied paying, the building is nice, has adogate parking, and there is room for exjunsion. Ethat anticigates that 75% of the trildite will be used in the silk-serees prosess while 25% with be used for sales. Eihaid's finternity beothers have cnoournged him wo advertise wedly in the Easiern University student newspaper. Upon inquining Lthan fornd that a 313ad would wost 528 per week. Ethan alse plans to run a weckly ad in the local new super that will cost him 580 per wock. (Note; Use 52 wecks per year invtead of 4 werks per mesth ) E thas wants to sell a large mumber of qualify shirts af a rezsonable cost. Hic extimales the selling prise of each suslomised shirt to be 526.5. All sales are credit sales. Iayne has suggeved that he should ask caceesers to pay for 50 in of their jortanes in the quater purchased, 35% in the quarter following the porchases, asd 15% in the second quarer following the purchases. Since Ethan will be making cestom sweatshirts, he is not kecping any extra inventory of fimiched shirts. AAcr talking to the insurance agent and the groperty valuation administrator in his manticipaliry. F:thas estimates that the peoperty taxes and itsarance os the mactiatery will cost \$2,640 annually. while peoperty tax and insurasce on display facniture and cash regioter will tocal $420 annsally. The property taxes will be paid cach qaarter Jayne reminded Ethan that maintenaces of the machines is requied for the silk-sctwen process. In addition. Ethan realixes that he must consider the coed of utilitick. The and More. Fa addition, Shirs and More sells approximiately the same number of shirth Ethat plans to sell in his store. Therefore, Eithan is confident that the maintenance and utility cests for his shop will be comparable to the maintenance and atility coes for Stans and Mose as below, Ethan will use the rearrosion method to estimute variable and fixad costs for the purpose of bodgeting. (Node, Whew estimsting mainteranse and unility costs, input data en a norksheyt and we Excel Data Analysis fitncticers to estimate the slope and the inferceje, When yor budget maintenaece and unlity costs, you should ane foemulas lecked ta the estimates of slope and intereept, and related quantify of shins, indead of inpuming numbers manually. Use Zwcel to display the total costs to the whole dollar.) Fthas cstimates the number of shirts so be sold in the fins five quericre, beginning January 2021 , to be: Secing how determined his son was so becume an entreptencur. Eithat's father offered to co-sign a note for an amount up to 536,000 wo help Fithan open his sweasdhir shop, Sun Derils Sweatshirt Galore. The loan officer advised Eathan that the insicrest cate en a 12. Devils Swearshirt Galore. The loan officer advised Ethan that the inerest rate cen 12month loan would be 8 percent. Ethan copects the loan to be taken out 3muary 1,2023 and paid bock, aloeg with interct eypense, on Docentice 31,2023. Preparatien of Spreadshect File Create the fotlowing six yeparate workbects: Sheet I: Regressien Results Present the regersion results firs ceximation of maintenance and utility costs. Sheet 3. Iselude the following nev budgets on the third worksheet, clearly lakelad: Sheet 4. Lelude the following rwe budgets on the fourth work wheet, cleady lakeled. Silk-Screen Labor Prepare a labor budect, in laboe twours Dedect and in lotal dollars, for cach quarler and foe the year a total. Silk-4creen Overhed Propace an werhead tradzes fir each Bedget quarice and for the year in total. List the overbeal items in the follesing order Yariable Overtheratcons itreluding ind. maitienanes, inilities, graphics design, abal subtutal of variable costs; Eised Qusthod Costs inclading rent, maimenance, utilities, graphics design, property taves and insumance. dopreciation, and subitotal of fised costs: 5. Itelude the following treo budgets an the fift weekshect, clearly labeled: 6. Include the following on the sivth worksheet, clearly labeled: Bedgeted 1ncome Tropare a huslgeted anceme isatement Siatement threagh net operaties inceese (interest expense escluded) for 2023 in ghe centribation marsin furm to Chupter 7 or 8 ). Target Profit Analysix Use Exeel focmula to conpute bow many chits Eithan woeld have to sell to care a target net operatitg aweete of 5000,000 for 2023 , 4. Chock figures: - total cevt of skirt purchavest 5371,380 - total manufactariag everhead! 591,031 - ending cash balances 551.824 - Eet eperating income: 5175,370 5. Perform - what if 7 analysis and 10x the cificacy of your master budects: if expected unit sales for 92 is 7,000 shirts, net operating income shorell be 5177.218 SU DEVHIS SWEATSHIRT C.ALOKE. Aferer gratuating from Sourthocesem University in Colloge Town. USA with a degree in business, Eituae Johnsion realiacd that he wanted to remain in Colloge Tewn. After a number of unsuceessful attempts at getting a job in lis discipline, Fithan decided to go ieta business for hittwelf. In thinkieg about his business veatsec. Fthan deteraninad that he bad four eriteria foe the nsw busincos. - Fird, be wankdd to do womething that he woull cnjoy. - Secoed, he wanted a besiness that would give back to the community- - Third, he wased a businces thal would grow and be mute isececeviul etery year - Foarth, realizing that he was going to have to work very hard, Ethan wanted a business that woeld generate s minimum ect income of 5150.000 annually: While reflecting on the crileria he kad callined. Ethan, who had hece prosident ef tais fratemity and served as an officer in several other stasknt organizations, tealized that there was tos place as Cellege Towa wo have custom sweatdirts made anithy a silk-mereeti proces. When student orgatizatiots wanted sw catshirts for their members or to maket en campos, the officers had to make a trip 10 a city 100 miles away 10 visit Fhirs and Mere." Ethan had worked as a part-time employee at Shirs and Moee while he was in high school and had cmisiceod ewning swch a shop. He realizod that a sw catshirt stop in College Town had the pocential so mect all four crilcria he had set. Ethan set op an appoctement with Jayne Sted, the ouncr of Stits and More, to cotain informaticen useful is getiing his shep started. Because Jayne liked Ethan and was intrigaed by him entrepresseurial spirit, she answered many of Fthan's questions. In addition, 1ayne provided information concerning the rype of eqpaipmean Ethan would necd for his busisess and the average uscful hife. Jyyne knows a competinor who is rctiring and wosld like to sell his cyajpencm. Ethan can purchase the cquipment at the begineing of 2023 and the owner is willing to give him tenms of 50 , dee ugoe purthase and 50 his tue the quarter following the purchasc. Ethan will purthase the following equipenent January 1. 2023: Ethat will use the straight-line method (assuming no resudual valuci ia rocond the stepreciation of the equipencat. Ithan has decided wo use the sueatshint supplier recommended by leyne. He learned the purchase cost per saveatshirt to be sitk-screened (direct materials) would be $12.40. fapne encouraged Ethan to maintain an ending invenlory of shists equal to 25% of the accil encouraged Eitan 70 maintain an ending inverilory of ahints cepal to 25% of the ned quarter's sales. Jayne has also encouraged Ethan to ask the swcatshirt sapplier for terms of aons of a quarter's parchaves to be paid in the qaarter of parchave with the remaining 60. of the quarter's parthaves to be paid in the quarter following the perchase Ethan also leamed frum talking with Jayte that the ink (indirect malerials) used in the alk-screen poocest costs appectumately $.8 Z per shirt. Knowing that the silk-sereen prosess is somerehat labor intensive, Ethan plans to hire college students to help with the silk wereen prosess with the wage nate of $13.5 per hear. Each shirt needs approainaicty 0.1 hovirs 10 complete the silk-screen process. In addition, Ethan will niced one porvon lo take orders. bell cusemorn, and perale the each register. Mary Ann Smith, who is currenty Ditecter of Siludet Developenent at Fabsern University, has appeoached Fithan ahenit a job in ales. Mary Ama knews the edisecr of all of the student ofgaciratices on College. In aldition, she is very active in the community. Ethan thiniss Mary Ane can bring in a ke of busaness, In adsirice sbe alse has the clerical shils needed for the position. Because of her contacts, Ethan is willing to pay Mary Ame $1,220 per month plus a commission of 8.64 is of cales revenuce. Fithas realizes that he will have difficulty in finding a pervat akillod in computer praphics 6o gentrale the designs to be printed ee the shimbs. Jayne resently hired a jraphics desigher in thet powition for \$hirts and More at a rate of $630 per motel plas $.24 for each shist priased. Fituan believer he can find a university graphics devige shadest oo work for the same rate layne is paying her decipner. Lthan was fortunate in finding a commercial terilding for ent near the Universiry and the dowmens arca. The landlord requires a onc-ycar lease. Althonch the monthly ront of SI, 500 is moee than Ethan had anticiguied paying, the building is nice, has adogate parking, and there is room for exjunsion. Ethat anticigates that 75% of the trildite will be used in the silk-serees prosess while 25% with be used for sales. Eihaid's finternity beothers have cnoournged him wo advertise wedly in the Easiern University student newspaper. Upon inquining Lthan fornd that a 313ad would wost 528 per week. Ethan alse plans to run a weckly ad in the local new super that will cost him 580 per wock. (Note; Use 52 wecks per year invtead of 4 werks per mesth ) E thas wants to sell a large mumber of qualify shirts af a rezsonable cost. Hic extimales the selling prise of each suslomised shirt to be 526.5. All sales are credit sales. Iayne has suggeved that he should ask caceesers to pay for 50 in of their jortanes in the quater purchased, 35% in the quarter following the porchases, asd 15% in the second quarer following the purchases. Since Ethan will be making cestom sweatshirts, he is not kecping any extra inventory of fimiched shirts. AAcr talking to the insurance agent and the groperty valuation administrator in his manticipaliry. F:thas estimates that the peoperty taxes and itsarance os the mactiatery will cost \$2,640 annually. while peoperty tax and insurasce on display facniture and cash regioter will tocal $420 annsally. The property taxes will be paid cach qaarter Jayne reminded Ethan that maintenaces of the machines is requied for the silk-sctwen process. In addition. Ethan realixes that he must consider the coed of utilitick. The and More. Fa addition, Shirs and More sells approximiately the same number of shirth Ethat plans to sell in his store. Therefore, Eithan is confident that the maintenance and utility cests for his shop will be comparable to the maintenance and atility coes for Stans and Mose as below, Ethan will use the rearrosion method to estimute variable and fixad costs for the purpose of bodgeting. (Node, Whew estimsting mainteranse and unility costs, input data en a norksheyt and we Excel Data Analysis fitncticers to estimate the slope and the inferceje, When yor budget maintenaece and unlity costs, you should ane foemulas lecked ta the estimates of slope and intereept, and related quantify of shins, indead of inpuming numbers manually. Use Zwcel to display the total costs to the whole dollar.) Fthas cstimates the number of shirts so be sold in the fins five quericre, beginning January 2021 , to be: Secing how determined his son was so becume an entreptencur. Eithat's father offered to co-sign a note for an amount up to 536,000 wo help Fithan open his sweasdhir shop, Sun Derils Sweatshirt Galore. The loan officer advised Eathan that the insicrest cate en a 12. Devils Swearshirt Galore. The loan officer advised Ethan that the inerest rate cen 12month loan would be 8 percent. Ethan copects the loan to be taken out 3muary 1,2023 and paid bock, aloeg with interct eypense, on Docentice 31,2023. Preparatien of Spreadshect File Create the fotlowing six yeparate workbects: Sheet I: Regressien Results Present the regersion results firs ceximation of maintenance and utility costs. Sheet 3. Iselude the following nev budgets on the third worksheet, clearly lakelad: Sheet 4. Lelude the following rwe budgets on the fourth work wheet, cleady lakeled. Silk-Screen Labor Prepare a labor budect, in laboe twours Dedect and in lotal dollars, for cach quarler and foe the year a total. Silk-4creen Overhed Propace an werhead tradzes fir each Bedget quarice and for the year in total. List the overbeal items in the follesing order Yariable Overtheratcons itreluding ind. maitienanes, inilities, graphics design, abal subtutal of variable costs; Eised Qusthod Costs inclading rent, maimenance, utilities, graphics design, property taves and insumance. dopreciation, and subitotal of fised costs: 5. Itelude the following treo budgets an the fift weekshect, clearly labeled: 6. Include the following on the sivth worksheet, clearly labeled: Bedgeted 1ncome Tropare a huslgeted anceme isatement Siatement threagh net operaties inceese (interest expense escluded) for 2023 in ghe centribation marsin furm to Chupter 7 or 8 ). Target Profit Analysix Use Exeel focmula to conpute bow many chits Eithan woeld have to sell to care a target net operatitg aweete of 5000,000 for 2023