Using the following information you are to prepare a comprehensive budget for Ri
ID: 2570973 • Letter: U
Question
Using the following information you are to prepare a comprehensive budget for River City Micro Systems, Inc. The Company assembles a specialized device used in airports to detect certain types of explosives to prevent terrorist attacks.
Arrangements have been made for the component parts (bundled in packets, one per unit) to be produced in Indonesia, shipped to Boise, then assembled and sold by River City Micro to the end users.
You have developed the prototypes, established a market, and now you are putting together a budget for the first three months of 2017. The Company will actually start manufacturing and distribution on January 2, 2017.
Please create an Income statement utilizing the following assumptions in making your calculations:
a.Projected sales in units are as follows: January = 500, February = 600, March = 600, April and following months = 800. At the start of each month the management plans to have 30 days, (1
month) of direct materials on hand. Each packet of direct material costs $80.00. The company will have 800 units on hand on January 1, 2017 (all purchased during December 2016).
b.Ten hours of direct labor are required to assemble each device. The direct labor cost (including fringe benefits) is $35.00 per hour.
c. Manufacturing overhead is 50% of direct labor cost.
d. Devices are sold at 100% markup on cost.
e. The company wants to have at least 50% of next months projected sales in ending finished goods inventory each month.
f. Direct materials purchases are paid for on the 10th day of the month following month of purchases.
g. Manufacturing overhead is paid 25% in cash and with the balance paid in 30 days.
h. Wages earned by employees during the first half of each month are paid on the 22nd with the remainder paid on the 7th of the following month. Assume that workforce is stable each month (hence, wages and salaries are the same every day of the month).
i.On January 1, 2017 you acquire equipment and finance it 100% through a capital lease. Life of equipment is 60 months with no salvage value. Capital lease payments are $12,000 per month including an imputed interest component. Your cost of capital is 10%. Use this rate to calculate the present value of the cash payments and the present value of the lease principal as of January 1, 2017. The first payment is due on February 1, 2017.
j. Selling commissions are 10% of sales price. These are paid on the 15th day of the month following month of sale.
k. Administrative salaries and fringe benefits are $60,000 per month payable on schedule outlined in h.
l. Rent is $8,000 per month payable on the first day of each month.
m. On January 1, 2017 the Company will pay 6 months insurance premiums in advance for a total of$24,000.
n. Other general and administrative expenses are estimated to be 15% of sales. They are paid in the month after they are incurred.
o. The company has a $500,000 line of credit secured by inventory and accounts receivable. Borrowing against this line must be in increments of $50,000. Interest is 12% per annum and is payable on the
1st day of the month following the borrowing. Assume all borrowing occur on the 15th day of the month. Repayments must also occur in $50,000 increments on the 15th day of the month.
p. All sales are on account and are collected 15% in month of sale, 75% in next month and the balance in the following month.
q. Income tax rate is 35%. Taxes accrue on each month’s income and are paid in arrears on January 15, Apr 15, Jul 15 and Oct 15 for the preceding quarter. Note: any expected losses create tax benefits that can be used in reduce taxes paid in future quarters.
r. Beginning cash balance on January 1, 2017 is projected to be $100,000 that was raised through the sale of capital stock in December 2016.
Below is the projected units of production, raw material requirements, and raw material purchases in dollars.
Please Create an Income Statement for the Company.
Ans 1 Projected Unit of Production: Jan Feb March Sales During the month Closing Stock (50% of next month Sale Opening Stock Add 300 Less 0 300 300 Production during the month 800 600 Projected raw material requirements Raw Material Requirement Ans 2 Ans 3 Raw Material Purchase in Dollars Raw Material Requirement Closing Stock (1 month in hand) Opening Stock Raw Material Purchase(units) Cost/ unit Raw Material Purchase in Dollars Add 800 Less 800 600 Ans 4 Projected cost of goods manufactured statement Direct Material Cost Direct Labour Cost Manufacturing Overhead 350 175 350 175 350 175 Cost of goods manufactured Units Produced 800 600 Projected cost of goods manufactured 484,000.00 363,000.00 423,500.00Explanation / Answer
January February March Budgeted Unit 500 600 600 + Planned ending Unit 900 900 1100 - Beginning Iunit 800 900 900 = Planned production in units 600 600 800 Projected Raw material requirement January February March Budgeted Unit 500 600 600 + Planned ending Unit 600 600 800 - Beginning Iunit 500 600 600 = Planned raw material requirment 600 600 800 Projected Raw material purchases in dollar January February March Budgeted Unit 40000 48000 48000 + Planned ending Unit 48000 48000 64000 - Beginning Iunit 40000 48000 48000 = Planned production purchases in dollar 48000 48000 64000 Cost Of goods manufactured statement Jan Feb March Direct Material $ 48,000 $ 48,000 $ 64,000 Direct labour $ 210,000 $ 210,000 $ 280,000 Factory overhead 50% of Direct labor cost $ 105,000 $ 105,000 $ 140,000 Total Manufacturing cost $ 363,000 $ 363,000 $ 484,000 Beginig WIP Inventory $ 64,000.00 - $ - less : ending WIP - - $ (64,000) cost of goods manufactured $ 790,000 $ 726,000 $ 904,000 Income Statement Jan Feb March Revenue 100% markup on cost $ 1,660,000 $ 1,500,000 $ 1,404,000 Expenses Selling Expenses(sell unit price10%* projected sales) $ 166,000.00 $ 150,000.00 $ 140,400.00 Administrative expenses $60,000 $60,000 $60,000 Rent payable $8,000 $8,000 $8,000 Insurance pay $4,000 $4,000 $4,000 15% of Total sell $ 238,000.00 $ 222,000.00 $ 212,400.00 Income before tax $ 1,422,000 $ 1,278,000 $ 1,191,600 Tax 35%( each month = 0.35/12) $ (41,475) $ (37,275) $ (34,755) Net Income $ 1,380,525 $ 1,240,725 $ 1,156,845 Cash flow statement Jan Feb March Operating Cash flow Add: Revenue 249000 1470000 1501600 Less: Direct material purchase refer schedule F - $ (48,000) -48000 Manufacturing cost 25% paid in cash remaining next month $ (26,250) $ (105,000) $ (105,000) wages of employees refer schdule h $ (105,000) $ (210,000) $ (245,000) selling commissions paid refer schdule j - $ (166,000) -$150,000.00 Insurance paid refer schedule M $ (24,000.00) Net Cash from operating activities $ 93,750 $ 941,000 $ 953,600 Cashflow from investing activities Investing in equipmet - $ (12,100) $ (12,100) Net Cash from investing activities $ 93,750 $ 928,900 $ 941,500 Cashflow from finanacing actvities 8,474.58 8,534.22 8,596.57 Ne Cash flow from financing actvities $ 85,275.42 $ 920,365.78 $ 932,903.43 Beginning of cash flow $100,000 - - Cash end of the year $ 185,275 $ 920,366 $ 932,903 Amortization schedule Loan Amount $500,000 Monthly payment $8,333 Years 5 Number of Payments per Year 12 Effective monthly interest rate 1.00% Effective annual interest rate 12.00% Amount Debt Periods Accum Accum Period Outstanding Increase in 5000per month Service Principal Interest Remain Principal Interest 0 500,000 500,000 60 0.00 0.00 1 501,525 500,000 8,474.58 3,474.58 5,000.00 59 3,474.58 5,000.00 2 498,006 494,985 8,534.22 3,518.97 5,015.25 58 6,993.54 10,015.25 3 494,390 490,005 8,596.57 3,616.51 4,980.06 57 10,610.05 14,995.32 4 490,672 485,061 8,661.80 3,717.90 4,943.90 56 14,327.95 19,939.22 5 486,849 480,154 8,730.07 3,823.35 4,906.72 55 18,151.30 24,845.94 6 482,916 475,286 8,801.58 3,933.10 4,868.49 54 22,084.40 29,714.43 7 478,868 470,456 8,876.54 4,047.38 4,829.16 53 26,131.78 34,543.58 8 474,702 465,668 8,955.15 4,166.47 4,788.68 52 30,298.25 39,332.26 9 470,411 460,921 9,037.66 4,290.64 4,747.02 51 34,588.89 44,079.28 10 465,991 456,217 9,124.33 4,420.22 4,704.11 50 39,009.11 48,783.39 11 461,435 451,557 9,215.44 4,555.53 4,659.91 49 43,564.65 53,443.30 12 456,738 446,942 9,311.30 4,696.95 4,614.35 48 48,261.59 58,057.65 13 451,894 442,375 9,412.23 4,844.85 4,567.38 47 53,106.44 62,625.04 14 446,894 437,856 9,518.61 4,999.67 4,518.94 46 58,106.12 67,143.97 15 441,732 433,387 9,630.82 5,161.89 4,468.94 45 63,268.00 71,612.91 16 436,400 428,970 9,749.31 5,331.99 4,417.32 44 68,599.99 76,030.23 17 430,889 424,606 9,874.55 5,510.55 4,364.00 43 74,110.55 80,394.23 18 425,191 420,297 10,007.07 5,698.17 4,308.89 42 79,808.72 84,703.13 19 419,296 416,045 10,147.44 5,895.53 4,251.91 41 85,704.25 88,955.04 20 413,192 411,852 10,296.30 6,103.34 4,192.96 40 91,807.59 93,148.00 21 406,870 407,720 10,454.36 6,322.44 4,131.92 39 98,130.02 97,279.92 22 400,316 403,651 10,622.40 6,553.70 4,068.70 38 104,683.73 101,348.62 23 393,518 399,648 10,801.30 6,798.14 4,003.16 37 111,481.87 105,351.78 24 386,461 395,713 10,992.03 7,056.85 3,935.18 36 118,538.72 109,286.97 25 379,130 391,848 11,195.67 7,331.06 3,864.61 35 125,869.77 113,151.58 26 371,508 388,057 11,413.44 7,622.14 3,791.30 34 133,491.92 116,942.88 27 363,576 384,342 11,646.73 7,931.65 3,715.08 33 141,423.56 120,657.96 28 355,315 380,706 11,897.07 8,261.31 3,635.76 32 149,684.87 124,293.73 29 346,702 377,153 12,166.23 8,613.08 3,553.15 31 158,297.95 127,846.88 30 337,713 373,686 12,456.20 8,989.18 3,467.02 30 167,287.13 131,313.90 31 328,321 370,309 12,769.27 9,392.15 3,377.13 29 176,679.28 134,691.03 32 318,496 367,026 13,108.06 9,824.86 3,283.21 28 186,504.13 137,974.23 33 308,205 363,841 13,475.59 10,290.63 3,184.96 27 196,794.76 141,159.19 34 297,412 360,759 13,875.34 10,793.28 3,082.05 26 207,588.04 144,241.25 35 286,075 357,785 14,311.39 11,337.27 2,974.12 25 218,925.31 147,215.36 36 274,147 354,924 14,788.50 11,927.75 2,860.75 24 230,853.06 150,076.11 37 261,576 352,182 15,312.28 12,570.81 2,741.47 23 243,423.87 152,817.58 38 248,302 349,567 15,889.39 13,273.63 2,615.76 22 256,697.50 155,433.34 39 234,258 347,084 16,527.79 14,044.77 2,483.02 21 270,742.27 157,916.37 40 219,363 344,741 17,237.05 14,894.48 2,342.58 20 285,636.74 160,258.94 41 203,528 342,547 18,028.81 15,835.18 2,193.63 19 301,471.92 162,452.58 42 186,646 340,512 18,917.34 16,882.06 2,035.28 18 318,353.98 164,487.86 43 168,592 338,646 19,920.33 18,053.87 1,866.46 17 336,407.86 166,354.32 44 149,218 336,960 21,059.99 19,374.06 1,685.92 16 355,781.92 168,040.24 45 128,346 335,468 22,364.51 20,872.32 1,492.18 15 376,654.25 169,532.42 46 105,759 334,184 23,870.29 22,586.84 1,283.46 14 399,241.08 170,815.88 47 81,191 333,127 25,625.12 24,567.53 1,057.59 13 423,808.61 171,873.47 48 54,310 332,315 27,692.88 26,880.97 811.91 12 450,689.58 172,685.38 49 24,692 331,772 30,161.05 29,617.94 543.10 11 480,307.52 173,228.49 50 -8,213 331,525 33,152.46 32,905.53 246.92 10 513,213.06 173,475.41 51 -45,140 331,607 36,845.19 36,927.32 -82.13 9 550,140.38 173,393.28 52 -87,099 332,058 41,507.27 41,958.67 -451.40 8 592,099.05 172,941.88 53 -135,531 332,929 47,561.30 48,432.29 -870.99 7 640,531.34 172,070.89 54 -192,601 334,284 55,714.07 57,069.38 -1,355.31 6 697,600.73 170,715.57 55 -261,769 336,210 67,242.09 69,168.09 -1,926.01 5 766,768.82 168,789.56 56 -349,094 338,828 84,707.03 87,324.72 -2,617.69 4 854,093.54 166,171.88 57 -466,691 342,319 114,106.35 117,597.29 -3,490.94 3 971,690.83 162,680.94 58 -644,851 346,986 173,492.98 178,159.89 -4,666.91 2 1,149,850.72 158,014.03 59 -1,004,734 353,434 353,434.47 359,882.98 -6,448.51 1 1,509,733.70 151,565.53 Budget Balancesheet Jan Feb March Assets Current Assets Cash acoount $ 185,360 $ 920,365 $ 932,903 Account receivables 492188 562007.0463 569806.6464 inventory 474000 363000 602667 Prepaid insurance $24,000 - - Total fixed assets $ 1,175,548 $ 1,845,372 $ 2,105,376 Fixed Assets Property and equipment $720000(60*12000) $ 709,070.00 $ 698,129.67 Less accumulated depreciation - 10929.91(PV of cash payment) ($10,940) ($10,961) Net property $ 709,070.00 $ 698,129.67 $ 687,168.47 Stock Holders Equity Commmon stock $100,000 $100,000 $100,000 retained earning $1,380,525 $1,240,725 $1,156,845 Networth $1,480,525 $1,340,725 $1,256,845 Current liabilities Account payable $518,250 $502,250 $517,400 bank note payable 8390 8535 8597 incometax payable 41475 37275 34755 capital lease paayble 10930 10940 10961 Total current liabilities $579,045 $559,000 $571,714
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.