Answered step by step
Verified Expert Solution
Question
1 Approved Answer
HOWEWORK 2 FINANCE 4 2 5 , FALL 2 0 2 3 ( This assignment is worth a total of 6 0 POINTS ) This
HOWEWORK
FINANCE FALL
This assignment is worth a total of 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 multistep 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 setup so that you can readily and easily change the assumptions in your assumption block so as to perform a "whatif analysis. All of the "hard numbers" will be presented in the assumption area the "calculation" area of your spreadsheet MUST NOT contain any hard numbersassumptions 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 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:
Quarter sales will be units
After Quarter sales increase by units per quarter
The sale price for a unit in Quarter is $ per unit
The sales price per unit increases by per quarter after Quarter
Initial costs of good sold is $ per unit for Quarter
COGS increases by every quarter after Quarter
Rent expense is $ per quarter and will not change for the Base Budget
Utility Expense is $ for Quarter and increases by per quarter
General overhead expense is $ per quarter and increases per quarter
Advertising expense is $ per quarter BUT it decreases by per quarter after Quarter
Interest Expense is $ per Quarter and will not change for the Base Budget
The tax rate is 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 Budget Option and Budget Option 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 : Change initial sales from to units and change the increase in sales per quarter after Quarter from to units per quarter. Do not change any of the other initial assumptions established in the Base Budget.
Budget Option : Use the original assumptions from the Base Budget but change the selling price for each unit from $ to $ in Quarter and increasing by per quarter after Quarter
Budget Option : Use the original assumptions as established in the Base Budget but change the tax rate to a more reasonable and realistic
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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started