Answered step by step
Verified Expert Solution
Question
1 Approved Answer
How do I calculate the IRR in this spread sheet? begin{tabular}{|l|l|ll|} hline D51 & v hline end{tabular} In-House Manufacturing: begin{tabular}{|c|c|c|c|} hline Unit Average Selling
How do I calculate the IRR in this spread sheet?
\begin{tabular}{|l|l|ll|} \hline D51 & v \\ \hline \end{tabular} In-House Manufacturing: \begin{tabular}{|c|c|c|c|} \hline Unit Average Selling Price & $ & 200.00 & \multirow{6}{*}{ As per unit cost per year calculated } \\ \hline Sales \& Marketing \% of Revenue & & 10% & \\ \hline General \& Admin \% of Revenue & & 3% & \\ \hline Income tax \% provision & & 35% & \\ \hline Unit Cost & & & \\ \hline MARR & & 8.50% & \\ \hline \end{tabular} Cells with formulas already Cells where the student enters data or a formula Units per year calculation \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|} \hline & 2022 & 2023 & 2024 & 2025 & 2026 & 2027 & 2028 & 2029 & 2030 & 2031 & 2032 \\ \hline \end{tabular} Free Cash Flow Model P\&L Revenue Avg Unit Selling Price Units Cost of Goods Sold Cost Per Units (line 32 next tab) Units = Gross Profit GP \% Operating Expenses Research \& Development SG\&A Sales \& Marketing General \& Admin Acquisition Depreciation IBT (Income Before Tax) Tax Net Income 8.5\% NPV IRR Non-Cash Expenses Depreciation Non-P\&L Cash Outlays Acquisition Cost Annual Depreciation = Free Cash Flow Cumulative Cash Flow 0.085 NPV IRR \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|} \hline & 2022 & 2023 & 2024 & 2025 & 2026 & 2027 & 2028 & 2029 & 2030 & 2031 & 2032 \\ \hline & $10,000,000 & $10,200,000 & $20,808,000 & $31,836,240 & $43,297,286 & $55,204,040 & $56,308,121 & $57,434,283 & $58,582,969 & $59,754,628 & $60,949,721 \\ \hline$ & 200 & 200 & 200 & 200 & 200 & 200 & 200 & 200 & 200 & 200 & 200 \\ \hline & 50,000 & 51,000 & 104,040 & 159,181 & 216,486 & 276,020 & 281,541 & 287,171 & 292,915 & 298,773 & 304,749 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hlines & 1,064,999 & $ & 1,064,999 & $ & 1,064,999 & $ & 1,064,999 & $ & 1,064,999 & $ & 1,064,999 & $ & 1,064,999 & $ & 1,064,999 & $ & 1,064,999 & $ & 1,064,999 & $ & 1,064,999 \\ \hline & $21 & & $21 & & $10 & & $7 & & $5 & & $4 & & $4 & & $4 & & $4 & & $4 & & $3 \\ \hline & 50,000 & & 51,000 & & 104,040 & & 159,181 & & 216,486 & & 276,020 & & 281,541 & & 287,171 & & 292,915 & & 298,773 & & 304,749 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline$ & 8,935,001 & $ & 9,135,001 & $ & 19,743,001 & $ & 30,771,241 & $ & 42,232,287 & $ & 54,139,041 & $ & 55,243,122 & $ & 56,369,284 & $ & 57,517,970 & $ & 58,689,629 & $ & 59,884,722 \\ \hline & 89% & & 90% & & 95% & & 97% & & 98% & & 98% & & 98% & & 98% & & 98% & & 98% & & 98% \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline$ & 3,000,000 & $ & 3,000,000 & $ & 1,500,000 & $ & 500,000 & $ & 500,000 & $ & 500,000 & $ & 500,000 & $ & 500,000 & $ & 500,000 & $ & 500,000 & $ & 500,000 \\ \hline$ & 1,300,000 & $ & 1,326,000 & $ & 2,705,040 & $ & 4,138,711 & $ & 5,628,647 & $ & 7,176,525 & $ & 7,320,056 & $ & 7,466,457 & $ & 7,615,786 & $ & 7,768,102 & $ & 7,923,464 \\ \hline$ & 1,000,000 & $ & 1,020,000 & $ & 2,080,800 & $ & 3,183,624 & $ & 4,329,729 & $ & 5,520,404 & $ & 5,630,812 & $ & 5,743,428 & $ & 5,858,297 & $ & 5,975,463 & $ & 6,094,972 \\ \hline$ & 300,000 & $ & 306,000 & $ & 624,240 & $ & 955,087 & $ & 1,298,919 & $ & 1,656,121 & $ & 1,689,244 & $ & 1,723,029 & $ & 1,757,489 & $ & 1,792,639 & $ & 1,828,492 \\ \hline \end{tabular} $260,876,620.27 \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline$ & (12,392,249) & $ & (11,178,916) & $ & (2,297,605) & $ & 6,464,455 & $ & 14,794,925 & $ & 23,356,386 & $ & 24,841,304 & $ & 26,289,425 & $ & 27,704,445 & 4 & 29,089,839 & 30,448,877 \\ \hline$ & (12,392,249) & $ & (23,571,165) & $ & (25,868,770) & $ & (19,404,315) & $ & (4,609,390) & $ & 18,746,995 & $ & 43,588,300 & $ & 69,877,725 & $ & 97,582,169 & $ & 126,672,008 & $157,120,885 \\ \hline \end{tabular}Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started