PLEASE DO IN EXCEL IF POSSIBLE. WHATEVER IS EASIER BUT INCLUDE NUMBERS AND POSSIBLY GENERIC FORMULAS FOR HOW IT WAS FOUND. Anything helps

Sweats Galore, Inc. 1 The Business Situation After graduating with a degree in busiress from Eastern University in Campus Town, USA, Michael Woods realized that he wanted to remain in Campus Town. After a number of unsuccessful attempts at getting a job in his discipline, Michael decided to go into business for himself. In thinking about his business venture. Michael determined that he had four criteria for the new business: 1. He wanted to do something that he would enjoy. 2. He wanted a business that would give back to the connmunity, 3. He wanted a business that would grow and be more successful every year. 4. Realizing that he was going to have to work very hard. Michael wanted a business that would generate a minimun net income of $25,100 annually. While reflecting on the criteria he had outlined, Michael, who had been president of his fraternity and served as an officer in several other student organizations, realized that there was no place in Campus Town to have custom sweatsharts made using a silk-screen process. Wben student organizations wanted sweatshirts for their members of to market on campus, the officers had to make a trij to a city 100 males away to visit "Shirts and More," Michael had worked as a part-time employee at Shirts and More while he was in high school and had envisioned owning such a skop. He realized that a sweatshart shop in Campus Town had the posential to meet all four of his criteria. Michael wet up an appointment with Jayne Stoll, the owner of Shirts and More, to ohtain information useful in getting his shop started. Because Jayne liked Michael and was intrigued by his entrepreneurial spirit, she answered many of Michael's questions. In addition, Jayne provided information coneerning the type of equipment Michael would need foe his business and its average useful life. Jayne knows a competitor who is retiring and would like to sel1 his equipenent. Michael can purchase the equipnent at the beginning of 2023 , and the owner is witling to give ham terms of 50% due upon purchase and 50% due the quarter following the purchase. Michacl decided to purchase the following equaptient as of January 1,2023. 'Modfified from Sweats Cialore, isc. Developed by Sessica Jabswn Fratier, Eastern Kernacky University, and Patricin H. Mownee, Chiversity of Contral Arkaesas Michael has decided to use the sweatshirt supplier recoenmended by Jayre. He learned that a good-quality sweatshir to be silk-screened would cost $8 each. Jayne has encouraged Michael to ask the sweatshirt supplier for terms of 30% of a quarter's purchases to be paid in the quarter of purchase, with the remaining 70% of the quarter's purchases to be paid in the quarter following the purchase. Michael also learred from talking with Jayne that the ink used in the silkscreen process costs appeoximately $0.80 per shirt. Knowing that the silk-screen process is sonewhat labor-intensive, Michael plans to hire six college students to help with the process. Michael estimates the hourly wage rate for the workers to be $15.5 and estimates it will take 30 mannutes (0.5 hour) to silkwereen sae shart. In addition, Michael will need one person to take orders, bill customers, and operate the cash register, Cary Sue Smith, who is currently Director of Student Development at Bastern University, has approeched Michacl aboet a job in sales. Cary Sue knows the officers of all of the student organizations on campus. In addition, she is very active in the community. Michael thanks Cary Sue can bring in a lot of basiesss. In addition, she also has the clerical skills needed for the position. Because of her contacts, Michacl is witling to pay Cary Sue \$1,100 per month plus a commassion of 10% of sales. Michacl realines that he will have difficulty finding a perwon skilled in computer graphies to generate the designs to be printed on the shirts. Jayre recently hired a graphics designer in that position for Shirts and More at a fate of $400 per moneth ples $0.10 for each shirt printed. Michacl believes he can find a university graphies design student to work for the same rate Jayne is peying her designer. Michael was foetunate to find a commercial buiding for fent near the university and the downtown area. The landlord requires a ore-year lease. Although the moathly rent of $1.500 is more than Michael had anticipated paying, the building is nice, has adequate parking. and there is foom for expansion. Michacl anticipates that 75% of the buikding will be used in the silk-sereen process and 25% will be used for sales. Michael's fraternity brothers have encouraged him to advertise weckly in the Eastem University student newspaper. Upon inquiring, Michacl found that a 3 inch x3 inch ad would cost $970 per quarter. Michael wants to sell a large number of quality shirts at a reasonable price. He estimates the selling price of each customined shirt to he $20. Below is the expected sales unit: Michael learned from talking with Jayne that the supplier is so focused on making quality sweatshirts that many times the shirts are not available for several days. She encouraged Michael to maintain an ending inventory of shirts equal to 35% of the next quarter's sales. Jayne has suggested that he should ask customers to pay for 60% of their purchases in the quarter purchased and pay the additional 40% in the quarter following the purchases. After talking with the insurance agent and the property valuation administrator in his municipality, Michael estimates that the property taxes and insurance on the machinery will cost $2,240 annually; property tax and insurance on display furniture and cash register will total $380 annually. Jayne reminded Michael that maintenance of the machines is required for the silk-screen process. In addition, Michael realizes that he must consider the cost of utilities. The building Michael wants to rent is roughly the same size as the building occupied by Shirts and More. In addition, Shirts and More sells approximately the same number of shirts Michael plans to sell in his store. Therefore, Michael is confident that the maintenance and utility costs for his shop will be comparable to the maintenance and utility costs for Shirts and More - $1,650/ month maintenance costs \& $1,000/ month utilities costs. Michael decides to establish his company as a corporation. He will invest $10,000 of his personal savings in the company. Seeing how determined his son was to become an entrepreneur, Michael's father offered to co-sign a note for an amount up to \$25,000 to help Michael open his sweatshirt shop, Sweats Galore, Inc. The loan officer advised Michael that the interest rate on a 12-month loan would be 8%. Michael expects the loan to be taken out as of January 1, 2023. Michael has estimated that his income tax rate will be 20\%. He expects to pay the total tax due when his returns are filed in 2023. Requirements: Part I: Based on the information given in the case study, prepare the following budgets using the provided excel template: - Sales Budget for year ending 2023 - Schedule of expected collections from customers - Shirt Purchases Budget for year ending 2023 - Schedule of expected payments for purchases - Silk-screen Labor Budget - Selling and Administrative Expenses Budget - Hint: not related to the manufacturing process (silk-screen) - Overhead Expenses Budget - Hint: related to the manufacturing process (silk-screen) - Budgeted Income Statement for the year ended December 31, 2023 - Hint: Cost of Goods Sold = \# of shirts to be silk-screened during the year*purchase price per shirt + labor costs + overhead costs - Cash Budget for the year ended December 31, 2023 - Budgeted Balance Sheet on December 31, 2023 - Hint: retained earnings = budgeted net income ** Depreciation Expense = Costs/Useful life (Assuming no salvage value)[Note that not all purchased equipments are used in the manufacturing process] ** Some information given in the case is monthly information, need to covert to quarterly information. Part II: Do you think Michael's new business will be successful? Please justify your answer. Part III: What advice would you give to him? Please be specific. Sweats Galore, Inc. 1 The Business Situation After graduating with a degree in busiress from Eastern University in Campus Town, USA, Michael Woods realized that he wanted to remain in Campus Town. After a number of unsuccessful attempts at getting a job in his discipline, Michael decided to go into business for himself. In thinking about his business venture. Michael determined that he had four criteria for the new business: 1. He wanted to do something that he would enjoy. 2. He wanted a business that would give back to the connmunity, 3. He wanted a business that would grow and be more successful every year. 4. Realizing that he was going to have to work very hard. Michael wanted a business that would generate a minimun net income of $25,100 annually. While reflecting on the criteria he had outlined, Michael, who had been president of his fraternity and served as an officer in several other student organizations, realized that there was no place in Campus Town to have custom sweatsharts made using a silk-screen process. Wben student organizations wanted sweatshirts for their members of to market on campus, the officers had to make a trij to a city 100 males away to visit "Shirts and More," Michael had worked as a part-time employee at Shirts and More while he was in high school and had envisioned owning such a skop. He realized that a sweatshart shop in Campus Town had the posential to meet all four of his criteria. Michael wet up an appointment with Jayne Stoll, the owner of Shirts and More, to ohtain information useful in getting his shop started. Because Jayne liked Michael and was intrigued by his entrepreneurial spirit, she answered many of Michael's questions. In addition, Jayne provided information coneerning the type of equipment Michael would need foe his business and its average useful life. Jayne knows a competitor who is retiring and would like to sel1 his equipenent. Michael can purchase the equipnent at the beginning of 2023 , and the owner is witling to give ham terms of 50% due upon purchase and 50% due the quarter following the purchase. Michacl decided to purchase the following equaptient as of January 1,2023. 'Modfified from Sweats Cialore, isc. Developed by Sessica Jabswn Fratier, Eastern Kernacky University, and Patricin H. Mownee, Chiversity of Contral Arkaesas Michael has decided to use the sweatshirt supplier recoenmended by Jayre. He learned that a good-quality sweatshir to be silk-screened would cost $8 each. Jayne has encouraged Michael to ask the sweatshirt supplier for terms of 30% of a quarter's purchases to be paid in the quarter of purchase, with the remaining 70% of the quarter's purchases to be paid in the quarter following the purchase. Michael also learred from talking with Jayne that the ink used in the silkscreen process costs appeoximately $0.80 per shirt. Knowing that the silk-screen process is sonewhat labor-intensive, Michael plans to hire six college students to help with the process. Michael estimates the hourly wage rate for the workers to be $15.5 and estimates it will take 30 mannutes (0.5 hour) to silkwereen sae shart. In addition, Michael will need one person to take orders, bill customers, and operate the cash register, Cary Sue Smith, who is currently Director of Student Development at Bastern University, has approeched Michacl aboet a job in sales. Cary Sue knows the officers of all of the student organizations on campus. In addition, she is very active in the community. Michael thanks Cary Sue can bring in a lot of basiesss. In addition, she also has the clerical skills needed for the position. Because of her contacts, Michacl is witling to pay Cary Sue \$1,100 per month plus a commassion of 10% of sales. Michacl realines that he will have difficulty finding a perwon skilled in computer graphies to generate the designs to be printed on the shirts. Jayre recently hired a graphics designer in that position for Shirts and More at a fate of $400 per moneth ples $0.10 for each shirt printed. Michacl believes he can find a university graphies design student to work for the same rate Jayne is peying her designer. Michael was foetunate to find a commercial buiding for fent near the university and the downtown area. The landlord requires a ore-year lease. Although the moathly rent of $1.500 is more than Michael had anticipated paying, the building is nice, has adequate parking. and there is foom for expansion. Michacl anticipates that 75% of the buikding will be used in the silk-sereen process and 25% will be used for sales. Michael's fraternity brothers have encouraged him to advertise weckly in the Eastem University student newspaper. Upon inquiring, Michacl found that a 3 inch x3 inch ad would cost $970 per quarter. Michael wants to sell a large number of quality shirts at a reasonable price. He estimates the selling price of each customined shirt to he $20. Below is the expected sales unit: Michael learned from talking with Jayne that the supplier is so focused on making quality sweatshirts that many times the shirts are not available for several days. She encouraged Michael to maintain an ending inventory of shirts equal to 35% of the next quarter's sales. Jayne has suggested that he should ask customers to pay for 60% of their purchases in the quarter purchased and pay the additional 40% in the quarter following the purchases. After talking with the insurance agent and the property valuation administrator in his municipality, Michael estimates that the property taxes and insurance on the machinery will cost $2,240 annually; property tax and insurance on display furniture and cash register will total $380 annually. Jayne reminded Michael that maintenance of the machines is required for the silk-screen process. In addition, Michael realizes that he must consider the cost of utilities. The building Michael wants to rent is roughly the same size as the building occupied by Shirts and More. In addition, Shirts and More sells approximately the same number of shirts Michael plans to sell in his store. Therefore, Michael is confident that the maintenance and utility costs for his shop will be comparable to the maintenance and utility costs for Shirts and More - $1,650/ month maintenance costs \& $1,000/ month utilities costs. Michael decides to establish his company as a corporation. He will invest $10,000 of his personal savings in the company. Seeing how determined his son was to become an entrepreneur, Michael's father offered to co-sign a note for an amount up to \$25,000 to help Michael open his sweatshirt shop, Sweats Galore, Inc. The loan officer advised Michael that the interest rate on a 12-month loan would be 8%. Michael expects the loan to be taken out as of January 1, 2023. Michael has estimated that his income tax rate will be 20\%. He expects to pay the total tax due when his returns are filed in 2023. Requirements: Part I: Based on the information given in the case study, prepare the following budgets using the provided excel template: - Sales Budget for year ending 2023 - Schedule of expected collections from customers - Shirt Purchases Budget for year ending 2023 - Schedule of expected payments for purchases - Silk-screen Labor Budget - Selling and Administrative Expenses Budget - Hint: not related to the manufacturing process (silk-screen) - Overhead Expenses Budget - Hint: related to the manufacturing process (silk-screen) - Budgeted Income Statement for the year ended December 31, 2023 - Hint: Cost of Goods Sold = \# of shirts to be silk-screened during the year*purchase price per shirt + labor costs + overhead costs - Cash Budget for the year ended December 31, 2023 - Budgeted Balance Sheet on December 31, 2023 - Hint: retained earnings = budgeted net income ** Depreciation Expense = Costs/Useful life (Assuming no salvage value)[Note that not all purchased equipments are used in the manufacturing process] ** Some information given in the case is monthly information, need to covert to quarterly information. Part II: Do you think Michael's new business will be successful? Please justify your answer. Part III: What advice would you give to him? Please be specific