Please finish the third part of this accounting exel from "Sales budget" to "sellings administrative budget"

Cost Basics'!D7= THE T-SHIRT PRINTING CO. COST BASICS TABLE 1: MATERIALS ITEM T-Shirt Quality Transfer Paper TOTAL COST ITEM Worker #1 Worker #2 TOTAL COST DESCRIPTION $40 for 100 Shirts $9.79 for 50 Sheets TABLE 2: DIRECT LABOR DESCRIPTION (per hour) $10.00 $10.00 TABLE 3: ASSETS ITEM Laser Printer Heat Press Cutter/Plotter Computer #1 Computer #2 TOTAL COST Cost (10 year useful life) $149 $225.99 $179.99 $1,300 $1,300 ITEM Management Salary Website Graphic Designer Power and light Indirect Materials Advertising and Marketing InDesign Illustrator TOTAL COST ITEM SELLING EXPENSES: Sales Salaries Expense Advertising Expense TOTAL SELLING EXPENSES TABLE 4: MANUFACTURING OVERHEAD DESCRIPTION $ 5,760 / month $ 4,200 / month $ 4,200 / month $ 990 / month $ 150 / month $ 270 / month $ 15.99 / month $ 15.99 / month TABLE 5: SELLING AND ADMINISTRATIVE EXPEN DESCRIPTION Per Quarter Cost Per Quarter Cost Per Quarter Cost ADMINSTRATIVE EXPENSES: Office Salary Expense Office Supplies Expense Miscellaneous Administrative Expense TOTAL ADMINISTRATIVE EXPENSES TOTAL SELLING AND ADMINISTRATIVE EXPENSES ITEM MANUFACTURING COSTS: Direct Materials Direct Labor Indirect Materials InDesign IIllustrator TOTAL VARIABLE COSTS Management Salary Website Graphic Designer Power and light Advertising and Marketing Selling and Administrative Expenses TOTAL FIXED COSTS Per Quarter Cost Per Quarter Cost Per Quarter Cost Per Quarter Cost Per Quarter Cost TABLE 6: FIXED AND VARIABLE COSTS DESCRIPTION VARIABLE COSTS: $0.59 $1.50 Per Quarter Cost $0.01 $0.01 2.16 FIXED COSTS Per Quarter Per Quarter Per Quarter Per Quarter Per Quarter Per Quarter Cost E T-SHIRT PRINTING CO. COST BASICS TABLE 1: MATERIALS COST PER UNIT $0.40 $0.19 $0.59 QUARTERLY COST $3,600.00 $1,710.00 $5,310.00 Formula $.40 x 9,000 units $.19 x 9,000 units note: 9K units produced each quarter TABLE 2: DIRECT LABOR COST PER UNIT $0.75 $0.75 $1.50 QUARTERLY COST $6,750.00 $6,750.00 $13,500.00 Formula $.75 x 9,000 units $.75 x 9,000 units 0.075 TABLE 3: ASSETS COST PER UNIT QUARTERLY COST Quarter Cost 9k units Dep per yr 4 quarters $0.01 $3.73 $0.01 $5.65 $0.01 $4.50 $0.01 $32.50 $0.01 $32.50 $0.05 $78.88 DEPRECIATION PER YEAR Cost of Item 10 yr useful life $14.90 $22.60 $18.00 $130.00 $130.00 $316 E 4: MANUFACTURING OVERHEAD COST PER UNIT $1.92 $1.40 $1.40 $0.33 $0.05 $0.09 $0.01 $0.01 $5.20 QUARTERLY COST $17,280.00 $12,600.00 $12,600.00 $2,970.00 $450.00 $810.00 $47.97 $47.97 $46,805.94 ELLING AND ADMINISTRATIVE EXPENSES DESCRIPTION $4,500 / month $275 / month Formula $1.92 x 9,000 units $1.40 x 9,000 units $1.40 x 9,000 units $.33 x 9,000 units $.05 x 9,000 units $.09 x 9,000 units $15.99 x 3 months $15.99 x 3 months note: 9K units produced each quarter COST $13,500.00 $825.00 $14,325.00 $14,650 / month $150 / month $300 / month $43,950.00 $450.00 $900.00 $45,300.00 $59,625.00 LE 6: FIXED AND VARIABLE COSTS DESCRIPTION VARIABLE COSTS: $0.59 $1.50 Per Quarter Cost $0.01 $0.01 2.16 FIXED COSTS $5750/month $4450/month $4450/month $1000/month 275/month Per Quarter Cost COST $5,310 $13,500 $450 $47.97 $47.97 $19,356 $17,280.00 $12,600 $12,600 $2,970 $810.00 $59,625.00 $105,885 hrs per unit Cost Basics'!E13=Divide( # of shirts to be sold per quarter: 9000 TABLE 7: Selling Price Unit Selling Price Break Even Point Profit Planning Contribution Margin Margin of Safety $30/ unit 3803 7353 $28/unit 2258 units Price Selling Price Units Sold/ Quarter $30.00 9000 Sales Contribution margin Sales per Unit Variable cost per Unit Contribution Margin Ratio % Fixed Cost Contibution Margin Break Even / Units Break Even / Sales Fixed Cost Target Profit Contribution Margin Profit Sales $30 $2.16 Break-Even Point Per Unit $11.77 $28 Profit Planning $11.77 $28 Unit 7353 : Selling Price Sales $270,000 $114,090 $100000 Profit 92% $155,900 $270,000.00 $28 0.928 $105,885 92% 3803 $114,090 t Planning 105885 $100,000 92% 220590 Sales Budget Q1 Budgeted Unit Sales Selling Price Total Sales Q2 9000 9000 30 270000 $30.00 $270,000.00 Production Budget Q1 Budgeted Unit Sales Add desired units of ending finished goods inventory Total needs Less units of begining finished goods inventory Required production in units Q2 9000 9000 Ending Inventory Budget Q1 Q2 Q1 Q2 Productin Cost per T-shirt Direct Materials Direct Labor Manufacturing overhead Unit Product Cost Selling & Administrative Expense Budget Budgeted unit sales Variable selling and admin expenses per T-shirt Variable selling and admin expense Fix selling and admin expense Advertising Secretory Office supplied Office equipment 9000 9000 Total fixed selling & admin expenses Total selling & admin expenses Cash disbursement for S & A expenses Units to be produced Direct Materials Per Quarter: T-Shirt Quality Transfer Paper Total Direct Materials needed for production Add desired ending materials: T-Shirt Quality Transfer Paper Less Beginning Inventroy Direct Material Purchases: T shirt Quality Transfer Paper Cost Per Unit of Direct Materials: T-Shirt Quality Transfer Paper Cost of Direct Material Purchases: T-Shirt Quality Transfer Paper Total Cost of Direct Material Purchases Hours required for production: Worker 1 Worker 2 Total Hrs Hourly Rate Total Direct labor cost DM Budget The T-Shirt Printing Co. Direct Materials Cost Budget For the Year Ending December 31, 2016 Quarter 1 Quarter 2 9000 9000 9000 9000 9000 9000 0 0 2200 2200 0 4400 2500 2500 4400 600 11200 11200 9300 9300 $0.40 $0.19 $0.40 $0.19 #NAME? #NAME? #NAME? #NAME? $6,608.00 #NAME? DL Budget The T-Shirt Printing Co. Direct Labor Cost Budget For the Year Ending December 31, 2016 Quarter 1 Quarter 2 675 675 675 675 1350 $10.00 $13,500.00 0 $10.00 $13,500.00 MOH Budget Q1 budgeted direct labor hours: worker 1 675 Q2 675 worker 2 variable manufacturing overhead rate variable manufacturing overhead fixed manufacturing overhead total manufacturing overhead 675 $10/hour $6,750 675 $10/hour $6,750 Cash Budget Quarter 1 beginning cash balance estimated cash receipts from: collections of accounts receivable total sales total cash available estimated cash payments for : diect labor manufacturing costs selling and administrative expenses equipment purchases direct materials total cash payment excess ending cash balance Budgeted Income Statement revenue from sales cost of goods sold gross profit total selling and administrative expenses total income Budgeted Balance Sheet current assets: cash raw materials finished goods inventory total current asset equipment accumulated depreciation total assets Quarter 2 0 0 $9,000 $270,000 $279,000 $9,000 $270,000 $279,000 $13,500 $19,356 $59,625 $13,500 $19,356 $59,625 Q3 Q4 9000 30 270000 Q3 Year 9000 30 270000 Q4 9000 36000 120 1080000 Year 9000 36000 Q3 Q4 Year Q3 Q4 Year 9000 9000 36000 g Co. Budget ber 31, 2016 Quarter 3 9000 Quarter 4 9000 9000 9000 18000 9000 9000 18000 3000 3000 5000 19000 9500 Total 6000 6000 6000 24000 12000 9500 $0.40 $0.19 12000 $0.40 $0.19 #NAME? #NAME? #NAME? #NAME? #NAME? $7,080.00 $24,780.00 g Co. udget ber 31, 2016 Quarter 3 Quarter 4 675 675 675 675 Total 0 0 $10.00 #NAME? $10.00 #NAME? Q3 Q4 675 675 $54,000.00 Production requires 1 Tshirt 1QualityTransferPaper 675 $10/hour $6,750 Quarter 3 675 $10/hour $6,750 Quarter 4 Yearly 0 0 $9,000 $270,000 $279,000 $9,000 $270,000 $279,000 $13,500 $19,356 $59,625 $13,500 $19,356 $59,625 $36,000 $1,080,000 $1,116,000