Collaborate on Excel Pro Forma Template for Production with Ease Using airSlate SignNow

Excel pro forma template for Production

financial statement of a person or an organization is a common term most of us heard but these detailed statements can differ based on their receiver every Financial details usually are in disclosed to a third party so there comes the performa financial statement where all the internal financial information are disclosed and are interconnected to each of the calculations you will find the process easier than it sounds let's see how hello there this is farat mim from EX Dei you are one stop for Excel blog post discussion forum templates and V related [Music] answers here we have three sheets in the income statement sheet you can see the income statement table assumption table capital expenditure and depreciation tables in the balance sheet worksheet you will find the table for balance sheet the assumptions and lastly in the cash flow statement sheet you will find a table listing the various components of the cash flow statement if you can notice you can see there are different kinds of fill colors used here income statement has been used as blue themed balance sheet as yellow themed and finally cash flow statement as gray themed each of the fill colors basically represent that this information sources will be available from yellow as in balance sheet and blue as in income statement worksheet we have previously formatted these tables so that we can easily make you understand on these formatted worksheets we will learn how to create a performal financial statement in Excel we will use Excel 365 for that you can use any other available Excel versions as well let's get down to the video as we can see that our income statement comes with three consecutive years calculation so we will discuss the calculations for 2022 on rest of the years will follow just as the same to calculate gross revenue we need to multiply orders and price per order ing to our assumption table equals I6 multiplied by i7 now refunds will be the gross revenue into the refund percentage which is 2% ing to the Assumption table equals now refunds will be the negative value so minus Revenue which is C7 let's make it absolute using F4 key two times so that our row reference doesn't change but column does even if we use fill handle to copy the formula to somewhere else multiplied by refund percentage which is I8 enter now discounts will be the same formula as refunds so we can just use fill handle to drag it till C9 and we can see see that the value gives us gross revenue multiplied by discounts percentage which is 5% now to calculate our net revenue we need to add all of them so we can just use a formula using Su function or we can Auto some using alter key and equal key all together so we will get the autosum keyboard shortcut enter now let's select all of them and using fill handle drag till 2024 so all of our values are just like that now let's Calculate cost of goods sold in cell C12 we will add formula equals our net revenue let's make it absolute using F4 key twice so that the row reference doesn't change with the formula copying multiplied by the product percentage for cost of goods sold 40% which is it 10 now if we use fill handle to drag so that we get product distribution and Merchant Services as well we can see the values like that so total cost of goods sold can be added using autosum alter equal so gross margin will be net revenue Minus cost of goods sold equals C10 minus C15 so this is our gross margin now to get the values for 2023 and 24 let's use fill handle to drag to column e now before we go into operating expense we need to calculate capital EXP expenditure and depreciation now these are our assumed values for data servers customized app Logistics in our case we have chosen these assumptions for all these sheets but this will vary ing to your use so feel free to change the assumptions ing to your convenience so here our total capital expenditure for 2022 alt equal let's use fill handle to drag it till column K now to calculate depreciation in I24 we will calculate the depreciation for data servers now we can select I24 to j24 and use a formula equals data servers cell reference which is i19 divided by h24 which is the useful life within the H column mentioned before let's make it absolute using F4 key three times so that our column reference doesn't change even if we use fill handle to copy the formula use controll enter now in j24 we need to edit the formula adding the value from I24 enter now let's drag the formula to K24 now the formulas will be similar for all these variables so let's use fill handle to drag till row 26 so let's add total depreciation for all these years using autosome feature alt equal use f handle to get total depreciation for these three years now let's go to operating expense here in c18 let's use a formula to use net revenue or Satan use F4 key twice to make it absolute so that the row reference doesn't change multiplied by the administrative percentage from assumption table which is I13 enter let's use fill handle to drag the formula to get marketing and and other values for operating expense now the depreciation value will come from the depreciation we calculated just now so equals i27 so total operating expense will be adding all of them so alt equal now operating income will be gross margin minus total operating expense so c16 minus c22 as we get our operating expense for 2022 let's select all of them and use fill handle to get operating income for 2023 and 2024 as well before proceeding any further we need to complete some tasks on balance sheet let's go to balance sheet now here as you can see the blue fills will be the information that has sources from income statement let's add net revenue first in I6 equals going to income statement click on C let's use fill handle to drag till that now to get cash and cash equivalence we need previous value and net cash flow value since these values will be output from Cashflow statement worksheet let's assume that for now let's use minus 20,000 1 million finally 400,000 now we can see from the balance sheet that we have an initial balance for 31 December 2021 exact date just before 2022 so here in 2022 or D7 let's try the formula equals C7 as previous Cash Plus the net cash flow for 20122 enter now accounts receivable will be equals the net revenue multiplied by accounts receivable percentage from assumption table which is 4% enter now total current asset will be addition of these two so let's use autosome feature alt and equal Keys together now for fixed asset value we need to include the previous fixed asset with the capital expenditure for 2022 so equals C10 let's make it absolute so that it's constant and doesn't change even if we copy the formula plus go to income statement and from the capital expenditure choose the one for 2022 now accumul culated depreciation needs to be subtracted from the initial one so equals c 11 minus from income statement the total depreciation for 2022 so here our net fixed asset will be submission of d10 and d11 so let's use autosome feature alt equal for total asset use alt equal as well now as the reference goes we can see that it's adding D12 and D9 which are our net fixed assets and total current assets enter now select all of them and drag them till column if there we are now let's go to liability section in D15 we need to add accounts payable value so we will multiply the accounts payable percentage of 5% from assumptions table with net revenue so equals net revenue which is I6 let's make it absolute using F4 key two times so that the row reference doesn't change multiplied by accounts payable now unearned Revenue will be just the same as unearned revenue is 4% just below accounts payable percentage so total current liabilities will be adding both of these alt equal now long-term depth will be equals the initial Deb plus the net borrowings minus the debt payments enter so total liabilities will be adding long-term depth and total current liabilities so alt equal we'll add only d18 let's add d17 with it there we are now let's select D15 to d19 and use fill handle to drag to F column now as we can see that there is a small green triangle in these three cells on the top left corner if we select them we can see that that it's showing us inconsistent formula since the auto sum is not matching with the previous one so we can just select them and choose ignore error now before going to equity section we need to calculate the interest payment for that click on I13 and add equals long-term depth value which is in d18 multiplied by the interest rate in i12 enter let's use fill handle to drag the till column K it's time to complete our income statement worksheet let's go to income statement and here the interest value will be equals balance sheet I13 now net income before tax will be equals operating income minus the interest amount which is c23 minus c25 now the tax amount will be the tax rate into the net income before taxed so equals c26 which is which is our net income before tax multiplied by the tax rate 23% in i16 so the net income will be c26 which is net income before tax minus the tax value which is c27 since it's not selectable we have typed it so this is our net income for 2022 let's select c25 to c28 and use fill handle to drag till e column to get the income for 2023 and 2024 as well now let's go to balance sheet to complete our Equity section here common stock will be just the same as initial here retained earnings will be equals the initial earning plus the net income of the following year so go to income statement to get net income in c28 so this is retained earning for 2022 so total shareholders Equity will be adding common stock and retained earnings so alt equal to add both of them so total liabilities and Equity will be equals total liability which is d19 plus d23 enter now for balance verification we need to deduct the liabilities and Equity from total asset so equals total asset which is D13 minus total liabilities and Equity now we need to make sure that balance verification needs to be zero we will verify that after we go to cash flow statement for now let's use fill handle to drag them so that we get values till 2024 now let's go to cash flow statement here we can see there are different kinds of fill colors used here net income depreciation capital expenditure will be clearly referenced from the income statement worksheet and the yellow fills will be from balance sheet now here let's add net income from income statement sheet c28 let's use fill handle to drag it till e column depreciation value from income statement as well i27 now change in accounts receivable to get the value we need to subtract from balance sheet so account receivable here will be C8 minus d8 so that we get the previous accounts receivable minus recent account receivable enter which is totally opposite for accounts payable equals let's go to balance sheet here we need to deduct C15 from D15 which is recent minus the previous one enter so change in unearned Revenue will be just the same as accounts payable use fill handle to copy the formula to get the total operating cash flow we need to add these four and next net income let's use autosome feature here using alt equal and here add C6 as in net income enter let's select all of them and use fill handle to drag till column e for capital expenditure it's the exact value from income statement in i22 for free cash flow we need to deduct capital expenditure from operating cash flow so equals C12 minus C4 enter now let's use fill handle to drag till column e now it's time for financial activities here the reference will be from balance sheet net borrowings and debt repayment will be just the same which is balance sheet i1 now the only difference is adding a minus here since Deb repayment will be opposite to net borrowings so net cash flow from financing will be equal adding these two so let's use alt equal add this two now net cash flow will be equals free cash flow plus net cash flow from financing which is c19 enter now just as we did before select c17 to C20 and use Phil handle to dtive column e now these values will be used in balance sheet here in 2022 net cash flow will be equals cash flow statement C20 and using f handle dra to K column as we can see that balance verification is not zero for all the years so there must be calculation problem within our balance sheet the problem is in fixed asset calculation here in c10 let's use F4 key three times so that it's no longer absolute now let's drag the formula till F10 as we can see that balance verification is zero here fixed asset will be adding fixed asset of the previous this year which was adding the fixed asset of 2021 every time which is why our value was wrong now as you can see we have successfully created performa financial statement for you using this elaborate steps you can also create a performa financial statement in Excel you can practice this with the help of the Excel file in the description box below let us know if you could do it in the comment section share your suggestions and feedback as well you can visit our website Excel .c and reach out to our forum for all your VBA or EXL related problems free of cost stay tuned by subscribing to the channel and clicking on the Bell icon like and share the video if it helps you thanks for watching