How to do Excel Spreadsheet- Formulas?
I have to do an excel spreadsheet for my computer class. Problem is my professor didn't at all explain how to do it. What formulas am I supposed to use! Please Help. The info he gave us are : Cash Sales: 56%, Rec. Last month: 28%, Rec. Last 2 months: 16%, COGS: 29%, Sales Tax: 6%, Income Tax: 22%, Rent: $5200, Fixed selling cost: $2000.
Jan: $55,000 Feb $69,000 March $82,000 Apr $64,000 May $ 58,000 Jun $47,000 Jul $41,000 Aug $ 46,000 Sep $52,000 Oct $68,000 NOV $57,000 DEC $86,000.
I have to find the Total Sales, total cash sales, cash from receivables. total cash in, COGS, selling cost, sales tax, monthly rent, income tax, total cash out, net cash in(out), cash position, positive cash flow?, net cash rank, average of 12 months sales, highest of 12 months sales, lowest of 12 months sales.
I'm not asking for anyone to do it but to please shed some light on how to do it. We have to use formulas in excel to come up with the numbers. If anyone knows a website to help me or knows how to themselves it would be greatly appreciated. Please help! Thanks so much!!
Here is a wide variety of Excel tutorials online, including some on video:
http://www.google.com/search?rlz=1C1GGGE_enUS379US379&sourceid=chrome&ie=UTF-8&q=excel+tutorials
If any are searchable, be sure to search for FORMULAS or something similar to focus the tutorial on what you need at the moment.
To calculate the various values in your Excel spreadsheet, you can use formulas that perform calculations based on the given information. Here's a step-by-step guide on how to approach this task:
1. Open Excel and create a new spreadsheet.
2. Enter the provided information in separate cells. For example, enter the cash sales percentage in one cell, the receivables last month percentage in another cell, and so on. You can label each value in the adjacent cell for better organization.
3. Now, let's start calculating the various values:
- Total Sales: To calculate the total sales, you need to sum up the monthly sales figures (Jan to Dec). You can use the formula: "=SUM(B2:N2)" where B2 to N2 represents the range of cells containing the monthly sales figures.
- Total Cash Sales: Multiply the Total Sales by the Cash Sales percentage. Assuming the Cash Sales percentage is in cell B3, you can use the formula: "=B1*B3" where B1 represents the Total Sales and B3 represents the Cash Sales percentage.
- Cash from Receivables: Multiply the Total Sales by the Receivables Last Month percentage. Use a similar approach as above.
- Total Cash In: To calculate the total cash inflow, add the Total Cash Sales and the Cash from Receivables.
- COGS: Multiply the Total Sales by the COGS percentage, and use a similar approach as above.
- Selling Cost: Use the fixed selling cost value provided to calculate this.
- Sales Tax: Multiply the Total Sales by the Sales Tax percentage.
- Monthly Rent: Use the rent amount provided.
- Income Tax: Multiply the Total Sales by the Income Tax percentage.
- Total Cash Out: Add the COGS, Selling Cost, Sales Tax, Monthly Rent, and Income Tax.
- Net Cash In or Out: Subtract the Total Cash Out from the Total Cash In. This will give you the net cash flow.
- Cash Position: This depends on the net cash flow being positive or negative. If the net cash flow is positive, the cash position will be "Positive"; otherwise, it will be "Negative".
- Positive Cash Flow?: Create an IF formula to determine if the net cash flow is positive or negative. If the net cash flow is positive, the answer should be "Yes"; otherwise, "No".
- Net Cash Rank: You can use the RANK function in Excel to determine the rank of the net cash flow among other cash flows.
- Average of 12 Months Sales: Use the AVERAGE function to calculate the average of the 12-month sales figures.
- Highest of 12 Months Sales: Use the MAX function to find the highest value among the 12-month sales figures.
- Lowest of 12 Months Sales: Use the MIN function to find the lowest value among the 12-month sales figures.
Remember to refer to the respective cells and ranges in the formulas above based on where you have entered the information in your spreadsheet.
If you need further guidance or if you encounter any difficulties with specific formulas, please feel free to ask. Good luck with your assignment!