IFSM 201

You are the CFO (chief financial officer) of Midwest Universal Gas
preparing the annual financial statement.  Below are the "business
formulas" that are to be used in the spreadsheet. This is a typical
way a spreadsheet is created: you have, or know, or are given, the
business formulas and some data and then you turn them into a worksheet.

Copy and paste the layout of the spreadsheet below into an Excel worksheet.
Apply some formatting to make it look professionally appealing.
Because the monetary values are in thousands of dollars, don't show decimal points.
Add a column chart that shows Total Sales, Total Expense, and Net
 Income for the four quarters.
Fit the worksheet onto one printed page.  The chart on its own page.

BCF stands for Billion Cubic Feet.
Retail gas sold is 22% of Total Gas Sold.
Wholesale gas sold is 78% of Total Gas Sold.
Retail sales is the Retail Price times the Retail gas sold.
  Note: use absolute cell referencing to reference the Retail Price
  cell so that when copying the formula the reference doesn't change. 
  i.e. put $ in front of both the row and column addresses.
  e.g. $B$6
Wholesale sales is the Wholesale Price times the Wholesale gas sold.
Total Sales is Retail sales plus Wholesale sales.
Cost of sales is 61% of Total Sales.
Marketing and selling is 14.4% of Total Sales.
Depreciation per quarter is one-fourth of the annual rate of 8.6% times Investments.
Total Expense is the sum of the four expense categories.
Operating Income is Total Sales minus Total Expense.
Interest Expense per quarter is one-fourth of the annual rate of 12%
  times Short-term debt plus one-fourth of the annual rate of 10.5% times Long-term debt.
Income Before Taxes is Operating Income minus Interest Expense.
Income Taxes is 46% of Income Before Taxes.
Net Income is Income Before Taxes minus Income Taxes.
Return on Investment (ROI) is Net Income divided by Investments.  It's a percentage.
  Hint: Total ROI is in the low double digits.


MIDWEST UNIVERSAL GAS					
					
Pro Forma Income Statement					
(Dollars in thousands)					
					
Retail Price	$350.00 				
Wholesale Price	$286.00 				
					
		1st Quarter	2nd Quarter	3rd Quarter	4th Quarter	  Total
		---------------	---------------	---------------	---------------	---------------
Production (in BCF):					
  Retail gas sold					
  Wholesale gas sold					
		---------------	---------------	---------------	---------------	---------------
Total Gas Sold	            228	            201	             82	            221	
					
Revenues:					
  Retail sales					
  Wholesale sales					
		---------------	---------------	---------------	---------------	---------------
Total Sales					
					
Expenses:					
  Cost of sales					
  Marketing and selling					
  General admin            4465	           3740	           3980	          4065	
  Depreciation					
		---------------	---------------	---------------	---------------	---------------
Total Expense					
					
Operating Income					
					
Interest Expense					
		---------------	---------------	---------------	---------------	---------------
Income Before Taxes					
					
Income Taxes					
		---------------	---------------	---------------	---------------	---------------
Net Income					
		---------------	---------------	---------------	---------------	---------------
Return on Investment					
					
					
Supporting Items:					
  Investments		102400				
  Short-term debt	13933				
  Long-term debt	39200