Scribd
Upload
128 views11 pages

Untitled Spreadsheet

The document contains spreadsheets outlining the initial investments, projected sales and costs, annual projections, and income statement for a rice trading business called MinRice over 5 years from 2020-2024. The initial investments total PHP 1,527,000 and include items like a warehouse, delivery vehicle, weighing scale, and permits. Projected annual sales are estimated to increase from PHP 18.8 million in 2020 to PHP 20.36 million in 2024, while annual costs of goods sold and operating costs also rise over the period. The income statement compares total annual revenue to costs to determine profitability each year.

Uploaded by

Beomi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
128 views11 pages

Untitled Spreadsheet

The document contains spreadsheets outlining the initial investments, projected sales and costs, annual projections, and income statement for a rice trading business called MinRice over 5 years from 2020-2024. The initial investments total PHP 1,527,000 and include items like a warehouse, delivery vehicle, weighing scale, and permits. Projected annual sales are estimated to increase from PHP 18.8 million in 2020 to PHP 20.36 million in 2024, while annual costs of goods sold and operating costs also rise over the period. The income statement compares total annual revenue to costs to determine profitability each year.

Uploaded by

Beomi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
  • Initial Investment
  • Estimated Expenses
  • Projected Sales and Cost
  • Operating Costs
  • Annual Projection
  • Income Statement
  • Capital Budget Methods

Untitled spreadsheet.

xlsx - Initial investment

INITIAL INVESTMENT

Materials Years Peso(P)


Warehouse/Solar dryer 15 300,000.00
Service Car (for delivery) 6 600,000.00
Weighing scale 5 15,000.00
Furniture and fixture 5 20,000.00
Rice bag sewing machine 5 3,000.00
Permits
-NFA registration 25,000.00
-Sanitary Permit
-Bureau of Fire Permit
-Municipal Permit
-DTI/BIR Permit
-Insurance

Other expenses
Electricity and maintenance 60,000.00
Promotion cost 4,000.00
Cash on hand (Palay Buying) 500,000.00
TOTAL INITIAL INVESTMENTS PHP 1,527,000.00

1
Untitled spreadsheet.xlsx - Projected sales and cost

ESTIMATED EXPENSES
PRODUCT Dinorado Red Rice C160 RC18 218 - Rice TOTAL
Sacks 100 150 70 70 70
Kilos 55 55 55 55 55
BUYING (PALAY)
Price/kilo 16.00 23.00 15.00 14.00 16.00
Cost 88,000.00 189,750.00 57,750.00 53,900.00 61,600.00 PHP 451,000.00
HAULING
Price/sack 20.00 20.00 20.00 20.00 20.00
Cost 2,000.00 3,000.00 1,400.00 1,400.00 1,400.00 PHP 9,200.00
DRYING
Price/sack 20.00 20.00 20.00 20.00 20.00
Cost 2,000.00 3,000.00 1,400.00 1,400.00 1,400.00 PHP 9,200.00
MILLING OF PALAY
Kilos (dry) 5000 7500 3500 3500 3500
Kilos of palay/
80 80 80 80 80
cavan of rice
Kilo/rice 50 50 50 50 50
Price/kilo 3.00 3.00 3.00 3.00 3.00
Cost 9,375.00 14,062.50 6,562.50 6,562.50 6,562.50 PHP 43,125.00
Rice bag(288sacks x P12.00) PHP 3,465.00
TOTAL EXPENSES PHP 515,990.00

2
Untitled spreadsheet.xlsx - Projected sales and cost

SALES (SEMI-MONTHLY)
PRODUCT DINORADO RED RICE C160 RC18 218-RICE TOTAL
Sale price 2,200.00 3,700.00 2,200.00 1,800.00 2,200.00
Cavans sold 62.5 93.75 43.75 43.75 43.75
TOTAL SALES 137,500.00 346,875.00 96,250.00 78,750.00 96,250.00 PHP 755,625.00

COST OF GOODS SOLD (SEMI-MONTHLY)


Palay 88,000.00 189,750.00 57,750.00 53,900.00 61,600.00
Hauling 2,000.00 3,000.00 1,400.00 1,400.00 1,400.00
Drying 2,000.00 3,000.00 1,400.00 1,400.00 1,400.00
Milling 9,375.00 14,062.50 6,562.50 6,562.50 6,562.50
Packaging Material 751.95 1,127.93 526.37 526.37 526.37
TOTAL COST OF
102,126.95 210,940.43 67,638.87 63,788.87 71,488.87 PHP 515,983.98
GOODS SOLD
GROSS PROFIT PHP 239,641.02

3
Untitled spreadsheet.xlsx - Projected sales and cost

OPERATING COSTS (SEMI-MONTHLY)


FIXED COST
Electricity and maintenance PHP 60,000.00
Depreciation expense
VARIABLE COST
Diesel/estimate per cavan (P1000
(Diesel)/ 50 cavans (288cavans) 5,760.00
Helpers/Operators (288cavans of
rice x P18/cavans) wages 5,184.00
TOTAL OPERATING COSTS PHP 70,944.00 PHP 70,944.00
TOTAL PROFIT PHP 168,697.02

4
Untitled spreadsheet.xlsx - Annual projection

ANNUAL PROJECTION
2020 2021 2022 2023 2024
ANNUAL SALES
Price growth rate 0% 0% 1% 2%
Cavans sold growth rate 0% 1% 2% 2%
DINORADO
Price per cavan 2,200.00 2,200.00 2,200.00 2,222.00 2,266.44
No. of cavans sold annually 1500.00 1500.00 1515.00 1545.30 1576.21
Sales 3,300,000.00 3,300,000.00 3,333,000.00 3,433,656.60 3,572,376.33
RED RICE
Price per cavan 4,000.00 4,000.00 4,000.00 4,040.00 4,120.80
No. of cavans sold annually 2250.00 2250.00 2272.50 2317.95 2364.31
Sales 9,000,000.00 9,000,000.00 9,090,000.00 9,364,518.00 9,742,844.53
C160
Price per cavan 2,200.00 2,200.00 2,200.00 2,222.00 2,266.44
No. of cavans sold annually 1050.00 1050.00 1060.50 1081.71 1103.34
Sales 2,310,000.00 2,310,000.00 2,333,100.00 2,403,559.62 2,500,663.43
RC18
Price per cavan 1,800.00 1,800.00 1,800.00 1,818.00 1,854.36
No. of cavans sold annually 1050.00 1050.00 1060.50 1081.71 1103.34
Sales 1,890,000.00 1,890,000.00 1,908,900.00 1,966,548.78 2,045,997.35
218-RICE
Price per cavan 2,200.00 2,200.00 2,200.00 2,222.00 2,266.44
No. of cavans sold annually 1050.00 1050.00 1060.50 1081.71 1103.34
Sales 2,310,000.00 2,310,000.00 2,333,100.00 2,403,559.62 2,500,663.43
TOTAL ANNUAL SALES 18,810,000.00 18,810,000.00 18,998,100.00 19,571,842.62 20,362,545.06

ANNUAL COST OF GOODS SOLD


Rate increase 0% 1% 2% 2%
DINORADO 2,451,046.88 2,451,046.88 2,475,557.34 2,525,068.49 2,575,569.86

5
Untitled spreadsheet.xlsx - Annual projection

RED RICE 5,062,570.31 5,062,570.31 5,113,196.02 5,215,459.94 5,319,769.13


C160 1,623,332.81 1,623,332.81 1,639,566.14 1,672,357.46 1,705,804.61
RC18 1,530,932.81 1,530,932.81 1,546,242.14 1,577,166.98 1,608,710.32
218-RICE 1,715,732.81 1,715,732.81 1,732,890.14 1,767,547.94 1,802,898.90
TOTAL ANNUAL COST OF
GOODS SOLD
12,383,615.63 12,383,615.63 12,507,451.78 12,757,600.82 13,012,752.83

ANNUAL OPERATING COSTS


FIXED
Electricity and maintenance 60,000.00 60,000.00 60,000.00 60,000.00 60,000.00
VARIABLE
Helpers and operators (wages) 5,184.00 5,184.00 5,184.00 5,184.00 5,184.00
Diesel 5,760.00 6,048.00 6,410.88 6,859.64 7,408.41
Rate increase 5% 6% 7% 8%
Depreciation expense 127,600.00 127,600.00 127,600.00 127,600.00 127,600.00
Total operating cost(semi-monthly) 198,544.00 198,832.00 199,194.88 199,643.64 200,192.41
TOTAL ANNUAL OPERATING
COSTS
4,765,056.00 4,771,968.00 4,780,677.12 4,791,447.40 4,804,617.91

6
Untitled spreadsheet.xlsx - Income statement

MINRICE
INCOME STATEMENT
For the years ending [Dec. 31, 2020, Dec. 31, 2021, Dec. 31, 2022, Dec. 31, 2023, Dec. 31, 2024]

2020 2021 2022 2023 2024


REVENUE
Sales 18,810,000.00 18,810,000.00 18,998,100.00 19,571,842.62 20,362,545.06
TOTAL REVENUE 18,810,000.00 18,810,000.00 18,998,100.00 19,571,842.62 20,362,545.06

EXPENSES
Cost of goods sold 12,383,615.63 12,383,615.63 12,507,451.78 12,757,600.82 13,012,752.83
Operating cost 4,765,056.00 4,771,968.00 4,780,677.12 4,791,447.40 4,804,617.91
TOTAL EXPENSES 17,148,671.63 17,155,583.63 17,288,128.90 17,549,048.22 17,817,370.74

Net income before tax 1,661,328.38 1,654,416.38 1,709,971.10 2,022,794.40 2,545,174.32


Business tax expense 498,398.51 496,324.91 512,991.33 606,838.32 763,552.30

NET INCOME 1,162,929.86 1,158,091.46 1,196,979.77 1,415,956.08 1,781,622.02

7
Untitled spreadsheet.xlsx - Nondiscounted method

NON-DISCOUNTED CAPITAL BUDGET METHOD

COMPUTATION OF COST OF CAPITAL

Market Premium(Retail Industry) 6.63%


Risk free rate 2.52%
Unlevered Beta 1.25

Cost of capital = Rf + Beta*Market premium


COST OF CAPITAL = 10.81%

ACCOUNTING RATE OF RETURN

ARR = AVERAGE NET INCOME


INITIAL INVESTMENT

ARR = 1,343,115.84
1,527,000.00
ARR = 88%
ACCEPTED

PAYBACK PERIOD

PP = NITIAL INVESTMENT
CASH FLOWS
YEAR CASH FLOWS BALANCE
PP = 1 0 1,527,000.00
+ 1 1,162,929.86 364,070.14
0.31 2 1,158,091.46 (794,021.33)

8
Untitled spreadsheet.xlsx - Nondiscounted method

PP(year)= 1.31 3 1,196,979.77


ACCEPTED 4 1,415,956.08
5 1,781,622.02

PAYBACK RECIPROCAL

PR = 1
PAYBACK PERIOD

PR = 1
1.31
PR = 76%
ACCEPTED

9
Untitled spreadsheet.xlsx - Discounted method

DISCOUNTED CAPITAL BUDGET METHOD


NET PRESENT VALUE

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5

Initial investment 1,527,000.00


Cash flows 1,162,929.86 1,158,091.46 1,196,979.77 1,415,956.08 1,781,622.02
1.00 0.9024456277 0.8144081109 0.7349590388 0.663260571 0.5985566023
Balance 1,527,000.00 1,049,480.97 943,159.08 879,731.10 939,147.84 1,066,401.62
Present value(total 4,877,920.62
Net present value 3,350,920.62
ACCEPTED

INTERNAL RATE OF RETURN

Interpolation (uneven cash flows)

Rate NPV
73% 1,563,385.05
x 1,527,000.00
75% 1,525,548.11
76.9%
ACCEPTED

OFITABILITY INDEX DISCOUNTED PAYBACK

PROFITABILITY INOF CASH FLOWS PP=TIAL INVESTMENT


INITIAL INVESTMENT DISCOUNTED CASH FLOWS

10
Untitled spreadsheet.xlsx - Discounted method

PI = 4,877,920.62 Year Cash Flows Balance


1,527,000.00 0 1,527,000.00
1 1,049,480.97 477,519.03
PI = 3.19 2 943,159.08 (465,640.05)
ACCEPTED 3 879,731.10
4 939,147.84
5 1,066,401.62

PP= 1
+
0.51
PP= 1.51 ACCEPTED

11

You might also like