Tutorial: Using the RoundUp Function

After completing this tutorial, you will be able to use the RoundUp function , that gives you the ability to change the way the spreadsheet application rounds numbers.

RoundUp Function To begin, open the template that accompanies this tutorial.

Go to the worksheet labeled Break-Even Analysis.

In performing calculations, spreadsheets use fractional numbers when generating results. For example, the actual value in a cell could be 526.59; unless rounded up (or down) this fractional number would be used in any subsequent calculations based on this cell. This decimal may significantly skew answers in later calculations. For example, when performing break even calculations, you would normally want to round your answer up to the next whole number because partial-unit production may not be allowed (or make economic sense). For example, a break-even answer of 11.4 units needs to be rounded up to 12 units. This rounding allows you to produce more accurate answers in later calculations that rely on the break-even amount as an input. The RoundUp function allows you to round any fractional unit up to the next whole unit.

The general formula to roundup a value in a particular cell =ROUNDUP(the number or formula being rounded, the number of digits away from zero). The cell used as an example below is B6 (which contains a break-even calculation, expressed in units). In this cell, the formula is =ROUNDUP(B4/B5,0).

If TECHWHIZ is using 100-unit batches, you might be interested in knowing how many batches need to be produced and sold in order to break even (under the assumption that partial batches are not possible). To determine this, you simply change the formula in cell B6 to =ROUNDUP(B4/B5,-2). The -2 tells the software to round up two places to the left of the decimal point. Note, if you put a negative number in the formula, the software rounds up the desired number of places to the left of the decimal point and if you put in a positive number, the software rounds up the desired number of places to the right of the decimal point.

Required:

In addition to B-E analysis above, create a financial model that communicates/calculates the number of units to be sold for TECHWHIZ, in order to generate $155,000 in after-tax profits, given a 5% increase in Fixed costs, and a 22% tax rate. Assume 100-unit batches.

Please if you can provide all formulas for excel. thank you!

Breakeven analysis sheet 2 3 In units: 4 Facilities costs (for a highly automated plant mainly includes rent, insurance, taxes, and depreciation) 5 Per unit contribution margin 2,352,000 Q(F+N/(p-y) 4 463 527 Breakeven units