Question

Would be helpful if you show in excel with calculations

Gulf States Manufacturing has the following data from year 1 operations, which are to be used for developing year 2 budget estimates:

14 Required Information The following information applies to the questions displayed below.] 10 point Gulf States Manufacturing has the following data from year 1 operations, which are to be used for developing year 2 budget estimates: Eeck $1,800,000 Sales revenues (20,000 units) Manufacturing costs Materials Variable cash costs Fixed cash costs Depreciation (fixed) Marketing and administrative costs Marketing (variable, cash) Marketing depreciation Administrative (fixed, cash) Administrative depreciation Total costs Operating profits $ 321, 439,90 177.ee 217,892 232,099 57.00 229,800 20,899 $1,691, 292 $ 199,000 All depreciation charges are fixed. Old manufacturing equipment with an annual depreciation charge of $18.350 will be replaced in year 2 with new equipment that will incur an annual depreciation charge of $22.900. Sales volume and prices are expected to increase by 15 percent and 4 percent, respectively. On a per-unit basis, expectations are that materials costs will increase by 13 percent and variable manufacturing costs will decrease by 3 percent. Fixed cash manufacturing costs are expected to decrease by 10 percent. Variable marketing costs will change with volume. Administrative cash costs are expected to increase by 11 percent. Inventories are kept at zero. Gulf States operates on a cash basis. Required: Prepare a budgeted Income statement for year 2. (Do not round Intermediate calculations. Round your final answers to the nearest whole dollar amounts.) GULF STATES MANUFACTURING Budgeted Income Statement For Year 2 Sales revenue Manufacturing costs Materials Variable cash costs Foxed cash on Depreciation (fixed) Total manufacturing costs Marketing and administrative costs: Marketing (variable, cash) Marketing depreciation Administrative (fixed, cash) Administrative depreciation Total marketing and administrative costs Total costs Operating profil s S 01

Answer 1

Gulf States Manufacturing Budgeted Income Statement For Year 2

Sales Revenue ($1,800,000×1.15×1.04)	$2,152,800
Manufacturing Costs;
Materials ($321,000×1.15×1.13)	$417,139.50
Variable Cash Costs ($439,000×1.15×.97)	$489,704.50
Fixed Cash Costs ($177,000×.90)	$159,300
Depreciation(Fixed)($217,000 -$16,350 +$22,800)	$223,450
Total Manufacturing Costs	$1,289,594
Marketing and Administrative Costs;
Marketing (Variable , Cash)($232,000×1.15)	$266,800
Marketing Depreciation	$57,000
Administrative(Fixed, Cash)($228,000×1.11)	$253,080
Administrative Depreciation	$20,000
Total Marketing and Administrative Costs	$596,880
Total Costs	$1,886,474
Operating Profit	$266,326