Help with pro forma projection (finance simulations)assignment. Kindly advice on cost and how soon can I get this back

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 Q1 Inputs for Quarter Number 1 Company Operating Decisions Units to be produced 100,000 Per unit price 100 Div. per common share 0.1 Advertising cost 0 Demand/price forecast 0 Sales discount 0 Investment Decisions Short-term investment $ 200,000 Risk of S-T investment 0 Machine units bought 0 Units of plant bought 0 Project A no Project B no Financing Decisions Short-term loans 0 Preferred shares 0 Two-year loans $2,500,000 Common shares 0 Three-year loans 0 Common tender price 0 Ten-year bonds 0 Special Options Strike settlement (per hr.) 0 Dollar penalty 0 Quarterly Performance Report Quarter Number 1 Sales revenue ( 97,383 units at $100.00 ) 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 9,738,300 Income from securities 2,725 $9,741,025 Cost of Goods Sold: Beginning Inventory: ( 9,809 at $74.79 ) 9809 $733,567 Materials $1,500,000 Direct Labor 3,500,000 Total Direct Costs $5,000,000 Warehousing Costs $60,408 Depreciation: Mach. and Equip. 478,125 Plant 1,300,000 Other Overhead Costs 200,000 Total Indirect Costs 2,038,533 Production Costs ( 100,000 at $70.39 ) 7,038,533 Goods Available for Sale ( $70.78 per unit ) $7,772,100 Less: Ending Inventory ( 12,426 units ) 879,492 Cost of Goods Sold 6,892,608 Gross Profit $2,848,416 Selling and administrative expenses $1,486,915 Financial Expenses: Short Term Bank Interest 0 Penalty Loan Interest 0 Intermediate Term Loan Interest 92,749 Bond Interest 33,600 Bond Redemption Costs 0 Total Financial Charges 126,349 1,613,264 Operating Income Before Extraordinary Items 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 $1,235,153 Extraordinary Items 0 Income Before Taxes $1,235,152 Income Tax (rate is 40%) 0.4 494,060 Income After Taxes $741,092 Preferred Stock Dividind 0 Earnings to Common Stockholders $741,092 Common Stock Dividends ( $0.10 per share ) 100,000 Net Income Transferred to Retained Earnings $641,092 Position Statement Quarter Number 1 ASSETS Current Assets Cash $ 120,638 Marketable Securities 200,000 Accounts Receivable 6524661 Inventory ( 12,426 units at $70.78 /UNIT ) 879,492 Total Current Assets $7,724,789 Fixed Assets (net of depreciation Machinery and Equipment 2,008,125 Plant 7,165,250 Total Fixed Assets 9,173,375 Total Assets $16,898,164 LIABILITIES AND OWNER EQUITY Current Liabilities Accounts Payable $520,000 Short Term Loans Payable 0 Short Term Penalty Loan 0 Intermediate Term Debt Maturing 1,850,000 Bonds Maturing 1,200,000 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 Total Current Liabilities $ 3,570,000 Long Term Liabilities Intermediate Loans: 2 years $ 937,500 3 years 0 Bonds 1,200,000 Total Long Term Liabilities 2,137,500 Total Liabilities $ 5,707,500 Owners' Equity Preferred Stock ( 0 shares ) 0 Common Stock ( 1,000,000 shares ) 8,000,000 Retained Earnings 3,190,664 Total Equity 11,190,665 Total Liabilities and Equity $ 16,898,164 Summary Data Quarter Number 1 HISTORICAL INFORMATION Common share price 35.57 Accumulated Wealth 35.67 Quarterly EPS 0.74 Dividend Yield 0.011 Price earnings ratio 12 Marketable Security Yield 0.01362 Actual unit price 100 Actual unit demand 97,383 Preferred stock price 32.15 Preferred dividend yield 0.0311 Return on investment 0.1754 Return on equity 0.2649 Call premium: preferred 0.08 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 Bond call premium 0.08 Common tender or sell/sh 0 Unpaid preferred dividend/share 0 Outstanding debt yields: Short-term 2-year loan 3-year loan Bonds Penalty loan 0.0298 0.0311 0.025 0.014 0.08 INFORMATION FOR FUTURE QUARTERS: 2 3 4 5 Units forecast 105,721 103,295 123,736 117,871 Price per unit forecast 99.81 103.73 103.03 102.85 Units of plant capacity 100,000 100,000 100,000 80,000 Units of machine capacity 100,000 100,000 75,000 60,000 Other overhead 200,000 200,000 200,000 200,000 Depreciation: Machinery 478,125 478,125 384,375 311,250 Projects 0 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 0 0 0 Plant 1,300,000 1,300,000 1,300,000 989,000 Principal repayment on debt: Short-term 0 0 0 2-year 312,500 312,500 312,500 312,500 3-year 300,000 300,000 0 0 Bonds 300,000 300,000 300,000 300,000 Warehouse fees: Units First 2000 Next 5000 Over 7000 Cost/Unit 1 3 8 Production costs per unit next quarter: Materials 15 Machinery 47 Plant 321 Units First 60,000 Next 40,000 Next 20,000 Over 120,000 Labor cost 39 29 25 33 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 Rates on funding in quarter 2 Short-term 2-year loan 3-year loan Bond Preferred 0.01981 0.01915 0.01854 0.01754 0.02377 Short-term 2-year loan 3-year loan Bond Preferred Interest due next quarter: Short-term 0 Intermediate $ 83,030 Bonds $ 33,600 Capital budgeting projects for next quarter: Life Cost Unit Capacity Overhead Saving Unit Labor sav., Qtr.2 Change/Qtr. Labor Sav. A 2-yr $ 659,280 100,000 $ 15,340 0.82 0.03 B 3-yr $ 513,432 120,000 -$ 8,102 0.85 -0.01 Life Cost Unit Capacity Overhead Saving Unit Labor sav., Qtr.2 Change/Qtr. Labor Sav. Life Cost Unit Capacity Overhead Saving Unit Labor sav., Qtr.2 Change/Qtr. Labor Sav. Pro Forma2 Pro Forma Decision Inputs for Quarter Number 2 Market Forecasts Units Demanded 105,000 Two-year loans rate 0 Short-term investment rate 0.01 Three year loan rate 0.02 Capital gains rate 0 Long-term loan rate 0 Short-term loans rate 0 Preferred stock yield 0 Company Operating Decisions: Pro Forma Units to be produced 100,000 Per unit price 100 Div. per common share 0.1 Advertising cost 0 Demand/price forecast 0 Sales discount 0 Investment Decisions: Pro Forma Short-term investment $ 2,000,000 Risk of S-T investment 0 Machine units bought 0 Units of plant bought 0 Project A yes 1 Project B yes 1 Financing Decisions: Pro Forma Short-term loans 0 Preferred shares 0 Two-year loans Apparent Computation 0 Common shares 0 Three-year loans $ 2,000,000 Common tender price 0 Ten-year bonds 0 Special Options: Pro Forma Strike settlement (per hr.) 0 Dollar penalty 0 Pro Forma Performance Report Quarter Number 2 Sales revenue ( 105,000 units at $100.00 ) $ 10,500,000 Income from securities 22,000 $ 10,522,000 Cost of Goods Sold: Beginning Inventory: ( 12,426 at $70.78 ) 12426 $ 879,492 Materials $ 1,500,000 Direct Labor 3,333,000 Total Direct Costs $ 4,832,999 Warehousing Costs $ 20,408 Depreciation: Mach. and Equip. 603,321 Plant 1,300,000 Other Overhead Costs 192,761 Total Indirect Costs 2,116,490 Production Costs ( 100,000 at $69.49 ) 6,949,491 Goods Available for Sale ( $69.64 per unit ) $ 7,828,982 69.64 Less: Ending Inventory ( 7,426 units ) 517,123 7426 Cost of Goods Sold 7,311,859 Gross Profit $ 3,210,140 $ 10,500,000 22,000 $ 10,522,000 12425.71347838 879,492 $ 1,500,000 3,333,000 $ 4,833,000 20,408 603,321 1,300,000 $ 192,762 2,116,491 6,949,491 7,828,983 517146.64 $ 7,311,859 $ 3,210,141 Selling and administrative expenses $ 1,525,000 Financial Expenses: Short Term Bank Interest 0 Penalty Loan Interest $1,525,000.00 0 Intermediate Term Loan Interest 128,030 $ 123,030 XXX Bond Interest 33,600 33600 Bond Redemption Costs 0 Total Financial Charges 161,630 1,686,630 Operating Income Before Extraordinary Items $ 1,523,510 Extraordinary Items 0 Income Before Taxes $ 1,523,510 Income Tax (rate is 40%) 609,404 Income After Taxes $ 914,106 Preferred Stock Dividind 0 Earnings to Common Stockholders $ 914,106 Common Stock Dividends ( $0.10 per share ) 0.1 100,000 Net Income Transferred to Retained Earnings $ 814,106 Pro Forma Position Statement Quarter Number 2 ASSETS Current Assets Cash $ 420,793 Marketable Securities 2,200,000 Accounts Receivable 7,035,000 Inventory ( 7,426 units at $69.64 /UNIT ) 517,123 Total Current Assets $ 10,172,915 Fixed Assets (net of depreciation Machinery and Equipment $ 2,577,516 Plant $ 156,630 XXX $ 1,681,630 1523510 1523510 609404 914106 914106 100000 814106 ? $ 2,200,000 $7,035,000 517123 2,577,516 5,865,250 5,865,250 8,442,766 8,442,766 Total Fixed Assets Total Assets $ 18,615,681 LIABILITIES AND OWNER EQUITY Current Liabilities Accounts Payable $ 502,576 Short Term Loans Payable 0 Short Term Penalty Loan 0 Intermediate Term Debt Maturing 2,216,664 Bonds Maturing 1,200,000 Total Current Liabilities $ 3,919,240 Long Term Liabilities Intermediate Loans: 2 years $ 625,000 3 years 1,166,667 Bonds 900,000 Total Long Term Liabilities 2,691,666 Total Liabilities $ 6,610,906 Owners' Equity Preferred Stock ( 0 shares ) 0 Common Stock ( 1,000,000 shares ) 1000000 8,000,000 Retained Earnings 4,004,775 Total Equity 12,004,770 Total Liabilities and Equity $ 18,615,681 INFORMATION FOR FUTURE QUARTERS: 3 4 5 6 Units of plant capacity 100,000 100,000 80,000 50,000 Units of machine capacity 100,000 2.216666667 1,200,000 $ 3,919,240 $ 625,000 1.166666667 $ 900,000 $ 2,691,667 $ 6,610,907 8,000,000 12,004,775 18,615,676 75,000 60,000 60,000 Other overhead 192,761 192,761 192,761 192,761 Depreciation: Machinery 478,125 384,375 311,250 311,250 Projects 125,196 125,196 125,196 125,196 Plant 1,300,000 1,300,000 989,000 667,500 Principal repayment on debt: Short-term 0 0 0 2-year 312,500 312,500 312,500 312,500 3-year 466,667 166,667 166,667 166,667 Bonds 300,000 300,000 300,000 300,000 Warehouse fees: Units First 2000 Next 5000 Over 7000 Cost/Unit 1 3 8 Production costs per unit next quarter: Materials 15 Machinery 49 Plant 334 Units First 60,000 Next 40,000 Next 20,000 Over 120,000 Labor cost 37.31 27.31 24.16 33