Sheet 3: Include the following two budgets on the third worksheet, clearly labeled: Shirts Purchase (Direct Material Purchase) Budget Prepare a shirts purchase budget, in units and in total dollars, for each quarter and for the year in total. (Tip: Production is the same as sales, i.e., no finished goods inventory) Schedule of Expected Cash Payments for Shirts Purchases Prepare a schedule of cash payments for shirts purchases for each quarter and for 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. 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 2022 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, 2022: Useful Cost Life Hand operated press that applies ink to the shirt $8,700 Light exposure table 1,400 Dryer conveyer belt that makes ink dry on the shirts 2,640 Computer with graphics software and color printer 3,760 Display furniture 2,400 Used cash register 1,620 . 5 yrs 10 yrs 10 yrs 4 yrs . 10 yrs 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.80. Jayne encouraged Ethan to maintain an ending inventory of shirts equal to 25% of the next quarter's sales. Jayne has also encouraged Ethan to ask the sweatshirt supplier for terms of 40% of a quarter's purchases to be paid in the quarter of purchase with the remaining 60% of the quarter's purchases to be paid in the quarter following the purchase. Ethan also learned from talking with Jayne that the ink (indirect materials) used in the silk-screen process costs approximately $.80 per shirt. Knowing that the silk-screen process is somewhat labor intensive, Ethan plans to hire college students to help with the silk-screen process with the wage rate of $12.5 per hour. Each shirt needs approximately 0.1 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 University, has approached Ethan about a job in sales. Mary Ann knows the officers 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,220 per month plus a commission of 8.4% of sales revenues. Ethan realizes that he will have difficulty in finding 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 Shirts and More at a rate of $630 per month plus $.24 for each shirt printed. Ethan believes he can find a university graphics design student to work for the same rate Jayne is paying her designer. Ethan was fortunate in finding a commercial building for rent near the University and the downtown area. The landlord requires a one-year lease. Although the monthly rent of $1,500 is more than Ethan had anticipated paying, the building is nice, has adequate parking, and there is room for expansion. Ethan anticipates that 75% of the building will be used in the silk-screen process while 25% will be used for sales. Ethan's fraternity 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 $80 per week. (Note: Use 52 weeks per year instead of 4 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 $22.5. All sales are credit sales. Jayne has suggested that he should ask customers to pay for 50% of their purchases in the quarter purchased, 35% in the quarter following the purchases, and 15% in the second quarter following the purchases. Since Ethan will be making custom sweatshirts, he is not keeping any extra inventory of finished shirts. 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,640 annually, while property tax 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 addition, Shirts and More sells approximately the same number of shirts Ethan plans to sell in his store. Therefore, Ethan is confident 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 regression method to estimate variable and fixed costs for the purpose of budgeting. LITT January February March April May June July August September October November December Shirts Sold 1,850 2,120 2,630 3,250 5,000 5,200 3,920 2,100 8,000 6,810 6,020 8,700 Maintenance Cost $1,630 1,720 1,740 1,780 1,912 1,986 1,800 1,725 2,120 2,030 2,014 2,260 Utility Cost $1,150 1,188 1,161 1,211 1,485 1,513 1,288 1,143 1,688 1,630 1,498 1,720 (Note: When estimating maintenance and utility costs, 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 first five quarters, beginning January 2022, to be: . First quarter 2022 Second quarter 2022 Third quarter 2022 Fourth quarter 2022 First quarter 2023 4,500 6,700 9,100 7,800 8,200 . 0 Seeing how determined his son was to become an entrepreneur, 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 8 percent. Ethan expects the loan to be taken out January 1, 2022 and paid back, along with interest expense, on December 31, 2022. Preparation of Spreadsheet File Sheet 3: Include the following two budgets on the third worksheet, clearly labeled: Shirts Purchase (Direct Material Purchase) Budget Prepare a shirts purchase budget, in units and in total dollars, for each quarter and for the year in total. (Tip: Production is the same as sales, i.e., no finished goods inventory) Schedule of Expected Cash Payments for Shirts Purchases Prepare a schedule of cash payments for shirts purchases for each quarter and for 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. 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 2022 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, 2022: Useful Cost Life Hand operated press that applies ink to the shirt $8,700 Light exposure table 1,400 Dryer conveyer belt that makes ink dry on the shirts 2,640 Computer with graphics software and color printer 3,760 Display furniture 2,400 Used cash register 1,620 . 5 yrs 10 yrs 10 yrs 4 yrs . 10 yrs 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.80. Jayne encouraged Ethan to maintain an ending inventory of shirts equal to 25% of the next quarter's sales. Jayne has also encouraged Ethan to ask the sweatshirt supplier for terms of 40% of a quarter's purchases to be paid in the quarter of purchase with the remaining 60% of the quarter's purchases to be paid in the quarter following the purchase. Ethan also learned from talking with Jayne that the ink (indirect materials) used in the silk-screen process costs approximately $.80 per shirt. Knowing that the silk-screen process is somewhat labor intensive, Ethan plans to hire college students to help with the silk-screen process with the wage rate of $12.5 per hour. Each shirt needs approximately 0.1 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 University, has approached Ethan about a job in sales. Mary Ann knows the officers 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,220 per month plus a commission of 8.4% of sales revenues. Ethan realizes that he will have difficulty in finding 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 Shirts and More at a rate of $630 per month plus $.24 for each shirt printed. Ethan believes he can find a university graphics design student to work for the same rate Jayne is paying her designer. Ethan was fortunate in finding a commercial building for rent near the University and the downtown area. The landlord requires a one-year lease. Although the monthly rent of $1,500 is more than Ethan had anticipated paying, the building is nice, has adequate parking, and there is room for expansion. Ethan anticipates that 75% of the building will be used in the silk-screen process while 25% will be used for sales. Ethan's fraternity 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 $80 per week. (Note: Use 52 weeks per year instead of 4 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 $22.5. All sales are credit sales. Jayne has suggested that he should ask customers to pay for 50% of their purchases in the quarter purchased, 35% in the quarter following the purchases, and 15% in the second quarter following the purchases. Since Ethan will be making custom sweatshirts, he is not keeping any extra inventory of finished shirts. 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,640 annually, while property tax 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 addition, Shirts and More sells approximately the same number of shirts Ethan plans to sell in his store. Therefore, Ethan is confident 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 regression method to estimate variable and fixed costs for the purpose of budgeting. LITT January February March April May June July August September October November December Shirts Sold 1,850 2,120 2,630 3,250 5,000 5,200 3,920 2,100 8,000 6,810 6,020 8,700 Maintenance Cost $1,630 1,720 1,740 1,780 1,912 1,986 1,800 1,725 2,120 2,030 2,014 2,260 Utility Cost $1,150 1,188 1,161 1,211 1,485 1,513 1,288 1,143 1,688 1,630 1,498 1,720 (Note: When estimating maintenance and utility costs, 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 first five quarters, beginning January 2022, to be: . First quarter 2022 Second quarter 2022 Third quarter 2022 Fourth quarter 2022 First quarter 2023 4,500 6,700 9,100 7,800 8,200 . 0 Seeing how determined his son was to become an entrepreneur, 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 8 percent. Ethan expects the loan to be taken out January 1, 2022 and paid back, along with interest expense, on December 31, 2022. Preparation of Spreadsheet File