HI Tutor,

Can you help me with the my project. The attached file is in word and excel. Excel needs to be fill in with the answers... it is due in 24 hours. Thanks.

Graphic Design unit sales unit selling price Q1 Q2 Q3 8,000 10,000 20,000 $16 $16 $16 $128,000 $160,000 $320,000 Q4 Total Year 50,000 $16 $16 $0 $800,000 Collections from customers A/R 1st qtr 2nd qtr 3rd qtr 4th qtr $128,000 $160,000 $320,000 $0 $0 $89,600 $38,400 112,000 224,000 $89,600 $150,400 $224,000 96,000 0 $96,000 0 Graphic Design shirts to be silk-screened plus: desired ending inventory total shirts less: beginning inventory total shirts needed cost per shirt total cost of shirt purchase A/P 1st qtr 2nd qtr 3rd qtr 4th qtr Q1 Q2 Q3 8,000 10,000 20,000 2,500 5,000 10,500 15,000 20,000 0 2,500 5,000 10,500 12,500 15,000 $10 $10 $10 $105,000 $125,000 $150,000 payments 0 $105,000 42,000 63,000 $125,000 50,000 $150,000 $45,000 $42,000 $113,000 Q4 Total Year 38,000 4,500 42,500 0 4,500 42,500 $10 $10 $45,000 $425,000 0 4,500 4,500 90,000 18,000 $60,000 $108,000 18000 60,000 27000 Graphic Design units to be produced labor hours per unit total labor hours cost per hour total cost Q1 8,000 0.12 960 $12 $11,520 Q2 0.12 0 $12 $0 Q3 0.12 0 $12 $0 Q4 0 0.12 0 $12 $0 Total Year 8,000 0.12 960 $12 $11,520 Graphic Design Sales commissions Fixed Costs Advertising Rent Sales Salaries Office Salaries Depreciation Property Taxes and Insurance Total Fixed costs Total S&A Expenses Q1 $12,800 1,300 750 1,800 1,800 75 95 5,820 $18,620 Q2 Q3 0 $0 Q4 0 $0 Total Year $0 $12,800 1,300 750 1,800 1,800 75 95 5,820 $5,820 2,600 1,500 3,600 3,600 150 190 11,640 $24,440 Graphic Design Variable Expenses Ink Maintenance Utilities Graphics Design Total variable Fixed Expenses Rent Maintenance Utilities Grapics Design Property taxes and insurance Depreciation Total Fixed Total MOH direct labor hours Q1 Q2 Q3 Q4 $6,000 264 400 800 7,464 $7,500 330 500 1,000 9,330 $15,000 660 1,000 2,000 18,660 2,250 4,950 3,000 1,500 560 690 12,950 $20,414 2,250 4,950 3,000 1,500 560 690 12,950 $22,280 960 0 Total Year 0 $28,500 1,254 1,900 3,800 35,454 2,250 4,950 3,000 1,500 560 690 12,950 $31,610 0 $0 6,750 14,850 9,000 4,500 1,680 2,070 38,850 $74,304 0 0 960 $77.40 0.75 0.033 0.05 0.1 Graphic Design Q1 Beg cash balance add: collections from customers Total available cash Less: Disbursements Payments for shirt purchases Labor MOH S&A Equipment purchases Total Disbursements Excess(deficiency) Financing Borowings Ending Cash Balance $0 Q2 ($38,464) Q3 ($68,729) Q4 ($99,649) $0 ($38,464) ($68,729) ($99,649) 11,520 19,724 18,545 8,675 58,464 (58,464) 0 21,590 0 8,675 30,265 (68,729) 0 30,920 0 0 0 5,745 30,920 (99,649) 5,745 (105,394) 20,000 ($38,464) ($68,729) ($99,649) ($105,394) Graphic Design Sales COGS Gross Profit S&A expenses Income from operations Interest Expense Income before income taxes Income taxes Net Income 0 0 1,600 -1,600 -320 -$1,280 Graphic Design Sales Variable Costs Contribution Margin Fixed Costs Net Income 0 $0 $0.00 Graphics Design Manufacturing Overhead Flexible Budget Report For the quarter ended March 31, 2016 Original Budget Production in units Variable expense: Ink ($0.75) Maintenance ($0.033) Utilities ($.05) Graphic Design ($0.10) Total variable expenses Fixed Expenses: Rent Maintenance Utilities Graphic Design Property taxes and Insurance Depreciation Total fixed expenses Total Expenses Flexible Budget Actual 6,500 $5,000 215 400 500 $6,115 $2,250 4,900 2,000 2,000 560 690 $12,400 $18,515 Actual Costs Over (under) Flexible Budget Graphics Desgin Selling & Administrative Flexible Budget Report For the quarter ending March 31, 2016 Original Budget Production in units Flexible Budget Actual 6,500 Variable expenses: Sales commision 18,000 Advertising Rent Sales salaries office salaries Depreciation Property taxes and insurance Total fixed expenses Total selling & administrative expenses 1,200 750 2,000 1,800 90 80 5,920 23,920 Total variable expenses Fixed expense: Actual Costs Over (under) Flexible Budget You have just been hired by Graphic Design as their new President. The company has multiple product lines including a profitable T shirt design business. As the President, one of your first tasks is to review some budgets that were given to you by the Vice President of the T shirt line. Your understanding of this business is limited but your background in accounting has you very interested in reviewing a critical part of the business. You need to complete the following tasks: You will review and make any changes for missing information on the 2016 budgets that were given to you. Hint- the blocked off cells need to be completed. I would also recommend that you spend some time understanding each budget and the relationship to the other budgets. A Traditional Income Statement for the year only needs to be completed. For the Cost of Goods sold, please use the cost of the T shirts, labor and MOH for the calculation. Please link all areas appropriately. A Cost Volume Analysis (CVP) Income Statement also needs to be completed for just the year. Please link all areas appropriately Calculate a flexible budget for the Sales and Administration and MOH budgets using increments of 1,000 units for quarter 1. So currently, you are selling 8,000 units for the first quarter so you want to prepare a schedule that will also include 6,000, 7,000 and 9,000 units sold. (similar to page 1014 exhibit 23-17) In addition assume, the company actually sells 6,500 units, prepare an actual to budget comparison for both the Sales and Administration and MOH budgets using the actuals for the first quarter found on the spreadsheets attached. After reviewing the revised budgets, flexible budgets for the Sales and Administrative and MOH budgets and budget to actual comparison, please discuss in a word document (memo format) about some of the following issues: The owner asks you to explain flexible budgets and their impact on budgeting. Please explain the concept and the importance of this type of budgets. What caused the budget to actual variances? Please explain and discuss the implications to the company if these trends continue. Evaluate the budgets for any areas that you might be able to recommend some changes like cost reductions, or price changes. Identify two areas for these recommended changes. For example, review the sales commissions currently paid. What is the % and should it be changed? DO NOT use this example in your paper. How do your changes impact the company? Support your conclusions with any calculations that are necessary. (A revised CVP income statement is always a great tool). Your final submission will include the excel worksheets and a word document(about 3 pages)