In the profit and loss worksheet, in the range C8:E8 project the company\'s inco
ID: 2599707 • Letter: I
Question
In the profit and loss worksheet, in the range C8:E8 project the company's income and expenses for the next five years by interpolating the Year 2 through Year 4 revenue assuming a growth trend.
In cell F3, enter 20% as the percent cost of marketing and in cell F4, enter 10% as the percent cost of R&D. In the range B9:F10, use those percentages to calcualte the Year 1 through Year 5 cost of sales. In the range B11:F11, calcualte the gross profit by subtracting the cost of market and R&D from the annual projected revenue.
In the range C14:F14 extrapolate the Year 2 through Year 5 payroll expenses by assumng the payroll grows by 12% per year. In C15:F17, extrapolate the other expenses by assuming the grow by 5% per year from the inital Year 1 values. In the range C18:F18, calcualte the total expense for Year 2 through Year 5.
In the range B21:F21, calcualte the company's initial earnings for each year, equal to thr gross profit minus the total general expenses.
**Please show all fromulas/work**
QR Shopper Business Plan Pro Forma Profit & Loss Statement Percent Cost of Marketing Percent Cost of R&D Assumed Tax Rate Income Year 1 Year 2 Year 3 Year 4 Year 5 Revenue $ 750,000 $ 1,900,000 Cost of Marketing Cost of R&D GROSS PROFIT Expenses Year 1 Year 2 Year 3 Year 4 Year 5 Payroll $ 420,000 Occupancy Costs 80,000 Insurance 20,000 Miscellaneous Expenses 30,000 TOTAL GENERAL EXPENSES 550,000 Earnings Year 1 Year 2 Year 3 Year 4 Year 5 Initial Earnings Depreciation Operating Profit Interest Expense Pretax Profit Tax Liability After-Tax Profit Dividends to Shareholders Retained EarningsExplanation / Answer
QR Shopper Business Plan Pro Forma Profit & Loss Statement Percent Cost of Marketing 20% Percent Cost of R&D 10% Assumed Tax Rate Income Year 1 Year 2 Year 3 Year 4 Year 5 Revenue $750,000.00 $1,037,500.00 $1,325,000.00 $1,612,500.00 $1,900,000.00 Cost of Marketing (20% of revenue) $150,000.00 $207,500.00 $265,000.00 $322,500.00 $380,000.00 Cost of R&D $75,000.00 $103,750.00 $132,500.00 $161,250.00 $190,000.00 GROSS PROFIT $525,000.00 $726,250.00 $927,500.00 $1,128,750.00 $1,330,000.00 Expenses Year 1 Year 2 Year 3 Year 4 Year 5 Payroll (12% grow every year) $420,000.00 $470,400.00 $526,848.00 $590,069.76 $660,878.13 Occupancy Costs (5% every year from year 1 values) $80,000.00 $84,000.00 $88,000.00 $92,000.00 $96,000.00 Insurance $20,000.00 $21,000.00 $22,000.00 $23,000.00 $24,000.00 Miscellaneous Expenses $30,000.00 $31,500.00 $33,000.00 $34,500.00 $36,000.00 TOTAL GENERAL EXPENSES $550,000.00 $606,900.00 $669,848.00 $739,569.76 $816,878.13 Earnings Year 1 Year 2 Year 3 Year 4 Year 5 Initial Earnings -$25,000.00 $119,350.00 $257,652.00 $389,180.24 $513,121.87
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.