Objective: Demonstrate knowledge of accounting and business by analyzing a case and applying the accounting concepts you have learned to date. Improve ability to work with a tea. . This project requires you to primarily apply Chapter 9 materials and complete the master budgets using Excel. Additionally, you will perform some basic target profit analysis.

Project Submission

When you have completed your file, please submit it in the following format:

1. You must use formulas and functions in your file such that when an input (for instance, sales volume) is changed, all of the worksheets generate new outputs. You will lose 1 to 5 points for any functional deficiencies, depending on the severity of deficiencies.

2. The worksheets must be in the order as specified, or points will be deducted.

Other information

1. You have been exposed to Excel. You should already know how to link worksheets and enter cell formulas. Since all budgets are based on sales estimates, all work sheets should be linked to sales estimates. If sales estimate changes, all the other worksheets should automatically adjust to the changes. The shirts purchase, direct labor, and manufacturing overhead costs will change, etc. The cells on Cash Budget and budgeted Income Statement and budgeted Balance Sheet, while may not be directly linked to sales estimates, should be linked to the cells in other worksheets as well.

2. Check figures: the total manufacturing overhead is $187,309; the ending cash balance is $46,284; operating income is $171,295.

1

SCREEN THREADS

After graduating from Southwestern University in College Town, USA with a degree in business, Pablo Casanova realized that he wanted to remain in College Town. After a number of unsuccessful attempts at getting a job in his discipline, Pablo decided to go into business for himself. In thinking about his business venture, Pablo 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, Pablo wanted a business that would generate a minimum operating income of $200,000 annually.

  While reflecting on the criteria he had outlined, Pablo, 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. Pablo 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. Pablo set up an appointment with Julia Selby, the owner of Shirts and More, to obtain information useful in getting his shop started. Because Julia liked Pablo and was intrigued by his entrepreneurial spirit, she answered many of Pablos questions.

  In addition, Julia provided information concerning the type of equipment Pablo would need for his business and the average useful life. Julia knows a competitor who is retiring and would like to sell his equipment. Pablo can purchase the equipment at the beginning of 2022 and the owner is willing to give him terms of 50% due upon purchase and 25% due the quarter following the purchase, and 25% due the second quarter following the purchase. Pablo will purchase the following equipment January 1, 2022:

Equipment Purchase	Cost	Useful Life Years
Hand operated press that applies ink to shirt	$8500	5
Screens, other smaller items	$2400	2
Light Exposure table	$1400	10
Dryer conveyer belt that makes ink dry on the shirts	$2600	10
Computer graphics software and color printer	$3760	4
Display furniture	$2400	10
Used Cash register	$1100	5

Pablo will use the straight-line method (assuming no residual value) to record the depreciation of the equipment. Pablo has decided to use the sweatshirt supplier recommended by Julia. He learned the purchase cost per sweatshirt to be silk-screened (direct materials) would be $16.50. Julia encouraged Pablo to maintain an ending inventory of shirts equal to 10% of the next quarters sales.

Julia has also encouraged Pablo to ask the sweatshirt supplier for terms of 40% of a quarters purchases to be paid in the quarter of purchase with the remaining 60% of the quarters purchases to be paid in the quarter following the purchase.

Pablo also learned from talking with Julia that the ink (indirect materials) used in the silk-screen process costs approximately $1.75 per shirt.

Knowing that the silk-screen process is somewhat labor intensive, Pablo plans to hire college students to help with the silk-screen process with the wage rate of $13.50 per hour. Each shirt needs approximately 0.2 hours to complete the silk-screen process.

In addition, Pablo will need one person to take orders, bill customers, and operate the cash register. Alexa Dumas, who is currently Director of Student Development at South western, has approached Pablo about a job in sales. Alexa knows the officers of all of the student organizations on College. In addition, she is very active in the community. Pablo thinks Alexa can bring in a lot of business. Additionally, she also has the clerical skills needed for the position. Because of her contacts, Pablo is willing to pay Alexa $3,000 per month plus a commission of 2.5% of sales revenues.

Pablo realizes that he will have difficulty in finding a person skilled in computer graphics to generate the designs to be printed on the shirts. Julia recently hired a graphics designer in that position for Shirts and More at a rate of $4,000 per month plus $.25 for each shirt printed. Pablo believes he can find a recently graduated graphics design student to work for the same rate Julia is paying her designer.

Pablo 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 $3,000 is more than Pablo had anticipated paying, the building is nice, has adequate parking, and there is room for expansion. Pablo anticipates that 75% of the building will be used in the silk-screen process while 25% will be used for sales.

Pablos fraternity brothers have encouraged him to advertise weekly in the Eastern University student newspaper. Upon inquiring Pablo found that a 3 x 3 ad would cost $28 per week. Pablo 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.)

Pablo wants to sell a large number of quality sweatshirts at a reasonable cost. He estimates the selling price of each customized shirt to be $30. All sales are credit sales. Julia 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 Pablo 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, Pablo estimates that the property taxes and insurance on the machinery will cost $2,400 annually, while property tax and insurance on display furniture and cash register will total $360 annually. The property taxes will be paid each quarter.

Julia reminded Pablo that maintenance of the machines is required for the silk-screen process. In addition, Pablo realizes that he must consider the cost of utilities. The building Pablo 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 Pablo plans to sell in his store. Therefore, Pablo 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. Pablo will use the regression method to estimate variable and fixed costs for the purpose of budgeting.

Month	Shirts sold	Maintenance Cost	Utility Cost
Jan	2600	$890	$908
Feb	4000	$956	$1114
Mar	4240	$983	$1127
Apr	1440	$810	$825
May	1688	$860	$869
June	2104	$870	$871
July	3136	$903	$959
Aug	1672	$853	$838
Sept	4800	$1060	$1266
Oct	4086	$1015	$1223
Nov	4214	$1007	$1124
Dec	5220	$1120	$1305

(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.)

Pablo estimates the number of shirts to be sold in the first five quarters, beginning January 2022, to be:

First Qrt 2022	10840
Second Qrt 2022	11208
Third Qrt 2022	5232
Fourth Qrt 2022	13520
Fifth Qrt 2023	11924

Seeing how determined his son was to become an entrepreneur, Pablos father offered to co-sign a note for an amount up to $40,000 to help Pablo open his sweatshirt shop, Screen Threads. The loan officer advised Pablo that the interest rate on a 12-month loan would be 9.5 percent. Pablo expects the loan to be taken out January 1, 2022 and paid back, along with interest expense, on December 31, 2022. Pablo will also invest $10,000 of his own saved money.

Preparation of Spreadsheet File

Create the following seven 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 Material Purchase) Budget: Prepare a shirts purchase budget, in units and in total dollars, for each quarter and for the year in total. (Note: 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.

Sheet 4:

Include the following two budgets on the fourth worksheet, clearly labeled:

Silk-Screen Labor: Prepare a labor budget, in labor- Budget hours and in total dollars, for each quarter and for the year in total.

Silk-Screen Overhead Budget:

Prepare an overhead budget for each quarter and for the year in total. List the 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 Administrative Expenses Budget: Prepare a selling and administrative expenses budget for each quarter and for 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 and Combined Cash Budget: Prepare a cash payments budget and combined cash budget for each quarter 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 Statement: Prepare a budgeted income statement through net operating income (interest expense excluded) for 2022 in the contribution margin format.

Target Profit Analysis: Use Excel formula to compute how many shirts Pablo would have to sell to earn a target net operating income of $200,000. Show steps of your calculation.

Sheet 7: Include the following on the seventh worksheet, clearly labeled: Budged Balance Sheet for the year ended 12/31/22