HOWEWORK $2$

FINANCE $425,$ FALL $2023$

$($This assignment is worth a total of $60$ POINTS$)$

This homework assignment is going to test you on how well you have been listening to me regarding the use of an "assumption" block as you develop a spreadsheet with repeating calculations and changing inputs. For this assignment, I want you to create an operating budget for a manufacturer named ABC Manufacturing Corporation $($creative name, right?$).$ The budget to be created is a "Quarterly" budget as opposed to a more traditional monthly budget or summary annual budget. That being the case, within your solution, I expect you to present an Excel spreadsheet that shows four columns presenting Quarters One through Four of an operating year. Your spreadhseet should also present a fifth column that presents the Totals of all four quarterly columns. Each quarter is compounding $-$ that is$,$ it builds on the preceeding quarter. Your operating budget should be created in the format of the multi$-$step income statement $-$ there is no depreciation expense and no "Other Income or Expense" so you do not need to show an EBITDA line and you can refer to your EBIT as NOI.

Somewhere at the top of your spreadsheet, you will place all of your assumptions from which the calculation portion of your spreadsheet $($the actual operating budget$)$ will draw the key inputs to be used in performing the calculations needed to develop the operating budget by quarter for ABC. You will do this type of set$-$up so that you can readily and easily change the assumptions in your assumption block so as to perform a "what$-$if$"$ analysis. All of the "hard numbers" will be presented in the assumption area $-$ the "calculation" area of your spreadsheet MUST NOT contain any hard numbers$/$assumptions$.$ Rather, your calculation area of the spreadsheet is to be developed so as to draw all required input numbers for calculations from the assumption block area of your spreadsheet. Refer back to the problem I assigned to you to work on as an exercise following our Week $2$ session and my solution for it as an example of what I am looking for specifically!!

The assumptions from which you will build your "Base" Quarterly Budget are presented below:

$1)$ Quarter $1$ sales will be $10,000$ units

$2)$ After Quarter $1,$ sales increase by $500$ units per quarter

$3)$ The sale price for a unit in Quarter $1$ is $$150$ per unit

$4)$ The sales price per unit increases by $3\%$ per quarter after Quarter $1$

$5)$ Initial costs of good sold is $$58$ per unit for Quarter $1$

$6)$ COGS increases by $2\%$ every quarter after Quarter $1$

$7)$ Rent expense is $$10,000$ per quarter and will not change for the Base Budget

$8)$ Utility Expense is $$15,000$ for Quarter $1$ and increases by $6\%$ per quarter

$9)$ General overhead expense is $$15,000$ per quarter and increases $4\%$ per quarter

$10)$ Advertising expense is $$8,000$ per quarter BUT it decreases by $2\%$ per quarter after Quarter $1$

$11)$ Interest Expense is $$1,000$ per Quarter and will not change for the Base Budget

$12)$ The tax rate is $45\%$ of taxable income

The Quarterly Budget spreadsheet you create with these above assumptions is your "Base" Budget $-$ that will be the first sheet of your Excel workbook. Once your Base Budget is complete, you will copy that base budget THREE times $($that is$,$ you will create three duplicates of your base budget as three additional worksheets in your Excel workbook. Rename the three tabs for the copied base budget as Budget Option $1,$ Budget Option $2$ and Budget Option $3.$ You will then make the following changes to your assumptions to test how well you have set your spreadsheet up and to see the impact that each change in assumptions has on your Operating Budget.

Budget Option $1$: Change initial sales from $10,000$ to $12,000$ units and change the increase in sales per quarter after Quarter $1$ from $500$ to $700$ units per quarter. Do not change any of the other initial assumptions established in the Base Budget.

Budget Option $2$: Use the original assumptions from the Base Budget but change the selling price for each unit from $$150$ to $$175$ in Quarter $1$ and increasing by $7\%$ per quarter after Quarter $1.$

Budget Option $3$: Use the original assumptions as established in the Base Budget but change the tax rate to a more reasonable and realistic $21\%.$

You will submit to me your Excel workbook that presents four tabs $-$ the Base Budget and an additional three tabs that present the three Budget Option scenarios. I will be looking to see that you properly developed a "calculation" area within your workbook that only contains formulas $-$ no numbers $-$ and references back to the assumptions that you place in your worksheet. If the calculation area of you