Calculating Revenue, Profit & Total Profit The calculated volume figures go in to columns H & I. Notice the mixed referencing style used, this will help you drag and reuse the same formula for calculating table volume.
Once yearly volume is picked, we just divide it by 12 to get monthly volume (ie units sold per month). To calculate year from month (G6), we use simple arithmetic: INT(($G6-1)/12) + 1 Simple, we pick the volume for year represented by month using INDEX formula. We can calculate number of chairs sold in that month using below formula (call it formula 1) Refer to inputs picture in above section for cell references. Let’s say month numbers are in column G, from G6 to G65. Although your initial estimates suggest that all stock will be sold in first 3 years, this allows you to monitor cash flows over 5 years, should there be a change in the inputs. Once we have these numbers, calculating revenue & profit per month is easy. The tricky part is figuring out units of chairs & tables sold per month. This will let you play with various combinations and control outputs in a better way. Note: If your business problem is complex, you need to setup dedicated worksheets for each type of input (fixed, variable and assumption). Gray ones have calculated values.Įverything in the above picture is self-explanatory, so let’s move on to output section.
Your model becomes complicated and can take a lot of time to develop. This will give maximum flexibility, but comes with a lot of cost. Of course, you can consider everything to be a variable in your model. Why bother – can’t everything be a variable? Use your experience and industry knowledge to define what items belong where. Of course, these three categories can overlap. Assumptions – We will be able to sell off all the items (ie no write offs), Profit per unit and book value per unit doesn’t change over time.Variables – Number of chairs and tables sold every month (or year), profit expectation.Fixed Inputs – for example opening stock of chairs & tables, book value of these items.Most business case problems will have following three kinds of parameters The case in this article is based on a forum question by Proteus.
CASH FLOW DASHBOARD EXCEL HOW TO
Let’s learn how to answer such open ended questions using various analysis techniques in Excel. You need to analyze this and prepare a cash flow model. You forecast that we can sell off these as per below. You are hoping to sell them off in 2 or 3 years. When sold, they will yield $100,000 and $25,000 gross profit. These are valued at $200,000 and $100,000 respectively. You currently have 20,000 Relaxer chairs and 5,000 Boca Tops. You want to stop reordering two brands of furniture – Relaxer (a type of chair) and Boca Top (a type of table). Imagine you run an office furniture company.
0 kommentar(er)
