After talking to the insurance agent and the property valuation administrator in his municipality. Ethan estimates that the property taxes and insurance on the machinery will cost $2.040 annuallyI while property tart and insurance on display furniture and cash register will total $420 annually. The property taxes will be paid each quarter. Jayne reminded Ethan that maintenance of the machines is required for the silk-screen process. In addition, Ethan realizes that he must consider the cost of utilities. The building Ethan wants to rent is roughly the same size as the building occupied by Shirts and More. In additionI Shirts and More sells approximately the same number of shirts Ethan plans to sell in his store. Therefore, Ethan is condent that the maintenance and utility costs for his shop will be comparable to the maintenance and utility costs for Shirts and More as below. Ethan will use the Egregm' I1 mgtbod to estimate variable and xed costs for the purpose of budgeting. Shirts Sold Maintenance Cost Utility Cost January 1.310 $1,030 $1,150 February 2,110 1,220 1.153 March 2,1530 1.240 1,115] April 3,250 1,730 1,21 I May 5,000 1,912 1.435 June 5,300 1,950 1,502. July 3,920 1,305 1,223 August 2,1011 1.125 1,143 September 3.000 2,120 1 .1533 October 6.810 2,030 1.630 November I0,020 2,014 1,493 December 3,700 2,240 1,740 (Note: When estimating maintenance and utility costsI input data on a worksheet and use Excel functions to estimate the slope and the intercept. When you budget maintenance and utility costs. you should use formulas linked to the estimates of slope and intercept, and related quantity of shirts. instead of inputting numbers by hand. Use Excel to display the total costs to the whole dollar.) Ethan estimates the number of shirts to be sold in the rst ve quarters, beginning January 2021I to be: a First quarter 2021 4.600 c Second quarter 2021 6,000 1- Third quarter 2021 0.100 a Fourth quarter 2021 2.500 a First quarter 2022 8300 Seeing how determined his son was to become an enuepreneur, Ethan's father offered to co-sign a note for an amount up to $35,000 to help Ethan open his sweatshirt shop. Sun Devil Sweatshirt Galore. The loan officer advised Ethan that the interest rate on a 12- month loan would be 6.5 percent. Ethan expects the loan to be taken out January 1, 2021 and paid back, along with interest expense, on December 31, 2021. Preparation of Spreadsheet File Create the following six separate worksheets: Sheet 1: Regression Results Present the regression results for estimation of maintenance and utility costs. Sheet 2: Include the following two budgets on the second worksheet, clearly labeled: Sales Budget Prepare a sales budget for each quarter and for the year in total. Cash Collections Budget Prepare a cash collections budget for each quarter and for the year in total. List cash collections in the following order: First-quarter sales, second-quarter sales, third-quarter sales, fourth-quarter sales, and total cash collections. Sheet 3: Include the following two budgets on the third worksheet, clearly labeled: Shirts Purchase (Direct Prepare a shirts purchase budget, in Material Purchase) units and in total dollars, for each Budget quarter and for the year in total. (Note: Production is the same as sales, i.e., no finished goods inventory) Schedule of Expected Cash Prepare a schedule of cash payments for Payments for Shirts shirts purchases for each quarter and for Purchases the year in total. List cash disbursements in the following order: First-quarter purchases, second-quarter purchases, third-quarter purchases, fourth-quarter purchases, and total cash payments. Sheet 4: Include the following two budgets on the fourth worksheet, clearly labeled: Silk-Screen Labor Prepare a labor budget, in labor-hours Budget and in total dollars, for each quarter and for the year in total. Silk-Screen Overhead Prepare an overhead budget for each Budget quarter and for the year in total. List the 6SUN DEVIL SWEATSHIRT GALORE After graduating from Southwestern University in College Town, USA with a degree in business, Ethan Johnson realized that he wanted to remain in College Town. After a number of unsuccessful attempts at getting a job in his discipline, Ethan decided to go into business for himself. In thinking about his business venture, Ethan determined that he had four criteria for the new business. First, he wanted to do something that he would enjoy. Second, he wanted a business that would give back to the community. Third, he wanted a business that would grow and be more successful every year. Fourth, realizing that he was going to have to work very hard, Ethan wanted a business that would generate a minimum net income of $100,000 annually. While reflecting on the criteria he had outlined, Ethan, who had been president of his fraternity and served as an officer in several other student organizations, realized that there was no place in College Town to have custom sweatshirts made using a silk-screen process. When student organizations wanted sweatshirts for their members or to market on campus, the officers had to make a trip to a city 100 miles away to visit "Shirts and More." Ethan had worked as a part-time employee at Shirts and More while he was in high school and had envisioned owning such a shop. He realized that a sweatshirt shop in College Town had the potential to meet all four criteria he had set. Ethan set up an appointment with Jayne Stoll, the owner of Shirts and More, to obtain information useful in getting his shop started. Because Jayne liked Ethan and was intrigued by his entrepreneurial spirit, she answered many of Ethan's questions. In addition, Jayne provided information concerning the type of equipment Ethan would need for his business and the average useful life. Jayne knows a competitor who is retiring and would like to sell his equipment. Ethan can purchase the equipment at the beginning of 2021 and the owner is willing to give him terms of 50% due upon purchase and 50% due the quarter following the purchase. Ethan will purchase the following equipment January 1, 2021: Useful Cost Life Hand operated press that applies ink to the shirt $8,500 5 yrs Light exposure table 1,400 10 yrs Dryer conveyer belt that makes ink dry on the shirts 2,600 10 yrs . . Computer with graphics software and color printer 3,760 4 yrs Display furniture 2,400 10 yrs Used cash register 1,100 5 yrs Ethan will use the straight-line method (assuming no residual value) to record the depreciation of the equipment. Ethan has decided to use the sweatshirt supplier recommended by Jayne. He learned the purchase cost per sweatshirt to be silk-screened (direct materials) would be $10.20. Jayne 3encouraged Ethan to maintain an ending inventory of shirts equal to 25% of the next quarter's 5-3165. Jayne has also encouraged Ethan to ask the sweatshirt supplier for tennis cf4% of a quarter's purchases to be paid in the quarter of purchase with the remaining % of the quarter's purchases to be paid in the quarter following the purchase. Ethan also learned 'om talking with Jayne that the ink {indirect materials) used in the silk-screen process costs approximately SLED per shirt. Knowing that the silkvscreen process is somewhat labor intensive. Ethan plans to hire college students to help with the silk-screen process with the wage rate of $1 1.2 per hour. Each shirt needs approximately lll] hours to complete the silk-screen process. In addition. Ethan will need one person to take orders, bill customers, and operate the cash register. Mary Ann Smith. who is currently Director of Student Development at Eastern UniversityI has approached Ethan about a job in sales. Mary Ann knows the ofcers of all of the student organizations on College. In addition. she is very active in the community. Ethan thinks Mary Ann can bring in a lot of business. In addition she also has the clerical skills needed for the position. Because of her contacts, Ethan is willing to pay Mary Ann $1,24ll per month plus a commission of 8.9% of sales revenues. Ethan realizes that he will have difculty in nding a person skilled in computer graphics to generate the designs to be printed on the shirts. Jayne recently hired a graphics designer in that position for Shins and More at a rate of Hill) per month plus $.24 for each shirt printed. Ethan believes he can nd a university graphics design student to work for the same rate Jayne is paying her designer. Ethan was fortunate in nding a commercial building for rent near the University and the downtown area. The landlord requires a oneayear lease. Although the monthly rent of $1.5m is more titan Ethan had anticipated payingI the building is nice. has adequate parking, and there is room for expansion. Eian anticipates that T5513 of the building will be used in the silk~screen process while 25% will be used for sales. Ethan's 'aternity brothers have encouraged him to advertise weekly in the Eastern University student newspaper. Upon inquiring Ethan found that a 3" x 3\" ad would cost $28 per week. Ethan also plans to run a weekly ad in the local newspaper that will cost him it] per week. {Nute: Use 52 weeks per year instead ufd weeks per month} Ethan wants to sell a large number of quality shirts at a reasonable cost. He estimates the selling price of each customized shirt to be 321.5. All sales are credit sales. Jayne has suggested that he should ask customers to pay for 513% of their purchases in the quarter purchasedI 35% in the quarter following the purchases, and 15% in the second quarter following the purchases. Since Ethan will be making custom sweatshirts, be is not keeping any extra inventory of nished shirts. overhead items in the following order: Variable Overhead Costs, including ink, maintenance, utilities, graphics design, and subtotal of variable costs; Fixed Overhead Costs, including rent, maintenance, utilities, graphics design, property taxes and insurance, depreciation, and subtotal of fixed costs; and finally, Total Overhead Costs. Sheet 5: Include the following two budgets on the fifth worksheet, clearly labeled: Selling and Prepare a selling and administrative Administrative Expenses expenses budget for each quarter and for Budget the year in total. List the cost items in the following order: Variable S&A Expenses, including sales commissions and subtotal of variable expenses; Fixed S&A Expenses, including advertising, rent, salaries, property taxes and insurance, depreciation, and subtotal of fixed expenses; and finally, Total Selling and Administrative Expenses. Cash Payments Budget Prepare a cash payments budget and and Combined Cash combined cash budget for each quarter Budget and for the year in total. List clearly each type of cash payments (for shirts purchases, labor, overhead, and S&A, and so on). Note: Depreciation does not require a cash payment. Sheet 6: Include the following on the sixth worksheet, clearly labeled: Budgeted Income Prepare a budgeted income statement Statement through net operating income (interest expense excluded) for 2021 in the contribution margin format. Target Profit Analysis Use Excel formula to compute how many shirts Ethan would have to sell to earn a target net operating income of $150,000 for 2021. 7