In: Accounting
The model to the right is a very simple Income/Loss Statement for a manufacturing business. There are many simplifications in this model, but an Income/Loss Statement is a very useful tool for analyzing profitability when numerous variables need to be considered. The model assumes that the variable values are known (deterministic), but this does not restrict the user from asking numerous important "What-If" questions. You can determine the model's assumptions by examining the cells where calculations take place...e.g. notice the parameters in Column N used in the calculations.
a) Build a two-way data table that varies Var. Cost % from 0.05-0.40 (in increments of 0.05 on the row) and Revenue from 100,000-1,000,000 (in increments of 50,000 on the column). Add conditional formatting to the data table that indicates when a value is negative. PLEASE LIST STEP-BY-STEP PROCESS FOR BUILDING THIS TABLE. THANK YOU SO MUCH!
b) Add a form-control scroll bar to control Tax Rate, which should vary from 0.24 to 0.45 in 0.01 unit increments. (Remember that the values of form control are integers and between 0-30,000, so you will need a dummy value cell to adjust for the scale.) PLEASE LIST HOW TO DO THIS STEP-BY-STEP. THANK YOU SO MUCH FOR THE HELP!!!
Revenue | 600000 | ||
COGS | 300000 | 0.5 | <=COGS% |
Gross Profit | 300000 | ||
Variable Cost | 120000 | 0.2 | <=Vaar.Cost% |
Fixed Cost | 100000 | ||
Operating Expenses | 220000 | ||
Net Operating Income | 80000 | ||
Interest Expense | 25000 | ||
Earnings Before Taxes | 55000 | ||
Taxes | 18700 | 0.34 | <=Tax Rate |
Net Income | 36300 | * units in $$ | |
Formula Sheet for the above calculation
Conditional Formatting
Answer 2
Insert Developer Tab
Go to File> Options> Customize Ribbon and Check on Developer
Then go to developer tab> Insert> Inser the Scrool Box in one cell only
Then select the scroll box and go to properties
Or
Right Click the scrool box
This dialogue box will open. Inser the values as per image