Question

Use the following estimated information for the Barkery to perform the analyses: Revenues $300,000 Cost of goods sold 60% of revenues Salaries and wages 22% of revenues Additional costs: Operating Costs - $12,600 Depreciation -$2,100 Bad debts expense-3 % of 70 % of revenues Interest expense - $1,125 . REQUIRED; On the basis of the facts as given above, prepare the following schedules using a spreadsheet software package: A. Prepare an income statement based on the information above. B. The Barkery would like to be more profitable. Use Goal Seek in Excel to determine the following changes, Each situation is independent of the others. You will need to reset after each situation 1. How much would the cost of goods sold percentage need to be for net income to be $40,000? 2. How much would the cost of goods sold percentage need to be for net income to be $50,000? 3. Assume original percentages, how much would the salaries percentage need to be for net income to be $50,000? C. GO BACK TO THE ORIGINAL DATA. Use Solver in Excel to determine the following assuming these constraints (COGS not more than 70% or less than 50%, Salaries not more than 30 % or less than 10 % ) : 1. Assuming both must change, what percentages would the COGS and the salaries percentages have to be in order to get $40,000 net income? 2. Assuming both must change, what percentages would the COGS and the salaries percentages have to be in order to get $50,000 net income? 3. What is the maximum net income The Barkery can achieve with the above constraints? 4. What is the minimum net income The Barkery can achieve with the above constraints? D.GO BACK TO THE ORIGINAL DATA. following: Use Solver in Excel to determine the 1. Assuming COGS = 60% and Salaries = 22%, how much would revenues have to be to earn $40,000 net income?

Answer 1

Income Statement based on the information using spreadsheet as follows: А Bc Company name Income Statement for the year ended Amount 4 Net Sales/Revenues $300,000 5 Less: Cost of Goods Sold $180.000 6 Gross Profit $120,000 7 Less: opearting expense Salaries and wages $66,000.00 Operating cost $12,600 Depreciation $2.100 Bad-Debt expense $6.300.0 Interest expense $1,125 $88.125.00 14 Net Income/loss) $31.875.00

Working as follows: A BC Company name Income Statement for the year ended Amount 4 Net Sales/Revenues 300000 5 Less: Cost of Goods Sold =0.6*C4 6 Gross Profit =C4-C5 7 Less: opearting expense Salaries and wages =0.22*C4 Operating cost 12600 Depreciation 2100 11 Bad-Debt expense =(0.7* C4)*0.03 Interest expense 1125 =SUM(B8-B12) 14 Net Income (loss) =C6-C12

Computation of cost of goods sold using Goal seeks function in Excel as follows: Given data before applying Goal Seek Function as given below: А в с Company name_ Income Statement for the year ended Amount 4 Net Sales/Revenues $300,000 Less: Cost of Goods Sold SO 6 Less: opearting expense Salaries and wages 66,000.00 Operating cost $12.600 Depreciation $2.100 Bad-Debt expense $6,300.0 Interest expense $1,125 $88,125.00 10 13 Net Income/loss) $211.875.00

А Bc Company name Income Statement for the year ended Amount 4 Net Sales/Revenues 300000 5 Less: Cost of Goods Sold 6 Less: opearting expense Salaries and wages 66000 Operating cost 12600 Depreciation 2100 Bad-Debt expense 6300 Interest expense 1125 =SUM(B7:B11) 13 Net Income/loss) =C4-C5+C1l) How to open Goal Seek function? Click the data tab. In the Data Tools group. Click What-If Analysis and choose Goal Seek. Steps to input as follows: 1. Select the cell that contains the formula. In this case C13. 2. To value input which your target value in this case $40000 net income. 3. By changing value menu the cell which you want to find out the value in this case COGS i.e. C5

F X w NW E Goal Seek ? Set cell: $C$13 To value: $40000 By changing cell: $$s OK Company name Income Statement for the year ended Amount 4 Net Sales Revenues 300000 5 Less: Cost of Goods Sold 6 Less: opearting expense Salaries and wages 66000 Operating cost 12600 Depreciation 2100 Bad-Debt expense I Interest expense 1125 88125 -- Cancel 12 13 Net Income/loss) T 2 11872 Value of COGS as follows by using Goal Seek:

F G Step Pause А ID E Company name Goal Seek Status 2 Income Statement for the year ended Goal Seeking with Cel C13 Amount found a solution. 4 Net Sales Revenues 300000 Target value: 40000 5 Less: Cost of Goods Sold 171875 Current value: 40000 6 Less: opearting expense OK 7 Salaries and wages | 66000 Operating cost 12600 Depreciation 2100 Bad-Debt expense 6300 Interest expense 88125 Cancel 13 Net Income (loss) 40000 Percentage of COGS=(COGS/Net sale)x100 = 57.29%

Computation of COGS, IF target income is $50000 as follows: A E F Goal Seek BC Company name 2 Income Statement for the year ended 3 Amount 4 Net Sales Revenues 300000 5 Less: Cost of Goods Sold SO 6 Less: opearting expense Salaries and wages 66000 Operating cost 12600 Depreciation 2100 Bad-Debt expense | 6300 Interest expense | 1125 88125 Set cell: $C$13 To value: $50000 By changing cell: $c$s OK Cancel 13 Net Income (loss) 211875

COGS amount as follows: F G D E Goal Seek Status Step Pause А BC Company name 2 Income Statement for the year ended Amount 4 Net Sales Revenues 300000 5 Less: Cost of Goods Sold $161.875 6 Less: opearting expense Salaries and wages 166000 Operating cost 12600 Depreciation 2100 Bad-Debt expense 6300 Interest expense 1125 88125 Goal Seeking with Cell C13 found a solution. Target value: 50000 Current value: 50000 OK Cancel B00 13 Net Income (loss) 50000 Percentage of COGS =(COGS/Net sale)x100 = 53.95%

Compute salaries and wages expense assuming original percentage as follows; Ε F Goal Seek А BC Company name Income Statement for the year ended Amount 4 Net Sales Revenues 300000 5 Less: Cost of Goods Sold 6 Less: opearting expense Salaries and wages $0 Operating cost 12600 Depreciation 2100 Bad-Debt expense 6300 Interest expense 1125 2 2125 Set cell: $C$13 To value: $50000 By changing cell: $8$7 OK Cancel 13 Net Income (loss) 97875

Salaries and wage Amount as follows: F G D E Goal Seek Status Step Pause BC Company name 2 Income Statement for the year ended Amount 4 Net Sales Revenues 300000 5 Less: Cost of Goods Sold $180,000 6 Less: opearting expense Salaries and wages $47,875 Operating cost 12600 Depreciation 2100 Bad-Debt expense 6300 Interest expense 1125 70000 Goal Seeking with Cell C13 found a solution. Target value: 50000 Current value: 50000 OK Cancel 13 Net Income (loss) 50000 Salaries and wage amount percentage = (Salaries & wage Net Revenue) 100 = 15.95%